Related Functions:

The IPMT function calculates the interest payment, during a specific period of a loan or investment that is paid in constant periodic payments, with a constant interest rate.

Syntax

=IPMT(rate,per,per,pv,[fv],[type])

Arguments

Argument Description
rate The interest rate – per period
per The period for which you want to find the interest and must be in the range 1 to nper
nper The total number of payment periods in an annuity
pv The present value, or the lump-sum amount that a series of future payments is worth today
[fv] Optional. The future value, or a cash balance you want to attain after the last payment is made

  If omitted, [fv] defaults to 0 (zero), i.e. the future value of a loan is 0
[type] Optional. Defines whether the payment is made at the start or the end of the period

  0 the payment is made at the end of the period; default if argument is omitted
  1 the payment is made at the beginning of the period

Examples

  A B C D
1 Data      
2 10% Annual interest rate    
3 3 Loan term – years    
4 $8,000 Present value of loan    
5        
6 Formula Result Notes
7 =IPMT(A2/12,1,A3*12,A4) -$66.67 Interest due in the first month for a loan with the terms above, where payments are made month
8 =IPMT(A2,3,A3,A4) -$292.45 Interest due in the last year for a loan with the terms above, where payments are made yearly

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
#NUM! Occurs if the supplied per argument is < 0 or is > the supplied value of nper
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