Excel like a finance pro.
CUMIPMT & CUMPRINC
FinanceTotal the interest and principal across a range of periods — how interest expense actually gets budgeted.
Difficulty
1What is it?
IPMT and PPMT answer for one payment. Nobody budgets one payment. CUMIPMT and CUMPRINC total the same two halves across a RANGE of periods — payments 13 to 24, say — which is the number that goes in next year's interest line without building a 60-row schedule to get it. Both are strict about their arguments in a way the rest of the family isn't: every one is required, periods are 1-based, and the type argument has no default, so leaving it off is an error rather than an assumption.
2What it looks like
CUMIPMT(rate, nper, pv, start_period, end_period, type) · CUMPRINC(same arguments)- rate
- The rate per period — annual over 12 for monthly payments.
- nper
- Total number of payments over the whole loan, not the range you're totalling.
- pv
- The amount borrowed, positive.
- start_period
- First payment in the range, counting from 1.
- end_period
- Last payment in the range, inclusive.
- type
- REQUIRED here, unlike everywhere else: 0 for end-of-period payments, 1 for beginning. There is no default.
3When you use it
- Next year's interest expense, in one cell, for the budget.
- How much principal a year of payments actually retires.
- Split a loan's total cost across the years it spans for a board pack.
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.
Pick a year and see what the budget line should say — no 60-row schedule needed.
=CUMIPMT(B2/100/12, B3, B4, 1, 12, 0)Payments 1–12| A | B | C | |
|---|---|---|---|
| 1 | Input | Value | Result |
| 2 | Annual rate % | Payments 1–12 | |
| 3 | Term (months) | Interest (the budget line): $8,279 | |
| 4 | Loan amount | Principal retired: $26,520 | |
| 5 | Year | Balance left after: $123,480 |
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
#NUM!start_period < 1, end_period < start_period, or either exceeds nper. Also fires if rate, nper or pv is ≤ 0.Fix: Periods count from 1 and both ends are inclusive. Year two of a monthly loan is 13 to 24, not 12 to 24.
#NUM! on a valid-looking calltype was left off.Fix: Pass it explicitly. These two functions genuinely require it.
Totals don't reconcile to the schedulenper was set to the length of the RANGE rather than the length of the loan.Fix: nper is always the full term. The range is start_period and end_period.
6Better functions & alternatives
- IPMT / PPMT — The same split for a single period — these are those two, summed over a range.
- Amortization schedule — Build the table when you need to see the path, not just the year's total.
Want CUMIPMT & CUMPRINC 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.