CUMIPMT & CUMPRINC Cumulative Loan Formulas
Learn how to calculate cumulative interest and principal paid over multi-year loan periods in Excel using CUMIPMT and CUMPRINC. Audit tax deductions cleanly.
Corporate tax accountants and loan officers often need to know the total interest paid during a specific fiscal year (such as Months 1 to 12) rather than just a single monthly payment. While IPMT calculates interest for one month at a time, generating 12 separate formulas and summing them is slow.
Excel provides two cumulative loan formulas that calculate total interest or principal between any two periods in a single cell: CUMIPMT and CUMPRINC.
Quick Comparison: Cumulative Loan Formulas
| Formula | What It Accumulates | Typical Use Case |
|---|---|---|
CUMIPMT |
Total interest paid between period $t_1$ and $t_2$ | Corporate tax write-offs for interest expenses in a given tax year. |
CUMPRINC |
Total principal repaid between period $t_1$ and $t_2$ | Calculating borrower home equity build-up and remaining balance reductions. |
The Strict 6-Argument Rule
=CUMIPMT(rate, nper, pv, start_period, end_period, type)
=CUMPRINC(rate, nper, pv, start_period, end_period, type)Unlike most Excel formulas where the final argument is optional, CUMIPMT and CUMPRINC require all 6 arguments. If you omit the type argument at the end, Excel will reject the formula with an error.
type = 0: Payments made at the end of the period (standard loan convention).type = 1: Payments made at the beginning of the period.
Hands-on Practice Playground
Use the commercial credit facility table below to audit cumulative interest and principal payments over specific operating years. Follow each objective to complete the exercise.
In cell D2, calculate the total Year 1 interest (months 1 to 12) using CUMIPMT (prefixed with a minus sign to display as positive).
In cell D2, calculate the total cumulative interest paid in Year 1 (period 1 to 12) using CUMIPMT: monthly rate $B$3/$B$5, total periods $B$6, principal $B$2, start period 1, end period 12, and type 0 (make positive by prefixing with -).
In cell E2, calculate the total Year 1 principal repaid using CUMPRINC.
In cell E2, calculate the cumulative principal paid in Year 1 (period 1 to 12) using CUMPRINC with the same loan parameters and type 0 (prefixed with -).
In cell D3, calculate cumulative interest paid in Year 2 (months 13 to 24).
In cell D3, calculate the cumulative interest paid in Year 2 (period 13 to 24) using CUMIPMT with start period 13, end period 24, and type 0 (prefixed with -).
Ready to apply CUMIPMT & CUMPRINC Cumulative Loan Formulas in a real workbook?
Put professional financial formulas into practice inside an executive cash flow model.
Open 12-Month Cash Flow StatementShare This Lesson
Spread the word with your peers and challenge your friends