Financial
All versions
◉ 40 views
=ISPMT()
Calculates the interest paid (or received) for the specified period of a loan (or investment) with even principal payments.
Applies To:
Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016
=ISPMT(rate, per, nper, pv)
Hover argument names in the table for details.
Output typeNumber
Popularity10 / 100
VolatileNo
Dynamic ArrayNo
Download sample .xlsx ▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =ISPMT() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
rate |
Number | Required | The interest rate for the investment. |
per |
Number | Required | The period for which you want to find the interest, and must be between 1 and Nper. |
nper |
Number | Required | The total number of payment periods for the investment. |
pv |
Number | Required | The present value of the investment. For a loan, Pv is the loan amount. |
▣ Examples
Example 1
=ISPMT(23% / 12, 0, 36, 500000000)
§ Remarks
- Make sure that you are consistent about the units you use for specifying Rate and Nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12/12 for Rate and 4*12 for Nper. If you make annual payments on the same loan, use 12% for Rate and 4 for Nper.
- For all the arguments, the cash you pay out, such as deposits to savings or other withdrawals, is represented by negative numbers; the cash you receive, such as dividend checks and other deposits, is represented by positive numbers.
- ISPMT counts each period beginning with zero, not with one.
- Most loans use a repayment schedule with even periodic payments. The IPMT function returns the interest payment for a given period for this type of loan.
- Some loans use a repayment schedule with even principal payments. The ISPMT function returns the interest payment for a given period for this type of loan.
- To illustrate, the amortization table below uses an even-principal repayment schedule. The interest charge each period is equal to the Rate times the unpaid balance for the previous period. And the payment each period is equal to the even principal plus the interest for the period.
✦ Tips
- Make sure that you are consistent about the units you use for specifying Rate and Nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12/12 for Rate and 4*12 for Nper. If you make annual payments on the same loan, use 12% for Rate and 4 for Nper.
- For all the arguments, the cash you pay out, such as deposits to savings or other withdrawals, is represented by negative numbers; the cash you receive, such as dividend checks and other deposits, is represented by positive numbers.
- ISPMT counts each period beginning with zero, not with one.
- Most loans use a repayment schedule with even periodic payments. The IPMT function returns the interest payment for a given period for this type of loan.
- Some loans use a repayment schedule with even principal payments. The ISPMT function returns the interest payment for a given period for this type of loan.
- To illustrate, the amortization table below uses an even-principal repayment schedule. The interest charge each period is equal to the Rate times the unpaid balance for the previous period. And the payment each period is equal to the even principal plus the interest for the period.
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!