ISPMT function
ISPMT returns the interest for a period of a loan repaid in equal chunks of principal, so interest shrinks in a straight line. It differs from IPMT, which assumes level payments.
Example
Read it as a sentence: find the interest part of the payment for period 1 on a 4000 loan over 4 periods at 10% per period, which gives -300.
A
B
C
D
1
-300
2
3
Syntax
ISPMT(rate, per, nper, pv)
-
rate(required) - Interest rate per period.
-
per(required) - Period to check, where the first period is 0.
-
nper(required) - Total number of periods.
-
pv(required) - Loan amount.
Returns: Interest for a period of a loan with equal principal payments.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=ISPMT(0.1,1,3,8000000) | -533333.3333 | Period 1 |
=ISPMT(0.1,0,3,8000000) | -800000 | Period 0 |
=ISPMT(0.1,5,3,1000) | 66.66666667 | Per > nper (no check) |
=ISPMT(-0.1,1,3,1000) | 66.66666667 | Negative rate |
=ISPMT("0.1","1","3","1000") | -66.66666667 | Text number |
=ISPMT(0.1,{0,1,2},3,1000) | -100 | Spill |
=ISPMT(0.1,1,0,1000) | #DIV/0! | Nper 0 |
=ISPMT("a",1,3,1000) | #VALUE! | Text |
Watch out for
- Periods count from 0, unlike IPMT where they start at 1.
- The result is negative for a positive loan.
Where it works
- Excel
- ISPMT is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .