IPMT calculates just the interest portion of a specific loan payment — useful for building a full amortization schedule.
Every loan payment is split between interest and principal. IPMT calculates just the interest portion of one specific payment in the schedule.
Early in a loan, more of each payment goes to interest; later, more goes to principal — IPMT lets you see exactly how that split changes payment by payment.
=IPMT(rate, per, nper, pv, [fv], [type])| Argument | Description |
|---|---|
| rate required | The interest rate per period. |
| per required | Which payment number to calculate (1 for the first payment, 2 for the second, etc). |
| nper required | The total number of payments. |
| pv required | The loan amount. |
| fv optional | The remaining balance after the last payment. Defaults to 0. |
| type optional | 0 = end of period (default), 1 = start. |
=IPMT(0.05/12,1,36,5000)On a $5,000 loan at 5% annual interest over 36 months, the first payment's interest portion is about -$20.83.
=IPMT(0.05/12,36,36,5000)By the final payment, the interest portion is much smaller since most of the balance has already been repaid.
=IPMT(0.05/12,ROW()-1,36,5000)Using ROW() lets this formula be copied down a column, automatically calculating the interest portion for each successive payment number.
IPMT and PPMT together always add up to the total fixed payment (PMT) for that period. IPMT is the interest share; PPMT is the principal share. Early in a loan, IPMT is larger; later, PPMT takes over as more of the balance has already been paid down.
Asking for the interest on payment 40 of a 36-payment loan returns a #NUM! error.
ExcelPro has 700+ hands-on Excel exercises across 7 career tracks — free to start, no download needed.
Start practicing free →