Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Leesburg, Virginia, USA
    Posts
    618
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CAGR function (Excel 2003)

    I would like to compute the compound annual growth rate (cagr) for a series of earnings. Excel help suggest using the XIRR function but this function contemplates an investment situation with the first variable a negative number (the amount of the investment). Is there a function that will compute the cagr of just a series of numbers?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CAGR function (Excel 2003)

    Is the CAGR the same number you would get from the RATE function?

    <pre>=RATE(A1,,-A2,A3) </pre>


    where A1 the number of years, A2 contains the starting amount, and A3 the ending amount

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Lawrence, Kansas, USA
    Posts
    202
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CAGR function (Excel 2003)

    CAGR and Rate are not necessarily the same. Rate also assumes that you have different signs.

    I don't know of a function besides the XIRR function that will give CAGR, but you can use this formula:
    Assume that the beginning date is in A1 and the ending date is in B1. The values are in A2 and B2.

    =((B2/A2)^(365/(B1-A1)))-1

    This formula doesn't work if the numbers change sign, but you only need the beginning and ending value to calculate a CAGR. XIRR works well if you have cash inflows and outflows (sign changes) with irregular dates.

    If you have a series of return relatives (1+return), then you can use GEOMEAN function to calculate the CAGR. However, the returns have to have the same periods ( annual, weekly, daily, etc)

    I've attached an example that shows XIRR and the formula.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CAGR function (Excel 2003)

    Yes I realize that CAGR and RATE are not neccessiraly the same, but neither is XIRR. The CAGR is only 1 situation, the rate (and XIRR) can calculate for a variety of situations.

    It can be calculated in the more "typical" CAGR form:
    <pre>=(A3/A2)^(1/A1)-1</pre>


    Where A1 = Number of years
    A2= The starting Value
    A3 = Ending value

    But my point was that the CAGR function above is equivalent to using the rate with the form:
    <pre>=RATE(A1, 0, -A2, A3)</pre>


    The XIRR can also be used as you pointed out. But this function is also not neccessarily the same. The XIRR can also be used with more than just the start and the ending values and this can lead to values that are not the same as using the CAGR. If there is a series of values, the starting and ending values as well as the starting and ending dates need to be extracted from the range to allow the use of XIRR to give the equivalent CAGR. If XIRR is used the start value must be in the worksheet as negative, it can not be fixed with the formula, but has to be adjusted in the worksheet.

    The rate function can use the numbers directly in the worksheet without having to change the sign. The sign change is part of the function not a change in the worksheet values. It also does not require extracting the values from the worksheet to place them in a form that can be used. The number of years can also be calculated from the start and end dates if desired.

    But ultimately It comes down to the user about which is easiest to use as equivalent forms can be used. I don't see why the direct formula could not be used, it is not that complex, but rate seems to be a simpler equivalent that XIRR to me.

    Steve

Posting Permissions

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