Home » Excel » CUMPRINC Excel

CUMPRINC Excel

ExcelMojo Team
Article byExcelMojo Team
Avatar
Reviewed byDheeraj Vaidya, CFA, FRM

What Is CUMPRINC Excel Function?

The CUMPRINC Excel function is a financial function used to calculate the cumulative principal amount paid on a loan over a specific period. This function is commonly employed in financial analysis and planning to determine the total principal amount that has been paid down on a loan, such as a mortgage or car loan. The CUMPRINC function is used to manage finances better and understand the breakdown of loan payments.

Let us consider a 3-year loan of $1200 with an annual interest rate of 10% and 12 compounding periods per year. To find the total interest you will end up paying over the entire term of the loan, you can use the CUMPRINC function as follows.

CUMPRINC-Excel-Definition

The formula used in this example is: =CUMPRINC(B2/12,B3*12,B4,B5,B6,0). The interest amount calculated is in cell B8, as shown below.

CUMPRINC-Excel-Definition-1

As you can see, the result is -760, and this is the total principal paid over the loan’s full term. In this article, we learn how to use the CUMPRINC function in Excel.

Table of contents
Key Takeaways
  • The CUMPRINC Excel function calculates the cumulative principal paid on a loan over a specific period.
  • The syntax of the function is: =CUMPRINC (rate, nper, pv, start_period, end_period, type).
  • The function helps users track how much of each payment goes towards reducing the loan balance.
  • This tool is used for individuals and businesses to manage their finances and make informed decisions about future payments.

Syntax

Syntax
  1. Rate – (Mandatory) This is the interest rate for each period.
  2. Nper – (Mandatory) This is the total number of payment periods and refers to the number of times a loan or investment must be paid back in full.
  3. Pv – (Mandatory) This is the current value of the loan or investment.
  4. Start_period – (Mandatory) This is the starting period for calculating interest, which must be a whole number between 1 and the total number of periods specified.
  5. End_period – (Mandatory) This is the closing time frame for interest calculations, which must be a whole number between 1 and the total specified periods.
  6. Type – (Mandatory) This is the payment made: either at the start of the period as 0 or at the end of the period as 1. It is an integer that indicates the timing of the payment.

How To Use CUMPRINC Function in Excel?

To use the CUMPRINC function in Excel, follow these steps.

#1 – Access from the Excel ribbon

  1. Choose a cell with no data and then click on the Formulas tab.

    Step-1-1

  2. In the Excel ribbon, choose the Financial option.

    Step-1-2

  3. Choose the CUMPRINC option from the drop-down menu.

    Step-1-3

  4. To input values for arguments in the Function Arguments window, enter values for rate, nper, pv, start_period, end_period, and type.
    Click OK to proceed.

    Step-1-4

#2 – Enter the worksheet manually

Step 1: To assign a vacant cell for output using the CUMPRINC Excel function, input =CUMPRINC() into the cell, with the appropriate values in the braces. Alternatively, type =C and double-click on CUMPRINC from the list of suggestions in Excel.

Step-2-1

Step 2: To get the result you want, press the Enter key.

Examples

Let us look at a few examples to understand the CUMPRINC function in detail.

Example #1

Let us consider a 2-year loan of $1000 with an annual interest rate of 10% and 12 compounding periods per year. You must find the cumulative principal over the entire term of the loan. This calculation can be easily determined using the CUMPRINC function as follows:

CUMPRINC-Excel-Example-1

Step 1: The formula used in this example is:

=CUMPRINC(B2/12, B3*12, B4, B5, B6, 0).

  1. B2/12 is the annual interest rate divided by 12.
  2. Nper is B3*12 as the 2-year loan has 24 payment periods.
  3. B4, B5, and B6 are the loan amounts, as well as the start and end periods
  4. The type is zero as the payment is at the end of each month.
Example-1-Step-1

Step 2: The amount calculated is in cell B8, as shown in the image below.

Example-1-Step-2

As you can see, the result is -1000 and is the total principal paid over the loan’s full term. The calculated payments are shown as negative values, representing payments made by the borrower.

The CUMPRINC function in Excel calculates principal payments more accurately with higher compounding frequencies, such as 12 times per year, because interest is accrued and compounded monthly, reflecting the effect of compounding more frequently.

Example #2

To calculate the total principal paid on a $3,000 loan with a 12% interest rate over 20 years and four compounding periods per year, you can use the CUMPRINC function. It will help you determine the total amount of cumulative principal you will end up paying over the entire term of the loan.

CUMPRINC-Excel-Example-2

Let’s start the calculation by entering the values as prescribed below.

Step 1: Insert the given formula to calculate the interest amount of an asset in cell B8. =CUMPRINC(B2/4, B3*4, B4, B5, B6, 0).

  1. B2/4 is the annual interest rate divided by 4. (as it is a quarterly payment)
  2. Nper is B3*4 as the 20-year loan has 80 payment periods.
  3. B4, B5, and B6 are the loan amounts, as well as the start and end periods.
  4. The type is zero as the payment is at the end of each month.
Example-2-Step-1

Step 2: The cumulative principal value is attained in cell B8 as ‘-3000.’

Example-2-Step-2

Whenever this formula is used, ensure that you are consistent in using the correct units for rate and nper.

Example #3

The example shows how to use the Excel CUMPRINC function to calculate the total interest paid annually on a $100,000 loan with a 4% annual interest rate spread over 5 years. We have to make monthly payments due at the end of each month.

CUMPRINC-Excel-Example-3

Step 1: Let us insert the formula to calculate the cumulative principal amount of an asset in its first year in cell B8.

Step 2: The complete formula is =CUMPRINC(B2/12, B3*12, B4, B5, B6,0).

  1. B2/12 is the annual interest rate divided by 12.
  2. Nper is B3*12 as the 5-year loan has 80 payment periods.
  3. B4, B5, and B6 are the loan amounts, as well as the start and end periods.
  4. The type is zero as the payment is at the end of each month.
CUMPRINC Excel-Example-3-Step-2

Step 3: The value is attained in cell B8 as ‘-18435’ for the first year. Now, let us calculate the cumulative principal for each of the successive years by dragging the formula from B8 to F8.

CUMPRINC Excel-Example-3-Step-3

Likewise, we can find values for other years, as shown in the below image.

CUMPRINC Excel-Example-3-Step-3-1

The CUMPRINC Excel function helps calculate yearly principal payments. It allows for a detailed analysis of cumulative principal over a set number of periods, helping financial professionals make informed decisions on investments or loan terms.

Important Things To Note

  1. The CUMPRINC Excel function can be used to determine the overall cost of borrowing, forecast cash flow requirements, and develop repayment strategies. 
  2. The #NUM! error occurs when:
  3. The start or end period is less than or equal to zero.
  4. When the start period is greater than the end period
  5. When rate, nper, or PV are less than or equal to zero.
  6. When the argument ‘type’ is neither 0 nor 1.
  7. The #VALUE! error occurs when a cell contains a value that is not recognized as a number.

Frequently Asked Questions (FAQs)

1. What are some common scenarios where the CUMPRINC function would be useful?

• One common scenario is in calculating the total principal payments made over time for a mortgage or other type of loan. By using this function within, we can quickly and accurately determine how much of each payment goes towards reducing the principal balance of the loan.

• The CUMPRINC function can also be used to track principal payments on investments such as bonds or structured products, providing insights into investment performance and potential returns.

2. Are there any limitations or caveats to be aware of when using the CUMPRINC function in Excel?

• The function assumes that each payment is made on time and in full, which may not always be the case in real-world scenarios.
• The function does not consider any changes in interest rates or payment terms over the course of the loan. This means that it may not accurately reflect the total amount of principal paid overtime if there are modifications to the loan agreement.
• Financial calculation in Excel is important to ensure that all input data is accurate and consistent to avoid errors in the results generated by the CUMPRINC function. 

3. Can the CUMPRINC function be combined with other functions to perform more complex calculations?

Yes, the CUMPRINC function can indeed be combined with other Excel functions to perform more complex calculations. By incorporating functions such as SUM, IF, and COUNTIF, users can further manipulate the data generated by CUMPRINC to achieve specific results based on a set of conditions or criteria. By combining CUMPRINC with other financial functions like PMT or RATE, users can conduct comprehensive analyses of loan repayments and interest rates for various scenarios. 

Download Template

This article must help us understand the CUMPRINC Excel Function’s formula and examples. You can download the template here to use it instantly.

CUMPRINC-ExcelDownload

Guide to CUMPRINC Excel Function. Here we explain how to use CUMPRINC function with examples & downloadable excel template. You can learn more from the following articles. –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *