Excel Calculate Compounded Growth Rate

[ziad alsayed]

dear all

i am trying to calculate the Compounded Growth rate over for year. i am doing it by month through out the year.

the problem i am facing is that if the sales of last year is less than the sales of first year the below formual will not work .

i am not even sure i am getting the right result when sales of last year is higher than first year

appreciate any help.

Excel Workbook
	A	B	C
1	2007 Figures	19,000,000	19,000,000
2	2008 Figures	18,000,000	18,000,000
3	2009 Figures	14,000,000	14,000,000
4	2010 Figures	13,000,000	19,500,000
5	Compounded growth Rate	#NUM!	2559.15%
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B5	=(B4-B1)^(1/4)-100%
C5	=(C4-C1)^(1/4)-100%

 

[Scott R]

B5 = (b4/b1)^(1/3)-1

 

[shg]

If you want the exponential trend,

      --A--- -----B----- -----C-----
  1   FY2007 19,000,000  19,000,000 
  2   FY2008 18,000,000  18,000,000 
  3   FY2009 14,000,000  14,000,000 
  4   FY2010 13,000,000  19,000,000 
  5   Growth      -13.0%       -2.5%

B4 and across: =LOGEST(B1:B4) - 1

 

[ziad alsayed]

dear shg4421 and Scott R, thanks for your reply.

now i am totally lost

both formula are logig ,but i am not getting the same result.

please check below and advise

Excel Workbook
	A	B	C
1	2007	19,000,000.00	19,000,000.00
2	2008	18,000,000.00	18,000,000.00
3	2009	14,000,000.00	14,000,000.00
4	2010	13,000,000.00	19,000,000.00
5	Scott R	-11.88%	0.00%
6	shg4421	-12.98%	-2.48%
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B5	=(B4/B1)^(1/3)-1
B6	=LOGEST(B1:B4)-1
C5	=(C4/C1)^(1/3)-1
C6	=LOGEST(C1:C4)-1

 

[shg]

One ignores the interior values of the series and just looks at the end points. The other is the slope of a best-fit exponential trendline through the data.

 

[ziad alsayed]

thanks it is now clear,
i will go with your formula , i think it is more accurate.

thanks again for you and for Scott R for your your help.

 

[shg]

Scott's is no less accurate, it just measures something different. You should use the one that makes sense for the question you're trying to answer.

 

[ziad alsayed]

sorry i meant more accurate for my need.