How to Calculate Compound Interest in Excel

You have all learned the formula for calculating compound interest in your school. Compound and simple interest have been among the mathematical applications used in real life for years. At certain times in our lives, we need to calculate simple and compound interest. For example, when we borrow money from financial companies or our friend at a certain rate of interest, we must know the calculations of compound and simple interest to avoid being cheated.

In addition to calculating compound interest on paper, if you know how to calculate it by Excel, it will be an additional asset for your professionalism. In the above formula, P represents the principal value, R is the interest rate, and n is the total time.

Here we will learn how to calculate compound interest using Excel. But before we start, let’s look at the terms used in compound interest calculations.

  • Compound annually or annually: Here, the interest rate is applied to the principal value every year.
  • Compound semi-annually or semi-annually: Here, the main value is increased every 6 months, that is, twice a year. To calculate compound interest semi-annually, multiply n by 2 and divide the rate by 2.
  • Quarterly Compound: Each year has four terms. Here, the main value is increased every 3 months, which means 4 times a year. To calculate quarterly compound interest, we need to multiply n by 4 and divide the interest rate by 4.
  • Compound monthly: There are 12 months in a year. Therefore, compounded monthly means that interest is applied monthly. Therefore, we need to multiply the n by 12 and divide the interest rate by 12.

How to Calculate Compound Interest (CI) in Excel

We will discuss here:

  1. When the interest rate is compounded annually.
  2. When the interest rate is compounded semi-annually.
  3. When the interest rate is compounded quarterly.
  4. When the interest rate is compounded monthly.

Let’s see the calculation of compound interest in Excel.

1]Compound interest calculation annually in Excel

Calculate compound interest annually in Excel

Consider an example of data with the following values:

  • P=1000
  • R = 10%
  • n = 5 years

Enter the above data into Excel and write the following formula:

=B1*(1+B2)^B3

B1, B2 and B3 are the cell addresses which indicate the principal value, interest rate and time respectively. Please enter the cell address correctly, otherwise you will get an error.

2]Compound interest calculation semi-annually in Excel

Compound interest semi-annually in Excel

Here we need to add some extra value to our data, by composing the periods per year. As explained above, two semesters make a full year. Therefore, there are 2 semi-annual capitalization periods.

  • Main = 1000
  • Interest rate = 10%
  • Time = 5 years
  • Capitalization periods per year = 2

Enter the above data into Excel and write the following formula:

=B1*(1+(B2/B4))^(B3*B4)

You see, we divided the interest rate (value in cell B2) by 2 (value in cell B4) and multiplied the time (value in cell B3) by 2 (value in cell B4).

3]Compound interest calculation quarterly in Excel

Calculate quarterly compound interest in Excel

Here, the formula remains the same, the one we used in the calculation of the semi-annual CI. Now you just need to change the values ​​in the respective cells. For the quarterly CI calculation, change the value in cell B4 to 4.

4]Compound Interest Calculation Monthly in Excel

Monthly Compound Interest in Excel

To calculate monthly compound interest, change the value in cell B4 to 12 and use the same formula.

That’s it. Let us know if you have any questions regarding CI calculation in Excel.

Read more: How to calculate simple interest in Excel.

Excel logo

About the author