CUMIPMT Function
Calculates the cumulative interest paid on a loan taken or an investment made
Calculates the cumulative interest paid on a loan taken or an investment made
The CUMIPMT Function is a Financial function. CUMIPMT helps in calculating the cumulative interest paid on a loan taken or an investment made.
=CUMIPMT(rate, nper, pv, start_period, end_period, type)
The CUMIPMT function uses the following arguments:
To understand the uses of the CUMIPMT function, let us consider a few examples:
Let’s assume we took a $100,000 loan that needs to be paid off over 5 years. The interest charged by the bank is 10% and we are required to make monthly payments. First, we need to arrange the data in such a manner that it becomes easier to apply the formula and avoid errors.
We will do this for all years. As seen in the screenshot above, the start and end periods change for each year. To avoid errors, we can use a formula. I used the following formula for the start_period:
For the end_period, I used the following formula:
As seen above, the interest rate charged is on a per annum basis. We need to convert them into a monthly rate (10%/12). It is the period rate in our table above.
Also, there are 60 total payments, as we would be making monthly payments.
Let’s see how this formula can be used in the given scenario:
We will get the result below:
The calculated interest payments are negative values, as it represents outgoing payments for the person who took the loan.
Often, this formula will give very or very low results. It generally happens when we forget to convert the annual interest rates or the number of periods to months or quarters as required. To avoid errors, we need to make sure that we are consistent about units for specifying rate and nper.
With the same data as above, let’s see how the results would change if we were required to make quarterly payments. Now, as we need to make quarterly payments, the number of periods per year will change to 4.
The table will look like this:
The formula to use will be:
We will get the following result:
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!