» Calculating Average Annual Growth
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Columns A & B show annual profits for a number of years.
We want to calculate the average rate at which profits grew each year.
Solution:
Use the AVERAGE function in the following Array formula:
{=AVERAGE((B3:B5-B2:B4)/B2:B4)}
Book Store:
Average Annual Growth
John Sharp
May I suggest a small experiment because I cannot find a fully general growth function that suits my needs and this solution is not particularly precise.
In cells A1:A5 enter 100, 105, 110, 115, 120.
In A7 enter {=AVERAGE((A2:A5-A1:A4)/A1:A4)}, as recommended here,
in A8, {=GEOMEAN(A2:A5/A1:A4)-1} and
in A9, enter =(A5/A1)^(1/4)-1.
This last formula is a simple compounding growth rate where 4 is the number of year's growth.
In cells B2 enter =A1*(1+A7)
in B3, =B2*(1+$A$7)
in B4, =B3*(1+$A$7) and
in B5, =B4*(1+$A$7).
In C5 enter =A5-B5.
If the growth formula is accurate, B5 should = A5; i.e. C5=0. Use four or more decimals of precision because these numbers are not big--real world numbers can be five or more orders of magnitude bigger.
If you use the averaging formula (A7) in column B, B5 does not = A5; if you use the geometric mean (A8), it does; if you use the simple growth formula (A9), it also does.
Conclusion: using the arithmetic mean is not as precise as using the other two methods.
Problem: now change A5 to -20. The formulas collapse. The arithmetic mean gives an answer of -25.7710%, which cannot be right because it is asymptotic to zero, while the other two cannot be calculated. Yet the progression in column A, or its inverse, often arises in financial data (profits are not a bad example).
Question: what is to be done?
Regards, John.
jasharp@telstra.com
In cells A1:A5 enter 100, 105, 110, 115, 120.
In A7 enter {=AVERAGE((A2:A5-A1:A4)/A1:A4)}, as recommended here,
in A8, {=GEOMEAN(A2:A5/A1:A4)-1} and
in A9, enter =(A5/A1)^(1/4)-1.
This last formula is a simple compounding growth rate where 4 is the number of year's growth.
In cells B2 enter =A1*(1+A7)
in B3, =B2*(1+$A$7)
in B4, =B3*(1+$A$7) and
in B5, =B4*(1+$A$7).
In C5 enter =A5-B5.
If the growth formula is accurate, B5 should = A5; i.e. C5=0. Use four or more decimals of precision because these numbers are not big--real world numbers can be five or more orders of magnitude bigger.
If you use the averaging formula (A7) in column B, B5 does not = A5; if you use the geometric mean (A8), it does; if you use the simple growth formula (A9), it also does.
Conclusion: using the arithmetic mean is not as precise as using the other two methods.
Problem: now change A5 to -20. The formulas collapse. The arithmetic mean gives an answer of -25.7710%, which cannot be right because it is asymptotic to zero, while the other two cannot be calculated. Yet the progression in column A, or its inverse, often arises in financial data (profits are not a bad example).
Question: what is to be done?
Regards, John.
jasharp@telstra.com