How to calculate CAGR (compound annual growth rate) in Excel

Compound interest is one of the fundamental structure segments in banking and one of the most powerful financial capabilities that define the result of your investments.

It might be a bit tricky to understand the concept of specialized financial books and manuals, except you are an accounting graduate or advanced investor. The aim of this lesson is to make it easy.

So, as a first, let’s see how to use a compound interest formula in Excel and find a general compound interest abacus for your own purpose.

Long-term investments can be a powerful strategy to grow your money. Even small deposits can cause great privilege over time. The Excel compound interest formulas will assist you to force the savings tactics to work. We are going to create a general formula that measures the future condition of the investment at any of the compounding interest rates – daily, monthly, annual, doesn’t matter which time frame you will apply.

In order to realize the concept of compound interest properly, let’s start with a very simple case and draft a formula to calculate annual compound interest in Excel. To make things more clear we will show in other examples. Say, you want to invest $10. And the annual interest rate is 7%. So, you would like to know how compounding can increase your savings per year.

An easy and straightforward way to calculate the amount earned with 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.


As you remember, 1% is one part of a hundred, i.e. 0.01, so 7% is 0.07, and this is how percentages are actually stored in Excel. Keeping this in mind, you can verify the result returned by the formula by performing a simple calculation of 10*(1+0.07) or 10*1.07.

And now, let’s calculate the balance after 2 years. 

So, how much will your $10 deposit be worth in two years’ time at an annual interest rate of 7%? The answer is $11.45 and you can get it by copying the same formula to column D.


To calculate how much money you will find in your bank account at the end of 3 years, simply copy the same formula to column E and you will get $12.25.

If you have some experience with Excel formulas have probably figured out that what the above formula actually does 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 will get the same number as you see in cell E2 in the screenshot above – $12.25. Naturally, you can directly calculate the balance after 3 years using this formula:

=A2*1.07*1.07*1.07

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.

Say 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, you must have in mind the following details: 

Fix the reference to the Annual Interest Rate cell (B2 in our case) by adding the $ sign, it should be an absolute column and absolute row, like $B$2.

For Year 2 (B6) and all subsequent years, change the formula to:

Year 1 balance + Year 1 balance * Interest Rate

In this example, you’d enter the formula =B5 + B5 * $B$2 in cell B6 and then copy it down to other rows like demonstrated in the screenshot below.


To find out how much interest you actually earned with annual compound interest, enter the formula =B5-B1 (Balance after 1 year – Initial deposit) in cell C5.

Then enter =B6-B5 (Balance after 2 years – Balance after 1 year) in cell C6 and drag the formula down to other cells.

You should see the earned interest growth like in the screenshot below that reveals the real power of compound interest.

The above examples do a good job illustrating the idea of compound interest. But none of the formulas is good enough to be called a universal compound interest formula for Excel. 

Firstly, because they do not let you specify a compounding period. And secondly, because you have to build an entire table rather than simply enter a certain duration and interest rate.

So, let’s create a universal compound interest formula for Excel that can calculate how much money you will earn with yearly, quarterly, monthly, weekly or daily compounding.

General compound interest formula

The financial advisors usually consider three factors that determine the future value of the investment (FV):

PV – present value of the investment

i – interest rate earned in each period

n – number of periods

You can use the following formula to find out the future value of the investment with a certain compounded interest rate:

FV = PV * (1 + i)n

If you prefer investing money rather than time in figuring out how to calculate compound interest in Excel, online compound interest calculators may come in handy. You can find plenty of them by entering something like “compound interest calculator” in your preferred search engine.

Compound interest can help supercharge your savings. It is effectively interest on interest, and it can give your bank balance a nice boost over time.

What is important in investing? 

If you decide you want to buy individual stocks, we recommend you take a slow and steady approach. Don’t put more than 10%of your portfolio in individual stocks until you get very comfortable with what you’re doing.

When a lot of people think of investing or trading, they imagine painstakingly picking individual stocks, tracking their daily performance and constantly buying and selling. This may be good and interesting for TV shows or movies.

But in real life it is agony.

All you need to do is pick a couple of funds that attempt to mimic the total market behavior, and—for the most part—leave them alone for 5 or 10 years. It’s very simple, and it’s something everyone can and should do. In fact, it’s one of the best ways to effortlessly build wealth in the long term.

But it is time to learn what is a portfolio and how to build it.

Quiz yourself on How to calculate CAGR (compound annual growth rate) in Excel in the following quiz:

 

When unsure what's the right move, you can always trade Forex

Get the number #1 winning technical analysis ebook for trading Forex to your email.
Containing the full system rules and unique cash-making strategies. You'll be surprised to see what indicators are being used and what is the master tuning for successful trades. Including case-studies and images.
Traders-Paradise