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

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.