IPMT FunctionWayne Becker20150903T03:14:07+00:00
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 lumpsum 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 
