CUMIPMT
Returns the cumulative interest paid on a loan between two specified periods. Useful for calculating the total interest paid in any range of payment periods.
CUMIPMT(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).
=CUMIPMT(5%/12, 60, 10000, 1, 12, 0)Total interest paid in year 1 of a 5-year $10,000 loan at 5% annual rate
=CUMIPMT(5%/12, 60, 10000, 13, 24, 0)Total interest paid in year 2 of the same loan
=CUMIPMT(5%/12, 60, 10000, 1, 60, 0)Total interest paid over the full life of the loan
- •Result is negative because it represents outgoing cash (interest payments)
- •Divide annual rate by 12 for monthly loan calculations
- •Use ABS() around CUMIPMT to display the result as a positive number
- •Pair with CUMPRINC to split payments into interest and principal
- •Forgetting that CUMIPMT's result is negative, representing outgoing interest 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
CUMPRINCCalculates the cumulative principal paid over the same period range, the complementary piece of a loan payment alongside CUMIPMT's interest.IPMTCalculates the interest portion of a single specific payment, rather than CUMIPMT's total across a range of periods.PMTCalculates the total payment amount per period, which CUMIPMT and CUMPRINC together split into interest and principal.Why is CUMIPMT'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.
Why does CUMIPMT return a #NUM! error?
start_period is likely less than 1, or start_period is greater than end_period - payment periods in CUMIPMT are numbered starting at 1, and the start must come before or equal the end.
How do I get the interest paid in just a single month using CUMIPMT?
Set start_period and end_period to the same value, like CUMIPMT(rate,nper,pv,5,5,0) to get just the fifth period's interest.
Need to translate a formula using CUMIPMT?
Use our translator to convert your complete formula