How to calculate compound interest in Excel: daily, monthly, yearly compounding
Download Practice File
The tutorial explains how to use the compound interest formula in Excel and gives examples of how to calculate the future value of an investment with yearly, monthly, or daily interest. It also shows you step-by-step how to make your own Excel compound interest calculator.
Compound interest is a key concept in banking and a powerful financial tool that affects your investments.
If you are not an accounting graduate, financial analyst, or experienced investor, understanding this concept from specialized financial books can be hard. This article aims to make it simple 🙂 You will learn how to use the compound interest formula in Excel and create a compound interest calculator for your worksheets
Table of Contents
What is Compound Interest ?
In simple terms, compound interest is interest earned on interest. More precisely, it is earned on both the initial deposit (principal) and the interest that has been added from previous periods.
It might be easier to start with simple interest, which is calculated only on the principal amount. For example, if you put $10 into a bank account, how much will it be worth after one year with an annual interest rate of 7%? The answer is $10.70 (10 + 10*0.07 = 10.70), and your earned interest is $0.70.
With compound interest, the principal amount changes each time period. Instead of giving the earned interest back to you, the bank adds it to your principal investment. This new amount becomes the principal for the next time period and also earns interest. In other words, you earn interest not only on the original amount but also on the interest earned in each period.
In our example, besides the principal amount of $10, the earned interest of $0.70 will also earn interest next year. So, how much will your $10 deposit be worth after 2 years with an annual interest rate of 7% compounded yearly? The answer is $11.45 (10.70 + 10.70*0.07 = 11.45), and your earned interest is $1.45. As you can see, at the end of the second year, you not only earned $0.70 on the initial $10 deposit but also earned $0.05 on the $0.70 interest from the first year.
There are several ways to calculate compound interest in Excel, and we are going to discuss each one in detail.
How to Calculate compound interest in Excel?
Long-term investments can be a great way to grow your wealth, and even small deposits can add up over time. The Excel compound interest formulas explained in this tutorial will help you create a savings strategy that works. We’ll also show you how to make a universal formula that calculates the future value of your investment with different compounding periods—daily, weekly, monthly, quarterly, or yearly.
Calculate Annual compound interest in Excel
To understand compound interest better, let’s start with a simple example mentioned earlier and create a formula to calculate annual compound interest in Excel. You are investing $10 at an annual interest rate of 7% and want to see how yearly compounding increases your savings.
Annual Compound interest rate – Formula 1
An easy and straightforward way to calculate the amount earned with an annual compound interest is using the formula to increase a number by percentage:
=Amount * (1 + %).
In our example, the formula is
=A2*(1+$B2)
Where A2 is your initial deposit and B2 is the annual interest rate. Please pay attention that we fix the reference to column B by using the $ sign.
Remember, 1% is one part of a hundred, or 0.01. So, 7% is 0.07, and this is how percentages are stored in Excel. You can check the result from the formula by doing a simple calculation: 10*(1+0.07) or 10*1.07. This confirms that your balance after 1 year will indeed be $10.70.
Now, let’s calculate the balance after 2 years. How much will your $10 deposit be worth after two years at an annual interest rate of 7%? The answer is $11.45. You can get this result by copying the same formula to column D in Excel
If you have some experience with Excel formulas, you might have noticed that the formula is multiplying the initial deposit of $10 by 1.07 three times:
=10*1.07*1.07*1.07=12.25043
Round it to two decimal places, and you’ll get the same number you see in cell E2 in the screenshot above: $12.25. You can directly calculate the balance after 3 years using this formula:
=A2*1.07*1.07*1.07
Annual compound interest - formula 2
Another way to make an annual compound interest formula is to calculate the earned interest for each year and then add it to the initial deposit.
Assuming that your Initial deposit is in cell B1 and Annual interest rate in cell B2, the following formula works a treat:
=B1 + B1 * $B$2
For the formula to work correctly, pay attention to these details:
- Fix the reference to the Annual Interest Rate cell (B2 in our case) by adding the $ sign, making it an absolute reference like $B$2.
- For Year 2 (B7) and all subsequent years, change the formula to:
Year 1 balance + Year 1 balance * Interest Rate
In this example, enter the following formula in cell B6 and then copy it down to other rows, as shown in the screenshot below:
=B6 + B6 * $B$2
To find out how much interest you earned with annual compounding, subtract the Initial deposit (B1) from the Balance after 1 year (B5). Use this formula in cell C5:
=B6-B1
In cell C6, subtract the Balance after 1 year from the Balance after 2 years, and then drag the formula down to other cells:
=B7-B6
You should see the earned interest grow as shown in the screenshot below.
The examples above do a good job showing how compound interest works, don’t they? But none of the formulas can be called a universal compound interest formula for Excel. This is because they don’t let you choose how often the interest is compounded, and you have to make a whole table instead of just entering a time period and interest rate.
Let’s make a universal compound interest formula for Excel that can calculate how much money you’ll earn with yearly, quarterly, monthly, weekly, or daily compounding.
General Compound interest formula
When financial advisors look at how compound interest affects an investment, they usually think about three factors that determine the future value (FV) of the investment
- PV – present value of the investment
- i – interest rate earned in each period
- n – number of periods
By knowing these components, you can use the following formula to get the future value of the investment with a certain compounded interest rate
FV = PV * (1 + i)n
To explain this better, here are a couple of quick examples
Example 1: Monthly compound interest formula
Suppose you invest $5,000 at 10% interest rate compounded monthly and you want to know the value of your investment after 5 years.
First off, let’s write down a list of components for your compound interest formula
- PV = $5,000
- i = 10% per year, compounded monthly (0.10/12= 008333333)
- n = 5 years x 12 months (5*12=60)
Input the above numbers in the formula, and you will get:
= $5,000 * (1 + 0.10/12)5×12
or
= $5,000 * 1.0083333360
Or
=5000*1.645308 = 8226.54
Â
Example 2: Daily compound interest formula
I hope the monthly compound interest example is clear. Now, you can use the same method for daily compounding. The initial investment, interest rate, duration, and formula are the same as in the previous example, only the compounding period is different:”
- PV = $5,000
- i = 10% per year, compounded daily (0.1/365 = 0.000274)
- n = 5 years x 365 days (5*365 =1825)
Supply the above numbers into the compound interest formula, and you will get the following result:
=$5,000 * (1 + 0.000274)/1825 = $8243.04
Sure, here is the simple English version:
As you can see, with daily compounding interest, the future value of the same investment is a bit higher than with monthly compounding. This is because the 10% interest rate adds interest to the principal amount each day instead of each month. As you might guess, monthly compounding will result in more than annual compounding.
All this is good, but you really want an Excel formula for compound interest, right? Just hang on a little longer, please. Now, we are getting to the most interesting part – creating your own powerful and flexible compound interest calculator in Excel.
Compound interest formula in Excel (daily, weekly, monthly, yearly compounding)
Usually, there is more than one way to do something in Excel, and a compound interest formula is no exception 🙂 Although Microsoft Excel doesn’t have a specific function for calculating compound interest, you can use other functions to create your own compound interest calculator.
Let’s start building our Excel compound interest calculator by entering the basic factors that determine the future value of an investment into an Excel worksheet
- initial investment (B3)
- annual interest rate (B4)
- number of compounding periods per year (B5)
- number of years (B6)
When done, your Excel sheet may look like this:
Compound interest formula for Excel:
Initial investment * (1 + Annual interest rate / Compounding periods per year) ^ (Years * Compounding periods per year)
For the above source data, the formula takes this shape:
=B3 * (1 + B4 /B5) ^ (B6 * B5)
If you want to know how much your investment will be worth at an 10% annual interest rate compounded quarterly, simply enter 4 in cell B5:
To calculate the future value of your investment with semi-annual compounding, enter 2 as the number of compounding periods per year. For weekly interest rates, enter 52, since there are 52 weeks in a year. If you want daily compounding, enter 365, and so on.
To find the amount of earned interest, simply compute the different between the future value (balance) and the present value (initial investment). In our case, the formula in B9 is as simple as
=B8-B3
As you see, we’ve created a truly universal compound interest calculator for Excel. Hopefully, now you have no regrets that you invested a few precious minutes in figuring out the tricky compound interest formula used by financial planners : )
Advanced compound interest calculator for Excel
If you don’t like the method above, you can create your Excel compound interest calculator using the FV function. This function is available in all versions of Excel from 2000 to 2019.
The FV function calculates the future value of an investment based on input data like what we’ve discussed, but its syntax is a bit different.
FV(rate, nper, pmt, [pv], [type])
let’s build a FV formula using the same source data as in monthly compound interest example and see whether we get the same result.
As you may remember, we deposited $5,000 for 5 years into a savings account at 10% annual interest rate compounded monthly, with no additional payments. So, our compound interest formula goes as follows:
=FV(0.1/12, 5*12, ,-5000)
If you need some explanation of the parameters, here you go:
- rate is 0.01/12 since you have the 10% annual interest rate compounded monthly.
- nper is 5*12, i.e. 5 years * 12 months
- pmt is left blank because we have no additional payments.
- pv is -5000 since it’s an outflow and should be represented by a negative number
Enter the above formula in an empty cell, and it will output $8,226.54 as the result (which is perfectly in line with the result of the math calculation performed in the monthly compound interest example).
Naturally, nothing prevents you from replacing the values with cell references:
=FV(B4/B5, B6*B5, , -B3)
The screenshot below shows the future value of $6,000 investment after 15 years at an annual interest rate of 6% compounded weekly
To make your Excel compound interest calculator even more powerful, you can add an option for additional contributions (extra payments) and adjust the compound interest formula to include them.
=FV(B4/B5, B6*B5, -B8, -B3, B9)
Where:
- B3 – principal investment
- B4 – annual interest rate
- B5 – the number of compounding periods per year
- B6 – the number of years to save
- B8 – additional contributions (optional)
- B9 – additional contributions type. Remember that you enter 1 if you deposit an additional amount at the beginning of the compounding period, 0 or omitted if additional payments are made at the end of the period.
