Have you ever thought it’d be nice to have an Excel formula to calculate the CAGR on your investments that move more unpredictably rather than in a perfectly straight line? Contributor Andy Shuler has just created a new 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, Andy’s written a great intro into 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!).
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, it’s 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 in
11 pounds. The next year you would bench
248 pounds (1.05*236), or an increase in 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,
because you’re building on top of those gains (both literally (GET SWOLL!) and
numerically).
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, and I can confirm that I am not one of
those 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, and 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.
Applying CAGR to Money in the Stock
Market
Now, to put this into money
terms. The same exact philosophy can be
applied towards 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 that those benefits will be when you get to retirement
age. Want to have your mind blown? Ok, FINE!
I’ll do it – you twisted my arm…
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, which would’ve cost you a total of $72,000 ($100*12 months*60 years).
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).
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 months ago I wrote an article that showed you how to calculate compound interest based on a static number, for instance, if you inputted $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:
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’s always more
options, but for this sake, let’s assume that you decide to do a combination of
spending less and investing less to help you pay for the AC, and you will have
to go three months without investing for retirement.
Well, my Excel CAGR Formula toolcan calculate that, and online calculators cannot. Let’s take a look!
Andy’s CAGR Formula (Excel) Explained
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 an 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!
Click to Zoom
Step 2 is to 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.
Click to Zoom
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 and now instead of your portfolio sitting
at $2342, which would be your expected amount based off the returns that I
inputted (8%) and the monthly investments that I added, your portfolio is
sitting at $3300! 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 $3300 in this hypothetical example,
update the December 2019 cell to $3300 and then it will continue calculating
the remaining years based off that same value.
Click to Zoom
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 year 1, your returns
are pathetic! Let’s pretend you end the
year at $2195, 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 $9231 after three years:
Click to Zoom
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, your first you need to input your
actual Year 1 return in the December 2019 cell, which was $2195.
Next, go to the ‘Contribution
Details’ tab and change your Contribution Amount for the months that you think
you can. If you changed your contribution amount each month for Years 2 & 3
from $250 to $257, you’re actually ahead of the $9231 goal that you initially
had, see below:
Click to Zoom
I really urge you to play around with this tool, use it however best benefits you, and let me know directly if you have any feedback that I can consider to make it better and build out other tools with.
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.
The post A Flexible CAGR Formula Excel Tool for Planning Your Chaotic Life appeared first on Investing for Beginners 101.
The Transparent Traders Blackbox 💸 Amazingly simple!
What’s in the box?
Our Blackbox is the first-ever to be created that specifically alerts for swing trades. It will also alert for bullish & bearish day trades.
The Blackbox runs off of multiple algorithms and uses predictive A.I. to locate the most accurate day or swing trades that it calculates to give the best chance for success.
Transparent Traders
Private group · 6,900 members
Join Group
Transparent Traders exists to solve the critical issues facing our traders, both large and small. Our unique approach is not only what differentiates ...
Contact Us: We Can Help the Trading Community Learn About Your Company or Product.
Post a Comment