CAGR function (Excel 2003)

Topic

New Reply

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?

Reply | Quote

Replies

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

=RATE(A1,,-A2,A3) 

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

Steve

Reply | Quote

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.

Reply | Quote

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:

=(A3/A2)^(1/A1)-1

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:

=RATE(A1, 0, -A2, A3)

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

Reply | Quote