Page 1 of 2 12 LastLast
Results 1 to 10 of 17
Like Tree1Likes

CAGR

This is a discussion on CAGR within the Excel Questions forums, part of the Question Forums category; Hi. Is there any simple formula in excel for calculating Compounded Annual Growth Rate (CAGR)?...

  1. #1
    New Member
    Join Date
    Feb 2004
    Posts
    23

    Default CAGR

    Hi. Is there any simple formula in excel for calculating Compounded Annual Growth Rate (CAGR)?

  2. #2
    MrExcel MVP fairwinds's Avatar
    Join Date
    May 2003
    Posts
    8,633

    Default Re: CAGR

    Hi,

    Try this UDF

    Code:
    Function CAGR(First, Last, Periods)
        CAGR = ((Last / First) ^ (1 / Periods)) - 1
    End Function
    Put the code in a module and use

    =CAGR(A1,A2,A3)

    in the sheet as a normal formula
    "Fair Winds and Following Seas"

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,001

    Default Re: CAGR

    You can use the RATE function, eg:

    =RATE(A3,,-A1,A2)

    where A1 contains the starting amount, A2 the ending amount and A3 the number of years.
    GeneralB likes this.

  4. #4
    New Member
    Join Date
    Jun 2009
    Posts
    1

    Default Re: CAGR

    Hello sir,

    Im not able to get the CAGR rate by using rate function.

  5. #5
    New Member
    Join Date
    Mar 2010
    Posts
    1

    Default Re: CAGR

    Hey thanks guys,,, I was facing the same issue to calculate CAGR....
    The rate formula works .

  6. #6
    New Member
    Join Date
    Jan 2008
    Posts
    25

    Default Re: CAGR

    Hi - is there a formula that properly calculates CAGR when you end up with a negative number (e.g.) over a 5-year period you go from making $100 in Year 1 to losing $50 in year 5 - what is the -CAGR in this calculation)?

    Thanks in advance for any help that can be offered.

  7. #7
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,673

    Default Re: CAGR

    Try looking at the IRR function in Excel. Dependent on what information you are using (and what circumstances it is modelling) will determine the applicability of IRR as a measure. If I had an investment eg in a bank earning +x% interest at the start but earning -x% interest at the end, I wouldn't leave said investment in the bank so the -x% wouldn't apply...
    Richard Schollar

    Using xl2013

  8. #8
    New Member
    Join Date
    Feb 2011
    Posts
    1

    Default Re: CAGR

    Quote Originally Posted by fairwinds View Post
    Hi,

    Try this UDF

    Code:
    Function CAGR(First, Last, Periods)
        CAGR = ((Last / First) ^ (1 / Periods)) - 1
    End Function
    Put the code in a module and use

    =CAGR(A1,A2,A3)

    in the sheet as a normal formula
    Just to say thanks. This has worked perfectly first time of trying.

  9. #9
    New Member
    Join Date
    Dec 2011
    Posts
    2

    Default Re: CAGR

    I believe the correct formula for excel is as follows:

    =((Last#/First#)^(1/((count(data range))-1))-1)

    The rate calculated from this formula can be tested by multiplying the first number ("First#" in the formula listed above) by the rate and continuing to multiply each subsequent number by the rate until you reach the nth point. That final number should equal the last number ("Last#" in the formula listed above).

    For example, if an actual trend is as follows (if each data point is in the same excel row):
    cell A1: 229,363
    cell B1: 225,309
    cell C1: 191,707
    cell D1: 146,023

    The CAGR as calculated by my formula above will be -14.0%

    The formula for the values above would look like this:
    =((D1/A1)^(1/((COUNT(A1:D1))-1))-1)

    It can be proved by multiplying 229,363 (and subsequent results) by -1.14%. The trend will look as follows:
    n1: 229,363
    n2: 197,313 (calculated)
    n3: 169,741 (calculated)
    n4: 146,023 (calculated) - THE SAME VALUE AS cell D1!

    Hope this helps...

  10. #10
    New Member
    Join Date
    Dec 2011
    Posts
    2

    Default Re: CAGR

    Quote Originally Posted by bobtownusa View Post
    I believe the correct formula for excel is as follows:

    =((Last#/First#)^(1/((count(data range))-1))-1)

    The rate calculated from this formula can be tested by multiplying the first number ("First#" in the formula listed above) by the rate and continuing to multiply each subsequent number by the rate until you reach the nth point. That final number should equal the last number ("Last#" in the formula listed above).

    For example, if an actual trend is as follows (if each data point is in the same excel row):
    cell A1: 229,363
    cell B1: 225,309
    cell C1: 191,707
    cell D1: 146,023

    The CAGR as calculated by my formula above will be -14.0%

    The formula for the values above would look like this:
    =((D1/A1)^(1/((COUNT(A1:D1))-1))-1)

    It can be proved by multiplying 229,363 (and subsequent results) by -1.14%. The trend will look as follows:
    n1: 229,363
    n2: 197,313 (calculated)
    n3: 169,741 (calculated)
    n4: 146,023 (calculated) - THE SAME VALUE AS cell D1!

    Hope this helps...
    Quick Correction: the first value isn't multiplied by -1.14%...you just apply the decline rate. Would technically be 229,363 + (229,363 * -.14).

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com