Calculate CAGR (Compounded Annual Growth Rate) using Excel [Formulas]
Here is a story we all are familiar with,
Jack learns about compound interest in school. He quickly forgets about it while learning other complicated things like trignometry, partial differentiation, correct spelling of trigonometry etc. Jack graduates and ends up with a job he loves / hates. Years pass and one day out of boredom jack looks thru mail and stumbles on a credit card bill. As Jack has nothing else to do, he opens it. He looks at something and wonders why his credit card number has only 7 digits!
Then he realizes that it is the amount he owes to credit card company!!!
Jack scratches his head vigorously and wonders how the tiny bits of money he spends on gas, toothpaste and occasional pizza can add up to $19,234.55 ?
Then he realizes the true power and meaning of compound interest!
The moral of the story: Never open a credit card bill.
Of course the real moral is, understand the power of compound interest and use it to your advantage.
But we are not here to talk about unhealthy money habits.
We are here to talk about awesome benefits of Excel.
So moving on, lets talk about how we can use Excel to calculate Compounded Annual Growth Rate (CAGR for short).
What is CAGR? What does it signify?
Let us say you are the CEO of ACME Inc. You have been selling various widgets since 2009. In your latest annual report you want to tell your shareholder at what rate you have been growing ACME Inc. sales. The figure are,
- 2009 – $150 Mn
- 2010 – $125 Mn
- 2011 – $160 Mn
- 2012 – $174 Mn
- 2013 – $195 Mn
- 2014 – $210 Mn
Now, if you see the growth rates, they are all over the place. Right from -16.67% to 28%. But you want to report a single annual growth rate.
This is where CAGR (Compounded Annual Growth Rate) comes handy.
Imagine that the sales of ACME Inc. grew at constant rate every year from $150 Mn in 2009 to reach the $210 Mn in 2014. This is what CAGR represents.
As you can see, CAGR removes all the volatility in the numbers to tell at what uniform rate the numbers grew (or declined) every year.
Arithmetic behind CAGR
While we can never explain the arithmetic behind how credit card companies calculate interest, penalties, fees, interest on penalties and miscellaneous charges, we can easily understand the logic of CAGR.
You see CAGR uses the all important compound interest math at the heart.
Lets throw a few variables in to this article now:
Lets say the starting value is P (in our case P= $150 Mn in 2009)
End value is A (A = $210 Mn in 2014)
and N represents the number of years it took P to become A. In our case N is 5 years.
R denotes the rate of growth (CAGR).
The basic equation is
A = P *(1+R/100)^N
And we need to find R.
So lets do some simple Arithmetic. Lets expand,
not that kind of expansion, you silly!
We need to do this:
The final equation for R is =(A/P)^(1/N) – 1
Calculating CAGR in Excel
Now that we have finished a crash course in arithmetic behind compound interest, we can calculate CAGR in Excel. There are 3 ways to do this.
- Using raw arithmetic as shown in above equation.
- Using RATE formula
- Using IRR formula
Using arithmetic equation for calculating CAGR
This is simple. Assuming,
- P is the initial value
- A is the last value
- N is the number of years
in a blank cell, write
=(A/P)^(1/N) – 1
And you get the Compounded Annual Growth Rate as output. (Just need to format the cell as %).
Using RATE() formula
RATE() is a financial formula (function) in Excel that can tell us what would be the interest rate for an annuity. Sounds complicated? See this example:
Lets say, as the CEO of ACME Inc. you took a loan of $30Mn to expand your company. You agreed to pay $5 Mn per year for next 10 years to pay off the loan. So what is the effective rate of interest?
=RATE(10, 5, -30) tells us that the rate of interest is 10.558%
We can use RATE() formula with below parameters to calculate CAGR,
In our situation, we want to know at what uniform rate the sales grew from $150 Mn in 2009 to $210 Mn in 2014.
This is same as taking a loan of $150 Mn in 2009 and paying off $0 per year for 5 years and paying one lump-sum payment of $210 Mn in 2014.
So we use =RATE(N,,-P,A) to indicate that we are paying $0 per year (hence omission of 2nd parameter) and paying one lump-sum amount at the end.
Using IRR() formula
While the above 2 formulas do not require any changes in the original data, this one requires that we re-shape the data.
See this picture:
So original data in B3:B8 is re-shaped in D3:D8,
Now, use =IRR(D3:D8) to get the rate of growth (CAGR).
Other scenarios where CAGR is helpful
- You purchased some shares at $100.00 per share on 1st of February 2007. You sold them off at $270.13 per share on 3rd of April 2014. What is your annual rate of return?
- Your website traffic was 15,000 page views per month in Jan 2012. In March 2014 it is 75,000 per month. What is the monthly growth rate?
- You had 10 paying clients when you started business 5 years ago. Today you have 300. What is the annual growth rate?
So what method do you use to calculate CAGR?
I have always relied on the brute arithmetic calculation of (A/P)^(1/N) -1. I guess derive strange pleasure writing equations in Excel cells.
What about you? Do you compute CAGR? Where do you use it? And what method do you use to calculate it? Please share your tips & thoughts in comments.
Feeling love towards money & modeling it in Excel?
While it may take us forever to earn few millions or pay off that student loan, we can model all of that in Excel in a few hours! Here are a bunch of articles to help you get started with just that.
- Financial modeling using Excel (6 part tutorial)
- Mortgage calculator using formulas & form controls
- When can you retire, find out using goal seek
- Growing a money mustache using Excel
Introducing our Online Power BI Class:
Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.Click here to know more and join us.
Leave a Reply
|How to become an MVP in Excel [case study]||CP007: aweSUM() – Overview of SUM functions in Excel|