IPMT function
IPMT returns the interest portion of a single loan payment, as a negative number for a positive loan. Pair it with PPMT to build a loan amortization table.
Example
Read it as a sentence: for an 8000 loan at 10% a year paid monthly over 36 months, find the interest part of payment number 1.
A
B
C
D
1
-66.6666666666667
2
3
Syntax
IPMT(rate, per, nper, pv, [fv], [type])
Arguments in [square brackets] are optional.
-
rate(required) - Interest rate per period.
-
per(required) - Period to analyse (1 to nper).
-
nper(required) - Total number of periods.
-
pv(required) - Loan amount today.
-
fv(optional) - Balance wanted at the end; 0 if omitted.
-
type(optional) - 0 = payments at period end (default), 1 = at start.
Returns: Interest portion of one loan payment, as a negative number.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=IPMT(0.05/12,1,60,20000) | ($83.33) | Period 1 |
=IPMT(0.05/12,60,60,20000) | ($1.57) | Last period |
=IPMT(0.05/12,1,60,20000,0,1) | $0.00 | Type 1 period 1 (0) |
=IPMT(0.05,1.5,10,1000) | ($48.04) | Per 1.5 |
=IPMT(0,1,10,1000) | $0.00 | Rate 0 |
=IPMT("0.05","1","10","1000") | ($50.00) | Text number |
=IPMT(0.05,0,10,1000) | #NUM! | Per 0 |
=IPMT(0.05,11,10,1000) | #NUM! | Per > nper |
Watch out for
- Use a monthly rate for monthly payments.
- Result is negative when pv is positive.
Where it works
- Excel
- IPMT is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .