How to Use Cumulative Interest Formula in Excel (3 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In finance, banking, or in business, it’s a quite common task to calculate cumulative or compound interest. We can calculate it in many ways. But Excel offers the easiest and time-saving ways to do it. So, today in this tutorial, we’ll learn 3 easy methods to use the cumulative interest formula in Excel with easy examples and vivid illustrations.

In this section, we will demonstrate three easy and effective ways to apply the cumulative interest formula in Excel.


1. Using a Basic Mathematical Formula for Cumulative Interest

First, we’ll apply the basic mathematical formula in Excel to calculate compound interest. The function of the formula is simple, it will first calculate the final value over the periods and then will subtract the principal value from it to get the total cumulative interest.

The basic mathematical formula is:

  • Cumulative/Compound Interest = P*(1+r/n)^t*n – P

Where,

  • P is the principal.
  • r is the annual interest rate.
  • t is the time.
  • n is the number of compounding.

1.1 Yearly Compounding

Firstly, we’ll learn to apply the manual formula for yearly compounding, which means the compounded interest will be applied annually.

Let, a person take a loan of $5000 for 3 years at a 10% annual rate.

Using Basic Mathematical Formula for Cumulative Interest of Yearly Compounding

Steps:

  • In Cell C9, insert the following formula:
=C5*(1+C6)^C8-C5
  • Then just hit the Enter button for the output.

As the compounding period per year is 1 here, so we skipped it in the formula.


1.2 Monthly Compounding

For monthly compounding, the compounding period per year is 12. So, we’ll have to divide the annual rate by 12 and will have to multiply the number of years by 12 to get the total monthly periods. We’ll use the previous dataset to calculate the monthly cumulative interest.

Using Basic Mathematical Formula for Cumulative Interest of Monthly Compounding

Steps:

  • Write the following formula in Cell C9–
=C5*(1+C6/C7)^(C8*C7)-C5
  • Next, press the Enter button to finish.


1.3 Weekly Compounding

To calculate weekly compounding interest, the compounding period per year is 52. So, we’ll have to divide the annual rate by 52, and to get the total weekly periods we’ll have to multiply the number of years by 52. Here also we’ll use the previous dataset to calculate the weekly cumulative interest.

Using Basic Mathematical Formula for Cumulative Interest of Weekly Compounding

Steps:

  • Type the following formula in Cell C9–
=C5*(1+C6/C7)^(C8*C7)-C5
  • Later, hit the Enter button to get the compounded interest.


1.4 Daily Compounding

As one year is equal to 365 days, for daily compounding, we’ll divide the annual rate by 365, and to get the total daily periods, we’ll multiply the number of years by 365.

Using Basic Mathematical Formula for Cumulative Interest of Daily Compounding

Steps:

  • In Cell C9, insert the following formula:
=C5*(1+C6/C7)^(C8*C7)-C5
  • Finally, just press the Enter button for the output.

Using Basic Mathematical Formula for Cumulative Interest

Read More: How to Calculate Daily Interest in Excel (2 Easy Ways)


2. Using the FV Function to Calculate Cumulative Interest in Excel

Instead of using a manual formula, Excel has some functions that we can apply in a formula to calculate cumulative interest. In this method, we’ll apply the FV function to calculate the future value over a certain period and then will subtract the principal value to get the compound interest. We’ll apply the same dataset from the first method to get the cumulative interest for one year.

Steps:

  • Apply the following formula in Cell C9–
=FV(C6,C8,0,-C5)-C5
  • Then hit the Enter button to return the output.

Have a look, we got the same output as like the first section of the first method.

Using FV Function to Calculate Cumulative Interest in Excel

Read More: Simple Interest Formula in Excel (With 3 Practical Examples)


Similar Readings


3. Using the CUMIPMT Function to Calculate Cumulative Interest Between Two Dates

The previous methods only return the cumulative interest for the whole period, they can’t calculate the interest for any range of specific periods within the whole period. But using the CUMIPMT function of Excel, we can calculate cumulative interest between two dates means the interest for any range of periods. Assuming, you have a loan of 60 months of the period then you can calculate the compound interest for the period from 5 to 30.

For this method, we modified the dataset. Let, a person has taken a loan of $5000 for 3 years/ 36 months at a 10% annual rate on 9/1/2020. Now we’ll find the interest between two dates- 9/1/2020 and 12/1/2021, the difference between the dates is 15 months of period. For the interest of these 15 months, we’ll use 1 for the start period and 15 for the end period in the formula.

Using CUMIPMT Function to Calculate Cumulative Interest Between Two Dates

Steps:

  • In Cell C11, insert the following formula-
=CUMIPMT(C6/12,C7*12,C5,1,15,0)
  • Finally, press the Enter button to get the cumulative interest for the period.

This function returns the interest in a negative value, to avoid it, you can use a negative sign before the function.

Read More: How to Calculate Interest Between Two Dates Excel (2 Easy Ways)


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


Conclusion

That’s all for the article. I hope the procedures described above will be good enough to use the cumulative interest formula in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo