Excel CUMIPMT and CUMPRINC: Total Interest and Principal Over a Period
Total the interest, or the capital, paid between any two payment numbers of a loan.
PMT gives the payment, and IPMT and PPMT split a single payment into interest and capital. These two do the same split across a *range* of payments: how much interest went out in year one, how much of the loan was actually repaid in the first three years.
That saves building the amortisation schedule at all. Without them you would need a row per month and a SUM over the slice you care about; CUMIPMT takes the start and end payment numbers directly and returns the total.
Two things trip people up. Payments are numbered from 1, not 0, so the first year of a monthly loan is periods 1 to 12 — using 0 as the start gives #NUM!. And the final `type` argument is required here even though it is optional in PMT; leaving it out is an argument-count error rather than a default.
Syntax
=CUMIPMT(rate, nper, pv, start_period, end_period, type)Arguments
rateRequired- The rate per period. Divide an annual rate by 12 for monthly payments.
nperRequired- The total number of payments over the whole loan, matching the rate's unit.
pvRequired- The amount borrowed. Enter it positive and the totals come back negative.
start_periodRequired- The first payment to include, counting from 1. Zero is invalid.
end_periodRequired- The last payment to include. It must be at least start_period.
typeRequired- 0 for payments at the end of each period, 1 for the start. Unlike in PMT this argument is mandatory.
The example data
Headers in row 1, data in A2:E4. Rates are annual, terms in years.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Asset | Cost | Annual rate | Years | Salvage |
| 2 | Delivery van | 24000 | 0.059 | 5 | 4000 |
| 3 | Packing line | 85000 | 0.072 | 10 | 9000 |
| 4 | Forklift | 12500 | 0.045 | 3 | 2500 |
Worked examples
=CUMIPMT(C2/12, D2*12, B2, 1, 12, 0)Result: -1290.83
Interest paid in the first year of the van loan. Negative because it is money going out.
=CUMPRINC(C2/12, D2*12, B2, 1, 12, 0)Result: -4261.09
Capital repaid over the same twelve payments. Add the two and you get twelve times the PMT.
=-CUMIPMT(C2/12, D2*12, B2, 1, D2*12, 0)Result: 3759.60
Total interest over the whole loan, shown positive. Period 1 to the last period covers everything.
=-CUMIPMT(C3/12, D3*12, B3, 1, 12, 0)Result: 6019.24
First-year interest on the packing line. Early payments are mostly interest, which is what these functions make visible.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#NUM!
Why it happens: start_period is 0 or below, end_period is beyond nper, or start_period is later than end_period.
How to fix it: Payments count from 1. The first year of a monthly loan is 1 to 12, not 0 to 11.
#VALUE!
Why it happens: The type argument was omitted. It is optional in PMT and required here, which is an easy assumption to carry over.
How to fix it: Pass 0 explicitly for end-of-period payments.
The result is negative
Why it happens: The cash-flow convention — interest and capital paid are outflows.
How to fix it: Put a minus in front of the function to display it positively, or enter pv as negative.
Totals do not match the schedule
Why it happens: The rate and nper are in different units from the period numbers being passed.
How to fix it: If nper is in months then start_period and end_period are month numbers too.
Tips worth knowing
- CUMIPMT plus CUMPRINC over the same range always equals the number of payments times PMT.
- Use them to build a yearly summary table without a monthly schedule: one row per year, periods 1-12, 13-24 and so on.
- For a single payment rather than a range, IPMT and PPMT are the right functions.
- Both are in the Analysis ToolPak in very old versions; from Excel 2007 they are built in.
Frequently asked questions
How do I find the total interest paid in year one?
=CUMIPMT(rate/12, years*12, amount, 1, 12, 0). Periods 1 to 12 are the first twelve monthly payments. For year two use 13 to 24, and so on.
Why do I get #NUM!?
Almost always because start_period is 0. Excel numbers payments from 1, so the first payment is period 1. The other cause is an end_period beyond the total number of payments.
What is the difference from IPMT?
IPMT returns the interest portion of one specific payment. CUMIPMT totals the interest across a range of payments. Use IPMT for a single row of a schedule and CUMIPMT to skip building the schedule.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.