Exceljet

Quick, clean, and to the point

Excel ISPMT Function

Excel ISPMT function
Summary 

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)
Arguments 
  • rate - Interest rate.
  • per - Period (starts with zero, not 1).
  • nper - Number of periods.
  • pv - Present value.
Version 
Usage notes 

The ISPMT function calculates the amount of interest in 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 IMPT function.

Notes:

  1. 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.

  2. ISPMT uses a zero-based index for period (per). Use 0 for period 1, 1 for period 2, etc.

  3. The PPMT function is for loans with even principal payments. For a loan with even periodic payments, use the IPMT function.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.