Compound interest

How to calculate compound interest in Excel

You have all learned the formula for calculating compound interest at your school. Compound and simple interest have been among the math applications that have been used in real life for years. There are times in our life that we need to calculate simple and compound interest. For example, when we borrow money either from financial companies or our friend at a certain interest rate, we should know the compound and simple interest calculations 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 is the primary 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 begin, let’s take a look at the terms used in calculating compound interest.

  • Composed annually or annually: Here, the interest rate is applied to the principal value each year.
  • Composed semi-annually or semi-annually: Here, the main value is increased every 6 months, or twice a year. To calculate interest compounded semi-annually, multiply n by 2 and divide the rate by 2.
  • Quarterly compound: Each year has four quarters. Here the main value is increased every 3 months, which means 4 times a year. To calculate the compound interest quarterly, we need to multiply n by 4 and divide the interest rate by 4.
  • Composed monthly: There are 12 months in a year. Therefore, compounded monthly means that interest is applied every month. 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]Calculation of interest compounded annually in Excel

Calculate interest compounded 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 respectively indicate the principal value, the interest rate and the time. Please enter the cell address correctly, otherwise you will get an error.

2]Calculation of semi-annual compound interest in Excel

Interest compounded semi-annually in Excel

Here we need to add extra value to our data, composing periods per year. As explained above, two half years make a full year. There are therefore 2 composition periods per semester.

  • Principal = 1000
  • Interest rate = 10%
  • Time = 5 years
  • Compound periods per year = 2

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

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

See, we have 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]Calculation of quarterly compound interest in Excel

Calculate quarterly compound interest in Excel

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

4]Calculating monthly compound interest in Excel

Monthly compound interest in Excel

To calculate compound interest monthly, 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 calculating CIs in Excel.

Read more: How to calculate simple interest in Excel.

Excel-Logo


Source link