arrow28 Comments
  1. Excel
    Apr 29 - 12:05 pm

    I use the power formula to calculate the CAGR.
    power(A/P,1/5)-1

  2. Mike Alexander
    Apr 29 - 2:22 pm

    Very well written Chandoo. Great explanation.

    • Chandoo
      Apr 30 - 4:23 am

      Thank you Mike. Your compliment means a lot :)

  3. Bob
    Apr 29 - 6:08 pm

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

  4. Brian
    Apr 29 - 7:20 pm

    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.

  5. sswilcox
    Apr 29 - 7:51 pm

    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.

  6. Bob
    Apr 29 - 8:23 pm

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

  7. jraju
    Apr 30 - 6:19 am

    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

    • Chandoo
      May 03 - 4:11 am

      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.

  8. jraju
    May 03 - 2:04 am

    Hi,
    Chandoo, could i expect your views on my point above about using arithmetic average for gsdp

  9. jraju
    May 03 - 7:34 am

    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

  10. Luis
    May 08 - 7:10 pm

    I use =RRI(nper,pv,fv)
    It is very simple

  11. Rupa
    May 22 - 2:52 pm

    Nice explanation.

  12. tony
    Jul 15 - 6:19 pm

    Very beautiful, but can further explain how to calculate a compound monthly growth rate (CMGR).
    Thank you

  13. jraju
    Jul 16 - 4:00 am

    Hi, Chandoo
    Expecting your clarification on using cagr in gsdp

  14. Joy
    Sep 24 - 7:37 am

    Hi Chandoo,
    How to calculate if I hv yearly growth rates and not absolute figures.

    • Bob
      Sep 24 - 2:25 pm

      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.

      • Joy
        Sep 29 - 8:20 am

        Many thanks Bob its really working…

  15. Umesh
    Oct 08 - 12:06 pm

    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 non-significant. 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.

  16. Bob
    Oct 08 - 1:54 pm

    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.

  17. Elizabeth Crum
    Nov 11 - 11:04 pm

    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
      Nov 12 - 2:36 pm

      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.

  18. Sreedhar
    Feb 11 - 12:22 pm

    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 7-JAN-2015. I sold them off at $120.00 per share on 3rd of 4-FEB-2015. What is the Annualized return I received?

    Thanks in advance.

  19. Prashant
    Feb 14 - 6:37 am

    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

  20. Rama
    Jun 11 - 1:27 pm

    You can easily calculate CAGR value using this web calculator.

    http://www.stockcalculation.com/2015/06/compound-growth-rate-calculator.html

Leave a Reply

Mobile Theme