CUMPRINC
Returns the cumulative principal paid on a loan between two specified periods. Complements CUMIPMT to show how much of your payments reduce the loan balance.
CUMPRINC(rate, nper, pv, start_period, end_period, type)Arguments
rateThe interest rate per period. For monthly payments, divide annual rate by 12.nperThe total number of payment periods in the loan.pvThe present value (principal) of the loan.start_periodThe first period in the calculation. Payment periods are numbered starting at 1.end_periodThe last period in the calculation.typeThe timing of payments. 0 = end of period (ordinary annuity), 1 = beginning of period (annuity due).
=CUMPRINC(5%/12, 60, 10000, 1, 12, 0)Total principal paid in year 1 of a 5-year $10,000 loan at 5% annual rate
=CUMPRINC(5%/12, 60, 10000, 1, 60, 0)Total principal paid over the full life of the loan equals the original loan amount
=ABS(CUMPRINC(6%/12, 360, 200000, 1, 12, 0))Principal paid in year 1 of a 30-year $200,000 mortgage at 6%
- •Result is negative because it represents outgoing cash
- •The sum of CUMPRINC over all periods equals the original loan amount (pv)
- •Use ABS() to get a positive value for reporting purposes
- •Combine with CUMIPMT to fully analyze any amortizing loan
- •Forgetting that CUMPRINC's result is negative, representing outgoing principal payments, which can look like an error to anyone expecting a positive total
- •Using an annual rate without dividing it by 12 for monthly payments, or forgetting to multiply the loan term by 12 for nper - a common mismatch across all the loan-related financial functions
- •Mixing up start_period and end_period, or using a start_period less than 1, which raises a #NUM! error since payment periods are numbered starting at 1, not 0
CUMIPMTCalculates the cumulative interest paid over the same period range, the complementary piece of a loan payment alongside CUMPRINC's principal.PPMTCalculates the principal portion of a single specific payment, rather than CUMPRINC's total across a range of periods.PMTCalculates the total payment amount per period, which CUMPRINC and CUMIPMT together split into principal and interest.Why is CUMPRINC's result negative?
It follows the standard cash flow convention where money paid out is negative. Wrap the formula in ABS() if you need a positive number for a report.
Does the total of CUMPRINC across all periods equal the original loan amount?
Yes, summing CUMPRINC over every period of the loan (from period 1 to the final nper) returns a value equal to the original loan amount (pv), since all the principal eventually gets paid off.
Why does CUMPRINC return a #NUM! error?
start_period is likely less than 1, or start_period is greater than end_period - payment periods in CUMPRINC are numbered starting at 1, and the start must come before or equal the end.
Need to translate a formula using CUMPRINC?
Use our translator to convert your complete formula