Related Function:

The ISPMT function calculates the interest paid, during a specific period of a loan or investment.

Syntax

=ISPMT(rate,per,nper,pv)

Arguments

Argument Description
rate The interest rate – per period
per The period for which you want to find the interest, and must be between 1 and nper
nper The total number of payment periods for the investment
pv The present value of the investment. For a loan, pv is the loan amount

Examples

  A B C D
1 Data      
2 10% Annual interest rate    
3 3 Loan term – years    
4 $800,000 Amount of loan    
5        
6 Formula Result Notes
7 =ISPMT(A2/12,1,A3/12,A5) -$64,814.80 Interest paid for the first monthly payment of a loan with the above terms
8 =ISPMT(A2,1,A3,A4) -$533,333.00 Interest paid in the first year of a loan with the above terms

Note: In the above example, the payments are made monthly, so

  • the annual interest rate of 10% had to be converted into a monthly rate (=10%/12), and
  • the number of years had to be converted into months (=3*12)

Cash Flow Convention: In line with general cash flow conventions, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the supplied arguments are not recognized as numeric values
Result is much higher or much lower than expected Usually occurs due to failure to convert the rate or the nper to months or quarters, i.e.:

  months = 12 * years   monthly rate = annual rate/12
  quarters = 4 * years   quarterly rate = annual rate/4