IPMT
Calculate how much of a given loan payment is interest, for any period of the loan.
Syntax
IPMT(rate, per, nper, pv, [fv], [type])| Argument | Required | Description |
|---|---|---|
rate | Required | The interest rate per period. |
per | Required | Which payment, from 1 to nper. |
nper | Required | The total number of payments. |
pv | Required | The amount borrowed. |
fv | Optional | The balance to leave at the end. Defaults to 0. |
type | Optional | 0 (the default) for payments at the end of each period, 1 for the start. |
Examples
Every example below was executed against VisiGrid engine 0.54.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=IPMT(0.05/12,1,360,200000) | -833.33 | The first payment on a 200,000 mortgage at 5% — 78% of the 1,073.64 payment is interest. |
=IPMT(0.05/12,360,360,200000) | -4.45 | The last payment is almost all principal. |
=IPMT(0.05/12,1,360,200000)+PPMT(0.05/12,1,360,200000) | -1073.64 | Interest plus principal is always the full payment. |
=IPMT(0.05/12,361,360,200000) | #NUM! | There is no 361st payment. |
Where the payment goes
A fixed-payment loan charges interest on the balance still owed, so interest is largest at the start and shrinks as the balance does. On the example mortgage the first payment is 833.33 interest and 240.31 principal; the last is 4.45 interest and 1,069.19 principal.
Filling IPMT down a column with per running from 1 to 360 gives the interest
line of an amortisation schedule. PPMT gives the principal
line, and the two always add up to the PMT.
For a whole year at once
Summing twelve IPMT cells works, but CUMIPMT does it
in one call — the usual way to find a year’s deductible mortgage interest.
Excel compatibility
Matches Excel, including #NUM! for a period outside 1 to nper.