How to Calculate Compound Interest in Microsoft Excel

by
Spread the love

Microsoft Excel is not limited to using predefined formulas. You can also create or define your own formulas in Excel to calculate the desired result. This tutorial shows how to calculate Compound Interest in Microsoft Excel.

Content

Compound Interest Based on Frequency

We calculate compound interest based on frequency. This frequency is the compound frequency, which refers to the number of times an interest is calculated and added to the principal amount within the specific time. Usually, we have to deal with the following compound frequencies:

  • Annual: the rate of interest is applied to the principal annually.
  • Semi-Annual or Half-Yearly: the rate of interest is applied to the principal two times a year.
  • Quarterly: the rate of interest is applied to the principal four times a year.
  • Monthly: the rate of interest is applied to the principal every month.

Using Microsoft Excel to calculate compound interest when the rate of interest is compounded annually, you would use the following formula:

CI = P(1+(R/100))^t - P

In the above formula, CI represents compound interest, P represents the initial principal amount, R represents the rate of interest, and t represents time. If you want to calculate the compound interest for the compound frequencies other than annually, change the rate of interest and time accordingly, as shown below.

Calculate Compound Interest in Excel for an Annual Compound Frequency

To create an Excel formula for an annual compound frequency, add the compound interest formula to the targeted cell.

To calculate compound interest for an annual cycle, use the following Excel formula:

=B1*(1+B2/100)^(B3)-B1

In the above formula for an annual interest rate, I used B1 for the principal amount, B2 for the rate of interest, and B3 for the time. These are the cell addresses. Be sure to use the correct cell addresses in your Excel spreadsheet to ensure there is no error.

Compound Interest for a Semi-Annual Compound Frequency

A half-yearly compound cycle repeats itself two times a year. Therefore, to calculate compound interest for a semi-annual compound cycle, make the following changes in the Excel compound interest formula:

  • Divide the rate of interest by 200
  • Multiply the time by 2

The formula will become:

=B1*(1+B2/200)^(B3*2)-B1

Use the correct cell addresses in your Excel spreadsheet.

Compound Interest for a Quarterly Compound Frequency

A quarterly compound cycle repeats four times in a year. Therefore, for a quarterly compound cycle, make the following changes to the formula:

  • Divide the rate of interest by 400
  • Multiply the time by 4

The formula to calculate compound interest in this case, use the following formula:

=B1*(1+B2/400)^(B3*4)-B1

Compound Interest for a Monthly Compound Frequency

A monthly compound frequency repeats 12 times in a year. Therefore, make the following changes to the formula:

  • Divide the rate of interest by 1200
  • Multiply the time by 12

The formula to calculate the compound interest will become:

=B1*(1+B2/1200)^(B3*12)-B1

Creating a Compound Interest Calculator in Excel

You can also create a Compound Interest Calculator in Microsoft Excel by changing the values in your data. Follow the steps below for the different compound cycles.

First, create a data in Excel with the following values in different rows:

  • Principal
  • Rate of interest
  • Time
  • Amount
  • Compound interest

Create separate columns for every compound cycle. I have created four columns in Excel for four compound cycles.

Write the formula for every compound cycle by dividing the rate of interest and multiplying the time by the required number, as described above. For example, in a half-yearly cycle, the formula to calculate the rate of interest and time will be B2/200 and B3*2, respectively.

The formula to calculate the amount is:

A=P(1+R/100)^t

Make changes to the rate of interest and time by dividing and multiplying these values by the required numbers for the different compound cycles. Enter the correct cell addresses to ensure you do not get an error in Excel.

The formula to calculate the compound interest when the amount is given is:

CI=A-P

In the above formula, CI represents the compound interest, A represents the amount, and P represents the principal. Add this formula to the required cells in Excel by entering the correct cell addresses. For example, in my data, the formula to calculate compound interest for a half-yearly compound cycle is:

=E4-B1

Once, you add all the formulas to Excel with the correct cell addresses in the required places, your Compound Interest Calculator will be created. Change the values of principal, rate of interest, and time to calculate the amount and compound interest for different cycles.

There are many real-life applications where there is a need to calculate compound interest. Some of these real-life applications of include interest rates for banking and finance, loans, and credit cards. Instead of using pen and paper to calculate the compound interest, just create a Compound Interest Calculator in Microsoft Excel to make things easier.

Image credit: Unsplash. All screenshots by Deeksha Gola.

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe


Deeksha Gola

Deeksha is a tech enthusiast and loves writing articles related to technology. Previously she has worked with Electronics Arts as a Tech Support Assistant and helped many gamers troubleshooting and fixing game errors. Playing Guitar is her hobby.

Leave a comment