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

Attached Files

Copy of CAGR's.xlsx (11.6 KB, 11 views)

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

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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

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.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

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