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 zerobased number instead of a 1based number.
 rate  Interest rate.
 per  Period (starts with zero, not 1).
 nper  Number of periods.
 pv  Present value.
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:
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 IMPT 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.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.