Have you ever thought it’d be nice to have an Excel formula to calculate the CAGR (Compound Annual Growth Rate) on your investments? After all, they move unpredictably rather than in a perfectly straight line. This post has a CAGR formula Excel spreadsheet to help investors plan their future—even when things don’t go exactly according to plan.

If you’re an absolute beginner, this post is a great intro to everything about CAGR and why you’d want to calculate it as an investor.

If you’re just looking for the Excel CAGR formula, then go ahead and scroll down to where the big colorful pictures are (and be sure to download the tool for free for yourself!).

Click to jump to a section:

• Introduction to Compounding and CAGR
• Applying CAGR to Money in the Stock Market
• Saving for Retirement
• CAGR Spreadsheet Explained (Downloadable)
• Spreadsheet Examples

Introduction to Compounding and CAGR

CAGR. What the heck is CAGR? Well, it’s an acronym for Compound Annual Growth Rate, or in other words, the rate that something compounds on an annual basis. Almost always, this will be in reference to money, but that’s not the only time this applies. Let’s get down to some Compounding 101.

You might’ve heard people say, “I compounded the problem by trying to make things right.” What they are actually saying is that they took a bad situation and made that situation even worse. Fortunately for all of us, not all examples of compounding are about making situations worse. 

For instance, imagine if you were a bodybuilder and you were told that from ages 18-30, you would gain 5% in your bench press each year. If you benched 225 pounds as an 18-year-old, your next year you’re going to bench 236 pounds (1.05*225), or an increase of 11 pounds. The next year you would bench 248 pounds (1.05*236), or an increase of 12 pounds. In essence, you will gain more strength each year because you’re gaining 5% strength on a larger number.

As you can see by looking at the chart above, you will have gained 19 pounds in strength when you’re 30 (385*1.05) compared to the 11-pound gain your first year. This, in essence, is the beauty of compound interest. 

The longer that you let something have the ability to compound, the larger your number, in the end, will be. You’re building gains on top of gains. 

Please note that this is an example and not me saying you’re going to go from benching 225 to 400+ in 12 years. Although I’m sure some people do, I can confirm that I am not one of these people. 

Something that’s very important to note, both in this example and in any other, is that our bodies don’t continue to get in better shape. Eventually, we will likely experience health issues, so the value of compounding mainly comes from time. 

The sooner you start, the better because you will have that much more time to allow your task to compound. Compounding is all about the long game.

Applying CAGR to Money in the Stock Market

Now, to put this into money terms – the same exact philosophy can be applied to investing.

The earlier that you start investing, the earlier that you will start seeing the benefits of investing in the stock market, and the larger those benefits will be when you get to retirement age. Want to have your mind blown? Ok, FINE! I’ll do it.

If you invested $100/month for your child starting the month they were born, and you received an 8% CAGR (the S&P 500 average since 1950 is 11%), then you would have $1,778,852.75 when they turned 60. This process would’ve cost you a total of $72,000 ($100*12 months*60 years). That works out to $1,706,852.75 just from compounding.

For your child to have about that same amount of money when they turned 60, assuming they started investing when they were 22 and also received an 8% CAGR, they would have to invest over $602/month for 38 years. Doing this would’ve cost them a total of $274,512 ($602*12 months*38 years)! How insane is that? 

That, my friends, is the value of compound interest. 

Time matters more than the actual amount that you put in. By starting when your child was just born, you can have the same amount for them when they’re 60 by putting in $200,000 less in total (You put in $72,000 in total while they put in $274,512 in total). 

Saving for Retirement

Now, I’ll admit, this might seem like a somewhat outlandish type of comparison because you are likely looking for how to get your own retirement going, and I 100% respect that. 

I am in that same boat. 

You need to focus on yourself first because if all you do is save for your child, then they’re going to be paying for your retirement (so it basically is you saving for it in a roundabout way lol). But I challenge you to really, really think about this…

If your goal is to live a happy life, and you want your children to live a great life, then can you find $100/month that you’re blowing on something you don’t need? The cost is so small to you to set them up for success in their life. 

Maybe it’s not $100. 

Maybe it’s $25. That’s literally one meal for yourself and your spouse. Can you eat at home one extra night/month? 

Saving $25/month for that same 60 years will give them $444,713.19. Not nearly as high as $100/month, but still enough to make a significant difference in their quality of life. Ok, end rant.

So, you likely understand the value of compound interest now, and that while the amount that you put in is important, time is the most important factor, so it’s better to start as early as possible. 

A couple of months ago, I wrote an article that showed you how to calculate compound interest based on a static number. For instance, if you input $10,000 dollars and you let it earn X interest for X years, how much would you have? 

Such as, if you invested $10,000 into the stock market and realized a 6% CAGR (reminder that the S&P Average since 1950 is 11%), you would have the following:

CAGR Spreadsheet Explained (Downloadable)

I’ve found some great compound interest calculators online that are very helpful, but they’re basic and somewhat inflexible. I wanted to create something that you could use that really fits the situation that you’re in. 

For instance, maybe you intend to invest $250/month each and every month. That’s great! But what if your AC dies on you (like ours just did), and you now have the choice – die of heat exhaustion and go without AC or slow down your investing a bit? 

I know there are always more options, but for this example, let’s assume that you decide to do a combination of spending less and investing less to help you pay for the AC. To achieve this, let’s assume you will have to go three months without investing for retirement. 

Well, this Excel CAGR Formula tool can calculate that, and online calculators cannot. Let’s take a look!

Step 1: On the tab labeled ‘Contribution Details,’ you can input how much money you plan to invest and how much you have actually invested. You’ll notice that the first 7 months are green, but that’s just because I like to highlight finalized numbers, so I know what’s planned and what is actual. All of these cells are hardcoded, so you can simply go in and change your anticipated savings amount for any month by just overwriting the cell. It’s a very easy update to make!

Step 2: Take a look at the tab titled ‘CAGR Calculator.’ All you need to do is change the cell that’s highlighted green to update what assumption you want to make for your portfolio growth. Once you do that, the rest auto-populates based on your input on the previous tab.

Spreadsheet Examples

My favorite thing about this tab is that it’s able to be personalized. You can manually update your investment amounts each month because, guess what, life happens. You’re not always going to invest the exact same amount each month from now until death. Not only that but maybe you want to update your portfolio with actual data! 

For instance, let’s imagine that you get huge returns your first year. Now instead of your portfolio sitting at $2,342, which would be your expected amount based on the returns that I input (8%) and the monthly investments that I added, your portfolio is sitting at $3,300! Talk about a monster year. 

To update this, all you need to do is simply override the cell. So, since we’re saying you ended the year at $3,300 in this hypothetical example, update the December 2019 cell to $3,300 and then it will continue calculating the remaining years based off that same value.

Now, that’s all lovey-dovey and a good problem to have… “Oh, I made too much money. How am I ever going to update my spreadsheet?” Wah wah, boo hoo. 

The best part of this CAGR Calculator Excel version is that instead of you seeing 8% returns in year 1, your returns are pathetic! Let’s pretend you end the year at $2,195, or a -5% return! Dang, that was brutal. BUT! This tool can show you what you need to do to get back on track. 

For instance, in our first example of 8% returns, your total portfolio would be $9,231 after three years:

But we just discussed that your Year 1 return was very low, so you need to make that up somehow. So, how do you do that? Well, first, you need to input your actual Year 1 return in the December 2019 cell, which was $2,195. 

Next, go to the ‘Contribution Details’ tab and change your Contribution Amount for the months that you think you can. If you change your contribution amount each month for Years 2 & 3 from $250 to $257, you’re actually ahead of the $9,231 goal that you initially had. See below:

I really urge you to play around with this tool, and use it however best benefits you. Please comment below if you have any feedback to make it better or suggestions for other tools. 

I think this has a great ability to be flexible when you’re looking at retirement because, let’s be honest, getting to retirement requires more flexibility than a yoga class.