CUMIPMT Function

What is the CUMIPMT Function In Excel?

The CUMIPMT function in Excel stands for "Cumulative Interest Paid." It's a financial function used to calculate the cumulative interest paid between two periods in a loan or investment. This function is useful for analyzing loans or investments where you want to know the total interest paid over a specific period.

Cumulative interest paid is the total amount of interest you have paid for your car or home loan. Other than these, there are many things you can buy and pay later with interest in today's world.

Loans are the most common to be issued to people. This is because when we make a very high amount of purchases, the average person will take out a loan to buy it.

Mortgages, vehicles, and student loans are just a few examples of the various kinds of loans. Apart from these, there are also regular loans that people sometimes take to pay for debts or other unspecified things.

When we take a loan, we not only pay the amount originally borrowed. But we also have to pay the interest on the borrowed amount.

Therefore, a loan installment payment is divided into two parts. The principle will be one part, and the other will be the interest.

Different banks and offices often charge different interest rates. For example, a bank may charge you higher interest than another bank. Banks may also charge you a different interest rate depending on the borrowing period.

If you are interested in taking a loan or have one and want to know the cumulative interest you will or are paying, the function can be of great use to you. It will help you calculate the total interest you might or are paying.

Calculating the amount of interest you must pay on loan can assist us in making a sound financial choice. When working with the function on Excel, it will not calculate taxes or fees you normally incur when obtaining a loan.

Example of Cumulative Interest

You decided to take a loan for fifteen thousand dollars to pay for a car you wanted. The company that gave you the loan charges you a two percent interest rate.

We have:

• Borrowed Amount = %15,000
• Interest Rate = 2%
• Loan Term = 2 Years

So now we multiply the borrowed amount by the interest rate, which will give us the total interest we will pay the first year.

$15,000 (amount borrowed) x 2% (interest rate) = $300 (interest paid for the first year)

We now know we paid three hundred dollars for the interest, and now let’s say we also paid five thousand dollars on the amount borrowed. So it leaves us with ten thousand dollars left to get paid.

$15,000 - $5,000 = $10,000

The balance on the second year of borrowing the money is now ten thousand. Interest will be charged again for that remaining balance because you get charged as long as you have the balance.

$10,000 x 2% = $200

As a result, you paid two hundred dollars in interest in the second year. The cumulative interest paid for the two years would be five hundred dollars. Of course, you can continue until the end, when you finish paying, but this is a quick and easy example to get an idea of how it works.

$300 (interest paid year 1) + $200 (interest paid year 2) = $500

How to use the CUMIPMT Function in Excel?

Now that we know some basics of cumulative interest. We can now work on doing it through Excel. Using the function on Excel will help you find the cumulative interest paid easier than by doing it by hand.

It will help you analyze your work accurately and efficiently. This leads to helping people to create a financial analysis of their data.

In our previous example of explaining how the cumulative interest paid worked, it may have looked easy. In Excel, there will be a lot more numbers to enter. They are numbers you can get easily.

The reason why the previous example seemed to be easy is that it was just a straightforward example. However, do not get overwhelmed by Excel and its formulas. As long you follow the procedures and understand what you are entering, it will start to appear easy.

It is always good to enter the required entries when working with Excel and not skip them. However, missing a required entry can lead to errors in formulas in Excel. Therefore, always ensure you know what is needed with the formula.

Knowing these things can ensure your usage of Excel is simpler. Also, knowing what is causing the error can help solve any issues or errors.

CUMIPMT Function Formula

When you start working on Excel, the formula will be

=CUMIPMT(rate,nper,pv,start_period,end_period,type) 

It will look like this in Excel.

You can type the formula as we did above or find it in your “Formulas” tab.

Once you are in the Formulas tab, you will click on the financial book and scroll down to CUMIPMT.

We will need the six arguments in the formula rate, nper, pv, start period, end period, and the type to calculate the interest.

You can not skip one by leaving it blank. If one of the arguments is left blank, it will result in an error, and the system will not solve for the cumulative interest paid. A more detailed explanation is as follows:

• Rate - This will require you to enter the interest rate. 
• Nper - This argument requires you to end the number of payments in total. 
• Pv - Stands for present value, and it is a required entry. The current value would be the loan or investment amount. 
• Start _period - This is the period you want to start on. For example, which month do you want to begin with? This entry is also required. 
• End_ period - This is the last period you want your calculations to end. 
• Type- This is when the payments are due. The number one entry would be for the beginning, and the zero would be for the end. 

CUMIPMT Function Example

Let’s say you have a six percent interest rate loan. The loan is a ten-year loan, and you want to borrow one hundred thousand dollars.

• Loan amount- $100,000
• Loan term- 10 years 
• Interest Rate- 6%

Let's set up your Excel sheet.

After entering your information, your sheet should look like the following.

The number of payments per year would be twelve as there are twelve months in a year, and we have made monthly payments.

Once you start entering the formula, instead of typing the numbers out or cell names, you can select the cell you want to enter the formula, as shown below. However, if you prefer to type the formula instead of selecting the cells you want, you can do that, too.

Ensure when clicking on cells, you are selecting the correct ones and in order. After selecting a cell, type after a comma “,” to move on with the formula.

Your next step is dividing the rate by the twelve months. Then you will enter your nper required entry. Finally, we will enter the ten years but multiply it by the number of payments per year.

Your formula should look like this so far.

Proceed to enter the PV entry, which stands for present value.

Followed by the start_period and end_period. If you want to calculate the cumulative interest paid, you will enter the start period you want to start at, meaning the month you want to start on and the ending month you want to end at.

Take advantage of the start and ending period to your needs. You can change it to year two or three. Whichever best suits you.

In this case, let's calculate the entire term of the loan. Therefore, we will enter 1 for the start period and the total number of payments, which we can get by multiplying the number of years of the loan by the number of payments per year.

Finally, you enter the type. Are you paying at the beginning of the period or the end?

• Enter 0 for the end of the period.
• Enter 1 for the beginning of the period.

Our cumulative interest paid would be as shown for the entire loan length. Knowing how much interest you will pay over the entire loan term can be beneficial. If you are not satisfied with the result, you can always find another company that may be willing to give you a lower rate.

Situation 1

Let's say we find a company that will give us the same loan at the same time of year but at a different rate.

As we can see, once we change the rate to a smaller one, our interest paid changes. We will now pay less interest than with the other loan. The function allows you to see your options and pick the ones that fit you economically.

Situation 2

Let’s grab the same loan, but this time calculated for the first year only. All the previous steps will remain the same except for the starting and ending periods. For the starting period, we will enter one for the first month and twelve for the ending period.

We will leave the ending period as one, or if you want, you can change it to the option that best suits you and solve for CUMIPMT.

The amount shown is the interest to be paid for year one. Let’s say we wanted to calculate the interest paid for year two of our loan. Again, we leave the first steps the same and change the starting and ending periods.

As we see, you would pay less interest on the second than on the first year. But, again, this is because the amount you owe from the loan is less because of what you paid off in the first year.

You can also figure out the interest paid on the year if you only make annual payments. In that case, you would change the number of payments per year. There are various scenarios because sometimes different companies can offer other things.

It is best to enter the numbers that you have and the data you want to know about. 

Conclusion

Gaining proficiency with Excel's CUMIPMT function is an important ability that enables people to perform thorough financial analysis quickly and accurately. Users may easily compute cumulative interest payments by utilizing Excel's features, which help with strategic planning and informed decision-making.

Although using Excel's formulae can be difficult at first, following best practices and comprehending the fundamentals will help you become more proficient over time. It is essential to rigorously enter all necessary data points in order to prevent formula errors and guarantee correct results.

Users can utilize Excel as a potent tool for financial analysis, gaining insights, and guiding well-informed decisions with practice and comprehension. People can maximize their Excel experience and confidently take on tasks by adopting these strategies and paying close attention to detail.

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• AVERAGE Function
• BASE Function
• CRITBINOM Function
• DATE Function
• INDEX Function