Average Annual Growth Rate

Hi,

I have a series of sales annual sales figures and I would like to determine the average growth rate, be it positive or negative. I've tried the XIRR function but can't seem to make it work. Does anyone else have any suggestions? A sample is included below:

3,549,233 30-Jun-08
2,200,158 30-Jun-07
2,091,082 30-Jun-06
1,197,515 30-Jun-05
1,119,110 30-Jun-04

Thanks in advance,

droddis

Hi Droddis,

XIRR expects at least one positive cash flow and one negative cash flow; otherwise, XIRR returns the #NUM! error value, all of your numbers are positive so it shows an error.

hope this helps
reg

Thanks Reggie1000,

I would assume then that this is the wrong way to approach the problem. Do you know of any functions or formulas that would return the average growth rate over a defined period?

Cheers,

You take the nth root of the start and end values, where n is the number of years in the period, to obtain the growth factor (i.e., 1 of there is no growth). Then subtract 1 to get the positive or negative growth rate:

=(A1/A5) ^ (1 / (ROWS(A1:A5) - 1) ) - 1 = ~33.4%

you could also try

=rate(number of years,number of payments,initial payment(must be negative),total growth)

so =RATE(4,0,-1119110,2430123) = 21%

thanks reg

Since that gives such a different answer, and only one can be right:

Please Login or Register  to view this content.

For an exponential best fit to the data (what you'd see if you plot the data and add an exponential trendline):

Please Login or Register  to view this content.