Results 1 to 4 of 4
Thread: CAGR function (Excel 2003)

20080311, 18:34 #1
 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?

20080311, 18:53 #2
 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

20080312, 03:32 #3
 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/(B1A1)))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.

20080312, 13:46 #4
 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