CUMPRINC Function - Calculating Accumulated Principal Payments in Excel

When investing, if you lack sufficient capital, you'll likely resort to borrowing. Once you've secured the investment loan, you'll want to calculate the total debt repayment by the end of the term. This article guides you on using the CUMPRINC Function to calculate the accumulated principal payments.

Description: The function returns the accumulated principal amount of a loan from the beginning period to the end period. Typically, the function returns a negative value because it represents the amount lost due to borrowing.

Syntax: CUMPRINC(rate, nper, pv, start_period, end_period, type)

In which:

- rate: The interest rate per period, a mandatory parameter.

- nper: Total number of interest-paying periods, a mandatory parameter.

- pv: The present value of the loan amount at the current time, a mandatory parameter.

- start_period

- end_period: The final period among the periods for which accumulated principal payments are to be calculated, a mandatory parameter.

- type: The method of interest payment, where type = 0 => payment at the end of the period, type = 1 => payment at the beginning of the period.

Note:

- rate and nper must use consistent units; for example, if the loan term is in years => interest rate should be in yearly terms.

- If nper, end_period, start_period, or type are decimal numbers => the function takes the integer value of these parameters.

- If pv<0 or rate<0 or nper<0 => the function returns #NUM! value.

- If start_period<1, end_period<1, or start_period>end_period => the function returns #NUM! value.

- Since type only has 2 values, 0 and 1, if the parameter falls outside of these values => the function returns the #NUM! value.

Example:

Calculate the total debt repayment for the loan below in the first month and in the second year with the following table:

1. Calculate the total amount of debt repayment in the first month

Since the interest rate is calculated annually, it needs to be divided by 12 months, and the total loan amount is calculated annually so it needs to be multiplied by the number of years with 12 months.

In the cell where you want to calculate, enter the formula: =CUMPRINC(D7/12,D8*12,D9,1,1,0).

Result:

As it is a debt, it carries a negative value.

2. Calculate the debt repayment in the second year

In the second year, it starts from period 13 to the end of period 24. So in the cell where you want to calculate, enter the following formula: =CUMPRINC(D7/12,D8*12,D9,13,24,0).

Result:

Since it's debt repayment, it carries a negative value. With the CUMPRINC function, you can determine the amount of debt you need to repay when borrowing, helping you organize your work effectively.

Wishing you all the best of luck!