What is CUMPRINC in Google Sheets?
The CUMPRINC function in Google Sheets calculates the cumulative principal paid on a loan between two specific payment periods. We commonly use it for loan and mortgage calculations to determine the amount from the total repayment that goes toward reducing the loan balance rather than paying interest. It is very helpful for creating amortization schedules, evaluating early repayment impacts, and financial planning.
To further understand the function, consider this example. Suppose we have a loan of $10,000 at a 6% annual interest rate, to be repaid monthly over 5 years. We want to calculate the total principal paid in the first year, up to month 12. We use the formula:
=CUMPRINC(6%/12, 60, 10000, 1, 12, 0)
Press Enter. The result will show the total principal paid during that period. This helps visualize how your repayments contribute to reducing your loan amount over time.
Key Takeaways
- The CUMPRINC function in Excel or Google Sheets calculates the total principal paid on a loan over a specific range of payment periods.
The syntax is: =CUMPRINC(rate, nper, pv, start_period, end_period, type).
- It is used to isolate just the principal portion of loan payments, not including interest.
- CUMPRINC is ideal for analyzing loan amortization and understanding how much of a loan is paid off in a given time span.
- This function differs from PMT, which calculates the total periodic payment; CUMPRINC focuses only on the reduction of the principal balance.
Syntax
The syntax of the function is as follows:
CUMPRINC in Google Sheets formula
=CUMPRINC(rate, number_of_periods, present_value, first_period, last_period, end_or_beginning)
- rate – The interest rate.
- number_of_periods – The number of payments to be made.
- present_value – The current value of the annuity.
- first_period – The number of the payment period to begin the cumulative calculation. It must be greater than 1.
- last_period – The number of the payment period to end the cumulative calculation. It must be greater than first_period.
- end_or_beginning – Whether payments are due at the end (0) or beginning (1) of each period.
How To Use CUMPRINC Function in Google Sheets?
The CUMPRINC function in reality stands for Cumulative Principal and calculates the total amount of principal paid on a loan between two specific payment periods. It clarifies how much of your loan repayments go toward reducing the actual loan balance.
- Use CUMPRINC by manually typing the formula
- Selecting it from the Google Sheets menu
Let’s first look at the manual method using a simple example.
Entering CUMPRINC Manually
Let us consider an example to calculate the total principal paid on a $20,000 loan over the first year. The loan has a 5% annual interest rate and is to be repaid monthly over 4 years.
Step 1: Enter the following loan details in a Google Sheet:
Step 2: In cell B9, where we want the result, type:
=CUMPRINC(
Step 3: Now use the cell references for all arguments, in the order specified in the syntax.
=CUMPRINC(B3, B5, B4, B6, B7, B8)
Close the parentheses.
Step 4: Press Enter. The result will show the total principal repaid from months 1 to 12 of the first year. You’ll see a negative value as it indicates a cash outflow.
Using CUMPRINC From the Google Sheets Menu
- Select the cell where you want the result.
- Click on the Insert menu. Go to Function → Financial.
- Choose CUMPRINC from the list.
- Enter all the required arguments either as values or cell references.
- Press Enter to calculate the cumulative principal over the given period.
Examples
The CUMPRINC function helps in budgeting, understanding loan structures, and evaluating the impact of early repayments. Below are some practical examples that show how to apply the CUMPRINC function in everyday financial planning.
Example #1
A person has taken a loan of $25,000 at an annual interest rate of 4.5%, to be repaid over 36 months with monthly payments. They want to calculate the total principal they will have repaid over the two-year loan term. Using the CUMPRINC function, find out how much of their total payments go toward reducing the loan principal.
Always remember that before starting, you should also adjust the number of periods and rate to match the payment frequency.
Step 1: Enter all the details in a Google Sheet.
Step 2: To calculate the total principal for the 24 months, use the CUMPRINC function as follows:
=CUMPRINC(B2, B3, B4, B5, B6, B7). Press Enter.
Thus, you get the value repaid over 24 months.
Example #2 – Using CUMPRINC with UMINUS Function
Let us consider a CUMPRINC in Google Sheets example where a company has taken a business loan of $15,000 to be repaid over 4 years with monthly payments at an annual interest rate of 5%. The company wants to know how much total principal it will have paid off in the first two years, so they can report it as a positive value in their financial dashboard. Since the CUMPRINC function returns a negative number by default (representing outflow), we’ll use the UMINUS function to convert it into a positive number for cleaner reporting.
Step 1: First, enter the relevant loan data into the sheet:
- Annual interest rate: 5%
- Monthly rate: =5%/12
- Total periods: 48 (4 years × 12 months)
- Present loan value: 15000
- Start period: 1
- End period: 24 (for the first 2 years)
- Type: 0 (end-of-period payment)
Step 2: In a blank cell, enter the CUMPRINC function inside UMINUS to get a positive result:
=UMINUS(CUMPRINC(B2, B3, B4, B5, B6, B7))
Step 3: Press Enter. The function will return a positive value showing the total principal paid during the first 24 months.
This combination is especially useful in dashboards or reports where one must present negative values (like loan payments) as positive for clarity.
The UMINUS function changes the sign of the cumulative principal from negative to positive.
Example #3 – Using CUMPRINC with PMT Function
Let us consider a scenario where a person has taken a car loan of $20,000 at a 6% annual interest rate, to be paid off over 5 years with monthly payments. The person wants to know how much principal they will have paid off in the first 3 years of the loan.
Step 1: Enter the required data.
Step 2: Calculate the Monthly Payment using the PMT function
Click on a blank cell and enter:
=PMT(B1/12, B3, -B4)
Where:
- B1 = Annual Interest Rate
- B2 = Payments per Year
- B3 = Loan Term
- B4 = Loan Amount
This is the monthly payment including principal and interest.
Step 3: Calculate the principal paid in the first two years.
Click on another blank cell and enter:
=CUMPRINC(B2, 36, B4, B5, B6, 0)
Where:
- B1 = Annual Interest Rate
- B2 = Payments per Year
- B3 = Loan Term
- B4 = Loan Amount
- B5 = Start Period (1)
- B6 = End Period (36)
0 means payments are due at the end of the period (typical for most loans)
The person will have paid off $20,000 of the original $20,000 loan in just the first three years, helping them see how fast they’re reducing the actual debt.
Important Things to Note
- Ensure that you use consistent units for rate and number_of_periods. They should not be representing month or year values in contrast.
- For quarterly payments, you must divide the annual interest rate by 4. Also, divide the number of periods by 3 (since there are 3 months in a quarter).
- For semi-annual payments, divide the annual interest rate by 2 and divide the number of periods by 6.
- Use the annual interest rate as given, and divide the number of periods by 12 as there are 12 months in a year for annual payments.
Frequently Asked Questions (FAQs)
The CUMPRINC function requires a periodic interest rate that matches the loan’s payment frequency. If one makes monthly payments, divide the annual rate by 12. For quarterly payments, divide the annual interest rate by 4. Always express the rate as a decimal value to avoid calculation errors.
• Annual rate for monthly payments: divide by 12.
• Use decimals: e.g., 6% becomes 0.06.
A #NUM! error usually occurs when one or more input values are invalid or out of the accepted range.
The start period must be greater than 0 and less than or equal to the total number of payments.
The end period must be equal to or greater than the start period and within the payment schedule.
The rate and number of periods must be positive numbers.
If the “type” argument is not 0 or 1, the function will return an error.
The “type” argument specifies whether the payments are made at the beginning or end of each period. A value of 0 means that the payment is due at the end of the period, which is the default scenario. A value of 1 means payments are made at the beginning of each period. This argument affects how interest and principal are calculated in the early stages of the loan.
Download Template
This article must help understand CUMPRINC Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is CUMPRINC Function in Google Sheets. We learn how to use CUMPRINC function in google sheets with its syntax and examples. You can learn more from the following articles. –
Leave a Reply