How to Calculate Compound Interest in Excel?

How to calculate Compound Interest in Excel

Compound interest is considered one of the wonders of the mathematical world. It is the interest that you earn on the interest of the previous period. Interest-ception, maybe? Well, for people interested in personal finance, you know the value compound interest adds to your financial lives. Today, we’ll build a compound interest calculator in Excel so you can keep track of your investments and their future returns right on your Excel file.

While many have heard of compound interest, you may not know the exact formula to calculate the compound interest. So let’s get started with that first.

What is Compound Interest?

Compound Interest is the method of addition of interest to a loan or deposit after a set time period to the principal amount or previous ended time period’s ending amount.

The Formula to Calculate Final Compounded Amount is:

Formula to calculate Final Compound Amount
Formula to calculate Final Compound Amount


A = Final Amount
P = Principal Amount (Initial)
r = Interest rate
n = number of times interest is applied per time period or number of times the amount is compounding
t = number of time periods or years

Here’s the formula to get the compound interest:

C.I. = A(Final Compounded Amount) — P (Principal Amount)

If you don’t quite get it yet, that’s alright. Let’s explore this further with an example.

Example for Compound Interest

Assume Jason invested $10,000 in a Government Railway Bond for 5 years. The compounding frequency for this bond is 6 months, which means the interest amount is accumulated every 6 months. And the rate of interest for this bond is 10%.

So we have:

P = $10,000, r = 10% per year, t = 5 years and n = 2 times (as 2 half-years in a year)

So his statement in 5 years will look like this:

YearJanuary – JuneJuly – December 
1st Year (2021)$10,500.00$11,025.00
2nd Year (2022)
3rd Year (2023)
4th Year (2024)

5th Year (2025)
Compound Interest Example

From the above statement, it is evident that in the duration of 5 years, Jason’s investment appreciated to $ with a Compound Interest of ($16,288.95 – $10,000 ) = $ 6,288.95

Note: I have used r = 10% for simpler calculations.

Calculate Compound Interest in Excel

As you now have a better idea of what compound interest is, how it is calculated, and what kind of information you need as inputs, let’s plot this out on our Excel sheet.

Step 1: Navigate to “Function”

  • Select a cell where you want to display your result
  • Navigate to ‘Insert‘ and then click on ‘Function
Using Function to calculate Compound Interest
Using Function to calculate Compound Interest

Step 2: Create a “Compound Amount Function”

  • Choose create a function on the popup window
  • If creating a function isn’t available as a direct option then click on any other function [here we have clicked on ‘Sum’ function]
General Function
  • Then clear everything after the Equal sign (=) [ Notice the function bar]
Create a blank function
  • Start substituting values in the formula of Compounded Amount (A) given here.

Formula to calculate Amount (A) = P* (1+(r/n))^(n*t)

creating amount function
creating amount function

So here you get the final answer of Amount (A) = $16, 288.95

Step 3: Create a Compound Interest Function

To get the result of Compound Interest (C.I.) – Follow the above steps,

  • Create a function of Compound Interest
  • Substitute values in it to get the result

Formula to Calculate Compound Interest (C.I.) = A (Amount) – P (Principal)

creating compound interest function
creating compound interest function

Hence, we find the Compound Interest for the given data is = $ 6,289


That’s it! We have our own compound interest calculator. You can add the absolute returns that you’ve earned over the same period by calculating the percentage right below the compound Interest amount.

We hope you learned and enjoyed this lesson and we’ll be back soon with another awesome Excel tutorial!