How to use the CUMPRINC function
What is the CUMPRINC function?
The CUMPRINC function calculates the accumulated principal based on a start and end period on a loan.
Table of Contents
1. Introduction
What is accumulated principal?
The original principal is the amount originally borrowed. As payments are made part of each payment goes towards reducing the principal and the remaining part pays the interest.
On a loan, accumulated principal increases over time as payments are made until the loan is repaid.
What is an annuity loan?
An annuity loan is a type of loan where the borrower makes equal periodic payments (usually monthly or annually) over a fixed term. These periodic payments consist of both principal and interest components. The interest is calculated based on the outstanding loan balance, and the principal repayment portion gradually increases over time as the interest portion decreases.
2. Syntax
CUMPRINC(rate, nper, pv, start_period, end_period, type)
| rate | Required. The interest rate. |
| nper | Required. The total number of periods in an annuity.
Examples: |
| pv | Required. Present value. |
| start_period | Required. The first period. |
| end_period | Required. The last period. |
| type | Required. When payments are due.
0 - End of period. |
What is an annuity?
An annuity is a series of equal payments made over a defined period of time. The payments are usually made monthly, quarterly, or annually. These payment intervals are the "periods" that nper counts.
For example, if an annuity pays out quarterly for 3 years, there would be:
4 payments per year
3 years
So nper = 4 * 3 = 12 total payment periods
What is interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
What is a monthly payment?
A monthly payment is a scheduled payment that occurs every month as part of an ongoing loan, mortgage, or other finance agreement.
What is a quarterly payment?
A quarterly payment is a scheduled payment that occurs every quarter as part of an ongoing loan, mortgage, or other finance agreement.
3. Example 1
You have taken a loan of $100,000 from a bank at an annual interest rate of 5%, with monthly payments over a period of 10 years. Calculate the cumulative principal paid at the end of year 3 for the loan term?
The image displays the arguments required for the CUMPRINC function in Excel, along with their respective values.
Here's a description of each argument:
- Rate: This argument represents the interest rate per period. In the example, the rate is set to 5%.
- Nper: This argument is the total number of payment periods. In the example, it is set to 10, which could represent 10 years, 10 months, or 10 of any other period, depending on the context.
- Pv: This argument is the present value or the loan amount. In the example, it is set to $100,000.00.
- Start_period: This argument specifies the starting period for which the cumulative principal should be calculated. In the example, it is set to 25, indicating that the calculation will start from the 25th period.
- End_period: This argument specifies the ending period for which the cumulative principal should be calculated. In the example, it is set to 36, indicating that the calculation will end at the 36th period.
- [Type]: This argument is optional and represents the timing of the payments. In the example, it is set to 0, which means that the payments are due at the end of each period.
The image above shows the CUMPRINC function in cell E3 calculating the accumulated principal for month 25 to 36 for a 10 year loan of 100 000. The chart demonstrates the principal (blue curve) over time starting from 1 to and ends on 120, 10 years have 120 months (periods). The principal increases over time as payments are made until the loan is repaid, the interest however decreases over time.
Formula in cell E3:
The CUMPRINC function is used to calculate the cumulative principal paid on a loan or investment between the specified start and end periods, considering the given rate, number of periods, and present value. The output of the function in the example is ($8,737.26), which represents the cumulative principal paid between periods 25 and 36 for the given loan amount and interest rate.
Periods 25 to 36 represents the months in year 3. Use the same unit for rate and nper, the above example uses monthly payments. That is why the interest rate is divided by 12 and nper is multiplied by 12. There are 120 monthly payments in a 10 year period.
This image shows the principal for month 25 to 36 and the cumulative principal. The amount in cell G22 is the same as in the top image cell E17.
4. Example 2
A small business has taken a loan of $75,000 at an annual interest rate of 7%, over a period of 6 years. How much is left of the loan after three years?
Here's a description of each argument:
- Rate: This argument represents the interest rate per period. In the example, the rate is set to 7%.
- Nper: This argument is the total number of payment periods. In the example, it is set to 10, which could represent 6 years, 6 months, or 6 of any other period, depending on the context. This value 6 is multiplied with 12, 6 years * 12 months = 72 periods
- Pv: This argument is the present value or the loan amount. In the example, it is set to $100,000.00.
- Start_period: This argument specifies the starting period for which the cumulative principal should be calculated. In the example, it is set to 1, indicating that the calculation will start from the 1st period.
- End_period: This argument specifies the ending period for which the cumulative principal should be calculated. In the example, it is set to 36, indicating that the calculation will end at the 36th period. 3 years * 12 months = 36 periods
- [Type]: This argument is optional and represents the timing of the payments. In the example, it is set to 0, which means that the payments are due at the end of each period.
Formula in cell E17:
Cell E17 returns 55,215.70 which is what is left of the loan after three years or 36 months of principal payments.
The loan was 100,000 and the cumulative principal after three years is -44,784.30 This returns 55,215.70 if we calculate 100,000 - 44,784.30 equals 55,215.70
5. CUMPRINC not working
The CUMPRINC function returns a #NUM error value if any of the following conditions are met:
- The rate argument is less than or equal to zero.
- The nper argument (total number of payment periods) is less than or equal to zero.
- The pv argument (present value or loan amount) is less than or equal to zero.
- The start_period argument (starting period for calculating cumulative principal) is less than 1.
- The end_period argument (ending period for calculating cumulative principal) is less than 1.
- The start_period argument is greater than the end_period argument.
- The type argument (timing of payments) is not specified as either 0 (payment due at the end of the period) or 1 (payment due at the beginning of the period).
Functions in 'Financial' category
The CUMPRINC function function is one of 27 functions in the 'Financial' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form