Purpose
Return value
Syntax
=CUMIPMT(rate,nper,pv,start_period,end_period,type)
- rate - The interest rate per period.
- nper - The total number of payments for the loan.
- pv - The present value, or total value of all payments now.
- start_period - First payment in calculation.
- end_period - Last payment in calculation.
- type - When payments are due. 0 = end of period. 1 = beginning of period.
Using the CUMIPMT function
The CUMIPMT function returns the cumulative interest over a range of time defined by a given start and end period. CUMIPMT is useful for financial analysis, particularly in scenarios involving loans and investments. It allows users to calculate the cumulative interest over specific periods, which makes it an important tool for understanding the financial impact of different loan terms or investment strategies. Typical use cases include assessing the total interest outlay on mortgages over various time frames, comparing interest accruals on different loan offers, or analyzing investment growth over time. The CUMIPMT function provides a useful view of financial obligations or growth which can help with more informed decision-making and effective financial planning
Example
Suppose you have a 5-year loan of $10,000 with an annual interest rate of 5% and 12 compounding periods per year. You want to find out the total interest you would pay over the full term of the loan. You can make this calculation with the CUMIPMT function like this:
=CUMIPMT(5%/12,5*12,10000,1,5*12,0)
The inputs to CUMIPMT are as follows:
- rate = 5%/12 = 0.00416 (since it’s an annual interest rate with monthly compounding)
- nper = 5*12 = 60 (a 5-year loan has 60 periods)
- pv = 10,000 (the loan amount)
- start_period = 1 (the first period)
- end_period = 60 (the last period)
- type = 0 (payments at the end of each month)
The result is -1,322.74. This is the total interest paid over the full term of the loan. Notice that the CUMIPMT function in Excel returns a negative value because it represents an outflow of money. If you need a positive value, you can wrap the formula in the ABS function .
Worksheet example
In the example above, all inputs to CUMIPMT are hardcoded to make the formula easier to read. More typically, the inputs will come from cell references. The screen below shows the same example transferred to a worksheet:
The formula in cell C10 is evaluated like this:
=CUMIPMT(C5/C7,C6*C7,C4,1,C6*C7,0)
=CUMIPMT(0.05/12,5*12,10000,1,5*12,0)
=CUMIPMT(0.0041667,60,10000,1,60,0)
-1322.75
Notice that we use the term in years * the periods per year to calculate total periods, instead of hardcoding the number 60. We do this so that the formula will automatically adapt to a different number of compounding periods per year.
Also, notice that the monthly payment is not an input to CUMIPMT. This is because CUMIPMT calculates cumulative interest based on the original principal (or present value), the interest rate, and the provided periods. The periodic (e.g., monthly) payment amount isn’t needed to determine how much interest accumulates across the given period range. To calculate a payment for a loan you can use the PMT function .
Notes
- The interest rate can be provided as a percentage like 5% or a decimal number like 0.05.
- Be consistent with inputs for rate. For example, for a 5-year loan with 6% annual interest, enter the rate as 6%/12.
- The loan value ( pv ) must be entered as a positive value or CUMIPMT will return a #NUM! error.
- The values for start_period and end_period should be integers between 1 and the total number of periods.
- The value for start_period must be less than or equal to the value for end_period .
Purpose
Return value
Syntax
=CUMPRINC(rate,nper,pv,start_period,end_period,type)
- rate - The interest rate per period.
- nper - The total number of payments for the loan.
- pv - The present value, or total value of all payments now.
- start_period - First payment in calculation.
- end_period - Last payment in calculation.
- type - When payments are due. 0 = end of period. 1 = beginning of period.
Using the CUMPRINC function
The CUMPRINC function calculates the cumulative principal amount paid over a specified range of time, defined by the start and end periods of a loan. This function is important for financial analysis, particularly in managing loans and amortization schedules. By calculating the principal portion of loan payments over specific periods, the CUMPRINC function provides useful insights into loan dynamics and helps show the trajectory of loan repayment over time. Typical use cases include evaluating the principal repayment structure of mortgages over various durations, analyzing the principal component in different loan offers, or planning financial budgets.
Example
Assume a 5-year loan for $10,000 with an annual interest rate of 5%. Payments are monthly and there are 12 compounding periods per year. You want to confirm the total principal paid over the full term of the loan. You can calculate the total principal paid with the CUMPRINC function like this:
=CUMPRINC(5%/12,5*12,10000,1,5*12,0)
The inputs to CUMPRINC are as follows:
- rate = 5%/12 = 0.00416 (the annual interest rate divided by 12)
- nper = 5*12 = 60 (a 5-year loan has 60 periods)
- pv = 10,000 (the loan amount)
- start_period = 1 (the first period)
- end_period = 60 (the last period)
- type = 0 (payments at the end of each month)
The result is -10,000, which is the total principal paid over the full term of the loan. As expected, this is the original loan amount. The CUMPRINC returns a negative value because it represents an outflow of money. If you need a positive value, you can wrap the formula in the ABS function like this:
=ABS(CUMPRINC(5%/12,5*12,10000,1,5*12,0))
Worksheet example
In the example above, all inputs to CUMPRINC are hardcoded to make the formula easier to read. In most cases, however, the main inputs will come from cell references. The screen below shows the same example can be set up in a worksheet:
The formula in cell C10 is evaluated like this:
=CUMPRINC(C5/C7,C6*C7,C4,1,C6*C7,0)
=CUMPRINC(0.05/12,5*12,10000,1,5*12,0)
=CUMPRINC(0.0041667,60,10000,1,60,0)
=-10000
Notice that we provide the term as years * periods per year, instead of hardcoding the number 60 into the formula. Excel then calculates a value of 60 for nper before the CUMPRINC function runs. One reason to do it this way is to let Excel handle the math and provide a reminder of how we are deriving nper . More importantly, this also makes it possible for the formula to automatically adapt to a loan with a different term or a loan with a different number of compounding periods per year.
Also, notice that the monthly payment is not an input to CUMPRINC. This is because Excel can determine the regular payment amount based on the interest rate, number of periods, and principal amount. Excel calculates the interest due for a period and subtracts this amount from the payment to determine the principal payment. To calculate a payment for a loan directly you can use the PMT function .
Notes
- The interest rate can be provided as a percentage like 5% or a decimal number like 0.05.
- Be consistent with inputs for rate. For example, for a 5-year loan with 6% annual interest, enter the rate as 6%/12.
- The loan value ( pv ) must be entered as a positive value or CUMPRINC will return a #NUM! error.
- The values for start_period and end_period should be integers between 1 and the total number of periods.
- The value for start_period must be less than or equal to the value for end_period .
