CAGR formula in Excel

[meggenm]

I have a job at work I desperately need help with. I am calculating a Compound Annual Growth Rate for data in the Excel table attached (Copy of CAGR's)



Basically I have four years and revenue data for each year,
I have attempted to use the XIRR formula and keep getting a Number Error message in cell. I have looked at help for this function etc...

Please advise formula to use, I KNOW HOW TO MANUALLY CALCULATE with CAGR MATHEMATICAL EQUATION, but wish to learn how with a formula.

CAGR is a popular data figure, I would find it hard to believe Excel has no formula for such. Help!!! and Big Thanks!!!

Meggen

[Pecoflyer]

I'm not versed in financial functions. Could you perhaps post a sheet containing the XIRR function where it gives an error? Thx

[meggenm]

Pecoflyer:

My problem is that the XIRR function requires a negative value as part of data in formula, and I have no negative values in the tables in which I need to calculate a CAGR ..I will post attachment again with calculation included.
See this website: http://office.microsoft.com/en-us/excel-help/xirr-HP005209341.aspx?CTT=5&origin=HP001122506...
Thanks sooo much, I need an expert here. MMM

[macropod]

There's at least a 3 problems with your worksheet:
1. For an XIRR function, the years on row 3 must be dates (e.g. 1/1/2010), not just numbers (e.g. 2010) as you now have.
2. You must have at least one -ve and one +ve value. As yet, you only have +ve values.
3. Since your dates are evidently periodic, you should be using the IRR function, which would also free you from the need for true dates on row 3.

[meggenm]

Thanks so much Paul. I gathered the same from research!
MMM