- rate - Interest rate.
- per - Period (starts with zero, not 1).
- nper - Number of periods.
- pv - Present value.
How to use
The ISPMT function calculates the amount of interest in a given period of an investment where principal payments are equal. The given period is specified as a zero-based number instead of a 1-based number. For example, to calculate the interest amount in payments for a loan where the amount is $10,000, the interest rate is 10%, and there are 5 periods in which the principal payment is constant (even), you can use:
=ISPMT(10%,0,5,-10000) // interest in period 1 =ISPMT(10%,1,5,-10000) // interest in period 2 =ISPMT(10%,2,5,-10000) // interest in period 3 =ISPMT(10%,3,5,-10000) // interest in period 4 =ISPMT(10%,4,5,-10000) // interest in period 5
In the example shown, the formula in H11, copied down, is:
Note ISPMT assumes principal amounts are equal, but the payment is variable. For a loan where the payment is a fixed amount, see the IMPT function.
Be consistent with the units. For a 3 year loan with monthly payments and an annual interest rate of 10%, enter rate as 10%/12. Enter nper as 3*12.
ISPMT uses a zero-based index for period (per). Use 0 for period 1, 1 for period 2, etc.
The PPMT function is for loans with even principal payments. For a loan with even periodic payments, use the IPMT function.