
Excel
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


Bob
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%.

Brian
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.

sswilcox
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.

Bob
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).

jraju
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.


jraju
Hi,
Chandoo, could i expect your views on my point above about using arithmetic average for gsdp 
jraju
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

Luis
I use =RRI(nper,pv,fv)
It is very simple 
Rupa
Nice explanation.

tony
Very beautiful, but can further explain how to calculate a compound monthly growth rate (CMGR).
Thank you 
jraju
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.
Bob
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…



Umesh
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.

Bob
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.

Mike
Bob – I was very happy to find your comments here. I’ve been warning folks for years about the pitfalls of the CAGR in terms of expressing historical trends, and it’s nice to happen upon the same arguments by an independent source.
I’ve just started a blog about analyzing industries and markets, and I cover the topic here: http://theindustryanalyst.info/2014/12/30/thecagrhandlewithcare/commentpage1/#comment2
Perhaps you’d be so kind as to review my post. (I hope to elaborate on LOGEST in a later post.)
Many thanks.

Mike
Bob – this is a better link: http://theindustryanalyst.info/2014/12/30/thecagrhandlewithcare/

Bob
Mike – Nice clear article on CAGR versus AAGR. In many instances calculating a point to point CAGR can be very misleading.




Elizabeth Crum
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!

Bob
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.


Sreedhar
Thanks. Here is a questions.
Would this brute arithmetic calculation of (A/P)^(1/N) 1 and RATE() formula work even when the value of N is LESS than ONE year – few months or days? In other words, would it work in the example below.I purchased some shares at $100.00 per share on 7JAN2015. I sold them off at $120.00 per share on 3rd of 4FEB2015. What is the Annualized return I received?
Thanks in advance.

Prashant
Very Nice explanation ..do liked it.
I was confused on one of the explanation given in other website but chandu is very nice to explain 
You can easily calculate CAGR value using this web calculator.
http://www.stockcalculation.com/2015/06/compoundgrowthratecalculator.html