CUMPRINC Function
It is a financial function that calculates the cumulative principal amount paid for a loan.
What is the CUMPRINC Function?
The CUMPRINC is a financial function that calculates the cumulative principal amount paid for a loan.
Oh boy, here comes a financial function that might be ‘a bit’ difficult to interpret, but don’t worry; we will ensure that it’s easy to understand.
First, let’s understand what we mean by cumulative principal amount.
The cumulative principal amount refers to the total amount of loan principal that has been repaid over a specific period, including any additional principal payments over the same period.
We know that the loan comprises two components - the interest and the principle.
When we pay off a loan, the bank usually secures the interest component first, followed by the principal amount. Thus, in a loan, the principal amount is far lower than the interest expense.
As the loan approaches the end of tenure, the interest gets smaller, and a sizable chunk of the payments is composed of the principal amount.
That’s just the basic understanding of the loan composition. Perhaps it will be far easier to understand once we see a couple of examples of how the function works.
Key Takeaway
- The CUMPRINC is a financial function that calculates the cumulative principal amount or the investment over a given time.
- The function accepts six different arguments: the loan rate, its present value, the total number of time periods, the starting period, the ending period, and finally, whether the amount was paid at the beginning or the end of the year.
- You can directly reference the cells in the formula instead of hardcoding all these values individually.
- The general rule with loans is that the payments are composed of larger interest components. Later on, the principal amount makes a significant chunk of the payments, and interest becomes insignificant.
How the CUMPRINC Function works
The CUMPRINC is categorized as a Financial function that returns the cumulative principal or the investment amount between two specified periods.
For example, suppose you must determine how much the principal amount was paid between the 13th and 24th payments.
The function will accept several arguments, such as the rate on the loan, present value of the loan, total time period, start and end time period, etc., which can be directly referenced in the formula.
You know what? Reading about the function's syntax will make things much easier for you. The syntax for the function is:
=CUMPRINC(rate, nper, pv, start_period, end_period, type)
where,
- rate - (required) the interest rate on the loan
- nper - (required) the total number of periods for which the loan will be repaid
- pv - (required) represents the present value of the loan or the investment
- start_period - (required) beginning of time period. It will usually be between 1 to specified nper
- end_period - (required) end of the time period. It will usually be between 1 to specified nper.
- type - (required) determines whether the payments are made at the beginning or end of the year. For example, if the value is 0, the payments are made at the start of the period, whereas if the value is 1, it is made at the end.
Let’s not get too technical and head over to the example so that it becomes a lot easier how the function works.
Example of the CUMPRINC Function
Six different arguments mean the function will be tricky to use.
But don’t worry; we will break it down for you so that it’s easier to understand and use the function.
Suppose you have the data as illustrated below:
Wow, now that’s a lot of data we need to review to understand how the function works.
- Loan amount - the loan amount is the present value of the investment, which will be referenced in the function.
- Rate - the rate of borrowing on the loan
- Period per year - the number of EMI payments made in a calendar year
- Period per rate - the loan rate as per the period per year. If the EMI is paid on a quarterly basis, then the period per year becomes 4. This ultimately changes the period per rate to 0.0125, i.e., (5%/4).
- Year - The total number of years for which the loan will be active
- Total period - The product of the total number of years and the period per year, which gives the whole period over the entire tenure of the loan or investment
- Start_period - the starting period of the loan over which we want to find the cumulative principal. Here, we are calculating the cumulative principal for 2nd year of the loan; hence we have the start_period as 13.
- End_period - the ending period of the loan over which we want to find the cumulative principal of the loan.
Now we know the different terminologies in the example, let’s calculate the cumulative principal amount for the given example.
We will use the formula =CUMPRINC(C5,C7,C2,C8,C9,0) in cell C11, which gives the result as:
Thus, the cumulative principal amount repaid over the second year of the loan tenure will be - $5124.71.
What about the first year of the loan tenure?
You just need to make two changes in the data, i.e., the start_period and the end_period. Without changing the formula, the updated data, along with the cumulative principal amount, will be:
Thus, for the first year of the loan tenure, the cumulative principal amount from 1st month to the 12th month will equal -4875.2856.
As you might recall, the principal only forms a significant chunk of repayment in the later years of the loan tenure, while the interest component is extracted at the beginning.
Practical Examples of the CUMPRINC Function
In this example, we will see two different examples to calculate the cumulative principal or the investment amount over a specific time period.
a. Cumulative principal over a time period
Suppose your client takes a loan of $20,000 for a period of five years. The interest rate on the loan is 6%, and the payments will be made every month. Therefore, you need to calculate the cumulative principal for each year until the end of the loan’s tenure.
The data looks as illustrated below:
From the yearly rate of 6%, we have calculated the monthly rate by dividing it by the total period per year, which gives the period per rate as 0.005.
To calculate the cumulative principal amount, we will use the formula =CUMPRINC(C6,C8,C3,C9,C10,0) in cell C12, giving the result as -$3536.0633.
By dragging the formula towards the right till cell G12, we get the result:
Thus, we get the cumulative principal of -$3754.16 in year 2, -$3985.7084 in year 3, and so on till the end of the tenure. If you add all these numbers, the total would equal $20000, which is the initial loan amount.
Well, you did find the principal amount for each of these years, but there’s the interest amount. What about it, or rather how do you find it?
In this case, another function, CUMIPMT, calculates the cumulative interest rate over a selected period.
Think of this just as an additional section. We are just doing this exercise to make it easier to understand why to use Excel's CUMPRINC or CUMIPT functions.
Firstly, we know the PMT function calculates the monthly payments that must be made after the loan is availed. The number consists of the principal and the interest components.
To calculate the monthly payments from the given number, we will use the formula =PMT(C6,C8,C3,,0) in cell C14 and drag it to cell G14, which gives the result:
However, we need to find the yearly number, so you just need to multiply the monthly payments by 12, which gives us the following:
Now we have the cumulative principal for each year and the yearly payments made for the availed loans. If we subtract the two numbers, we will get the cumulative interest rate for each year which is as below:
Remember what we said earlier? The interest amounts are more significant in the beginning and go on decreasing at the end of the loan’s tenure.
However, you do not always need to calculate the cumulative interest this way. For this, we have the CUMIPMT function, which can calculate the cumulative interest using the formula =CUMIPMT(C6,C8,C3,C9,C10,0), which gives the result:
If you add the cumulative principal and the interest for each year, it will equal the payments made each year.
Hope this is now making sense to you as to why we have the CUMPRINC function and the CUMIPMT function, which we will cover in an entirely different article!
b. Cumulative investment over a time period
This example will guide you on calculating the investment within a specific time period.
Suppose you purchase a T-bond from TreasuryDirect and want to calculate the amount of investment you would have over a defined period.
Let’s say you have purchased bonds worth $5000, paying an annual interest of 3%, paying a semi-annual interest.
The data looks as illustrated below:
We are considering how much of the principal investment would be collected in the first ten years.
To calculate the investment over the period, we will use the formula =CUMPRINC(C6,C8,C3,C9,C10,0) in cell C12 and drag it to cell L12, which gives the result:
Oh yeah, and for your information, the yearly interest payments on the investment amount would equal $126.97. So this means that in the first year, the principal amount you earned was $104.71, while in the second year, it was $107.87.
This continues until the entire investment amount is collected in the 30th year, along with the interest component, which can be separately calculated using the CUMIPMT function.
Still, this is how the entire calculations would look like for the initial ten years:
You can go ahead and even calculate it for the entire tenure of the T-bond, which we have assumed to be for 30 years.
Conclusion
Excel's CUMPRINC function is an essential tool for financial analysis. It allows users to evaluate the total principle paid back on loans or the principal accumulation in investments over predetermined periods of time.
Its syntax offers a thorough foundation for carrying out accurate computations and contains inputs for interest rate, total periods, present value, and start and finish periods.
Through helpful examples and explanations, users may appreciate the function's usefulness in a variety of financial settings, from comprehending loan repayment schedules to assessing investment returns.
Because of its adaptability and simplicity of use, this function is a priceless tool for financial experts. It helps them efficiently optimise their financial strategy and make well-informed judgements.
Through the use of the CUMPRINC function, individuals and organizations can acquire a more profound understanding of their financial commitments and investment holdings.
This can ultimately improve their financial management strategies and help them more accurately and confidently accomplish their long-term objectives.
Free Resources
To continue learning and advancing your career, check out these additional helpful WSO resources:
or Want to Sign up with your social account?