Excel like a finance pro.
IPMT & PPMT
FinanceSplit a loan payment into the interest half that hits the P&L and the principal half that pays the balance down.
Difficulty
1What is it?
PMT tells you what leaves the bank account each month; it doesn't tell you what any of it means. IPMT returns the interest portion of a given payment and PPMT returns the principal portion, and for the same period they always add back to PMT. That split is the whole point: interest is an expense on the P&L, principal is a reduction of a liability on the balance sheet, and booking the full payment to one or the other is a real and common error. Early payments are mostly interest and late ones mostly principal, which is why the split has to be calculated per period rather than averaged.
2What it looks like
IPMT(rate, per, nper, pv, [fv], [type]) · PPMT(rate, per, nper, pv, [fv], [type])- rate
- The rate PER PERIOD — an annual rate over 12 for monthly payments.
- per
- Which payment you're asking about, from 1 to nper. This is the argument PMT doesn't have.
- nper
- Total number of payments.
- pv
- The amount borrowed. Enter it positive and the answers come back negative (money leaving).
- [fv]
- Balance left at the end. 0 by default — a balloon payment goes here.
- [type]
- 0 (default) for payments at the end of each period, 1 for the beginning.
3When you use it
- Book the month's loan payment correctly: interest to the P&L, principal against the note.
- Budget next year's interest expense without building the whole amortization table.
- Show a lender or a board how little of an early payment actually reduces the balance.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Interest goes to the P&L, principal comes off the loan. Move the period and watch the mix flip.
=IPMT(B2/100/12, B5, B3, B4) · =PPMT(B2/100/12, B5, B3, B4)Payment 1 of 60: $2,900| A | B | C | |
|---|---|---|---|
| 1 | Input | Value | Result |
| 2 | Annual rate % | Payment 1 of 60: $2,900 | |
| 3 | Term (months) | Interest (P&L): $750 — 26% of it | |
| 4 | Loan amount | Principal (balance sheet): $2,150 | |
| 5 | Payment number | They add back to PMT: $2,900 |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
The two don't add up to PMTDifferent arguments were passed to each — usually a different rate or period.Fix: Keep every argument identical across IPMT, PPMT and PMT. Only `per` differs between payments.
#NUM!per is 0, negative, or larger than nper.Fix: Periods are 1-based: the first payment is 1, not 0.
Signs look wrongpv was entered negative as well, so the result comes back positive.Fix: Pick one convention. Positive pv with negative results (money out) is the usual one; wrap in ABS only for display.
Annual figures on a monthly loanThe annual rate was passed without dividing, or nper was given in years.Fix: rate/12 and nper in months — both, or neither.
6Better functions & alternatives
- PMT — The total payment. IPMT and PPMT are the two halves it's made of.
- Amortization schedule — The full table, period by period — what you get when you run these two down a column.
- CUMIPMT / CUMPRINC — The same split totalled across a range of periods, which is how interest actually gets budgeted.
Want IPMT & PPMT already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.