Excel ISPMT function

The Excel ISPMT function calculates the interest paid during 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.

Purpose

Get interest paid for specific period

Return value

Interest amount in given period

Syntax

=ISPMT(rate,per,nper,pv)

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:

=ISPMT($C$6,B11-1,$C$7,-$C$5)

Note ISPMT assumes principal amounts are equal, but the payment is variable. For a loan where the payment is a fixed amount, see the IPMT function.

Notes

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.