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,
=RATE(N,,P,A)
Explanation:
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 lumpsum 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 lumpsum 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 reshape the data.
See this picture:
So original data in B3:B8 is reshaped in D3:D8,
Now, use =IRR(D3:D8) to get the rate of growth (CAGR).
Related: Using IRR() function over a dynamic range with OFFSET.
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
 
 

Leave a Reply
How to become an MVP in Excel [case study]  CP007: aweSUM() – Overview of SUM functions in Excel 
22 Responses to “Calculate CAGR (Compounded Annual Growth Rate) using Excel [Formulas]”
I use the power formula to calculate the CAGR.
power(A/P,1/5)1
Very well written Chandoo. Great explanation.
Thank you Mike. Your compliment means a lot
Back in the dark ages I was taught that CAGR (or AAGR – Average Annual Growth Rate) was the exponential growth rate of the line that best fit all the data. i.e., that you had to consider the data from all of the data points. The 6.96% CAGR you calculate ignores the performace in intervening years, notably the large sales drop in 2010. Therefore I would use LOGEST(B3:B8)*1 and say that a more representative estimate of the average compound growth of ACME’s sales over the period from 2010 to 2014 was 9.26% – more attractive than 6.96%. If ACME’s sales were: 150,220,230,240,180,210 your calculations would still yield 6.96% while LOGEST would calculate the average growth to be 3.26%.
Using IRR to calculate the rate of growth of sales is not strictly speaking a correct application of the IRR concept IMHO. The IRR function is intended to measure the return on an amount invested. In your example the final sales figure would represent the amount realised at the end of the investment term rather than the level of sales ( or any other variable) at a particular time. But interesting application nevertheless.
Also an interesting post about LOGEST function Bob. How does it handle negative values ? As you know the IRR doesn’t handle negatives very well, sometimes producing two or more solutions to the same problem.
I always use the full mathematical formula and have never considered the notion of using an Excel formula as an alternative. Of course, I then always verify the CAGR by plugging it back in and making sure I end up at the correct number. So much for shortcuts.
LOGEST doesn’t handle negatives well at all. If ACME had negative sales that would probably be a signal to avoid them. However, if looking at profits and some periods were negative we would have the formula return “NMF” – No Meaningful Figure – instead of the #NUM! error and then use cumulative profits/cumulative sales to compare competitor profitability (or couple Sales Growth with absolute improvement in % Return on Sales over the period being analyzed.) Three key measures: Grow sales faster than your world (i.e. gain market share); grow your income faster than your sales (i.e. improve ROS/become more efficient); and grow cash faster than your income (effectively manage your assets).
Hi,
Cagr could be used only for those parameters, where you are having a kind of closing stock. Opening stock etc. But in calculating the growth rate of Gross State Domestic Product and such a kind of parameters, then this formula could not be used. Only mere arithmetic average could be used. Because, the Value added to the particular years are only taken in to account for GSDP. Any comments, chandoo, Am I correct
Hi Jraju.. I think CAGR works even in such cases too.
When someone says Country X’s GDP grew by 8% on average in the last 5 years, I would imagine the GDP grew from 100 to 136 in 5 years, not 132.
When you explain 100 to 136, 8% would be the CAGR.
Hi,
Chandoo, could i expect your views on my point above about using arithmetic average for gsdp
Hi, chandoo,
Please review the use of cagr in gsdp . The gross value added from each sector for the year only has been taken in to the calculation of GSDP. There is no opening stock or closing stock to brought forward in such case. So, only arithmetic mean, that is the growth rate of each year divided by the no of years is the possible way, as far as i know. Expecting your clarification
I use =RRI(nper,pv,fv)
It is very simple
Nice explanation.
Very beautiful, but can further explain how to calculate a compound monthly growth rate (CMGR).
Thank you
Hi, Chandoo
Expecting your clarification on using cagr in gsdp
Hi Chandoo,
How to calculate if I hv yearly growth rates and not absolute figures.
If you have no absolute values you can set the first year to 1 and construct an an annual index [prior period index * (1+ Annual Growth Rate)] for each year. The Compound Growth Rate of this calculated index, calculated using the Logest function as I suggested in above comments, will be the same as if you had all the actual values. I assume the other calculation methods would work as well. As noted in above discussion, if an actual value (not the % change) is negative, calculating a meaningful CGR is problematical.
Many thanks Bob its really working…
Sometimes you might feel that CAGR is not an effective way of calculating the growth. For ex. in calculating the CAGR %, what is important is the Value during the base year & Value during the final year of calculation, all other values are nonsignificant. So irrespective of what growth rates are achieved (even 0 or negative) during the intermediary years, CAGR is calculated based on the final year results & first year results only.
I beg to differ with Umesh. I beleive that all the points in a trend are relevant. Would you rather have a business with periodic revenues of 10, 15, 20, 25, 30 or a stock with periodic revenues 10, 5, 20, 10, 5, 30? Some may like the roller coaster but others get sick. Ignoring intermediate points is quick and easy but ignores both cummulative revenues and very important volatility.
Hi there – I am using the RATE funtion to calculate CAGR, using your example above. The “starting” point (2009) is annual sales for a product. The number is not negative, how do I keep the number positive in the spreadsheet, yet use the RATE function? I tried putting in a negative sign in the formula and it didn’t work…. I had to change the first sales figure to a negative number. Thank you!
The negative sign has to be in front of the pv argument. So if your starting point value is in cell A2 then the formula should be RATE(number of periods , , A2 ,ending value. If your values are in A2:A5, e.g., 10,15,20,25. Then the formula would be:
=RATE(3,,A2,A5) which would return 35.72%.
I continue to think that using RATE is not the best way to calcualte CAGR – see my comments above.