Purpose
Return value
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 zerobased number instead of a 1based 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,B111,$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 zerobased 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.