How to Use the CUMPRINC Function in Excel

This guide explains how to use CUMPRINC Function in Excel, including its syntax, required arguments, practical examples, and common errors.

Description: The function returns the cumulative capital amount of a loan from the beginning of the period to the end of the period, usually the function returns a negative value because the value of the function represents the amount of money lost due to borrowing.

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

Inside:

- rate : The interest rate of each period, is a required parameter.

- nper : Total number of periods with interest payment, a required parameter.

- pv : The value of the loan amount at the present time, is the required parameter.

- start_period : The first of the periods in which you want to calculate the cumulative capital amount, is the required parameter.

- end_period : The last of the periods in which you want to calculate the cumulative capital payables, is the required parameter.

- type : Method of interest payment, if type = 0 => interest paid at the end of the period, type = 1 => interest paid at the beginning of the period.

Attention:

- rate, nper must use a consistent unit, for example, loan is annualized => interest is calculated by year.

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

- If the function pv returns the #NUM!

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

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

For example:

Calculate the total liabilities of the loan below in the first month and in the second year with the following table:

1. Calculate the total amount of liabilities payable in the first month

Since the interest rate is per year, it is necessary to divide the interest rate by 12 months, the total loan amount by the year, so the number of years times 12.

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

Result:

Because it's a debt, it has a negative value.

2. Calculating liabilities in the second year

In 2nd year starting from the expiry of the period 13 to 24. So why should count box enter the following formula: = CUMPRINC (D7 / 12, D8 * 12, D9,13,24,0) .

Result:

Because the debt calculation should be negative. So with CUMPRINC function you will identify the debt you have to pay when the loan helps you adjust your work arrangements.

Good luck!