Jan 2125
Feb 2055.63
Mar 2303.889
Apr 2624.77
May 1978.356
Jun 2084.52
I want to calculate the growth rate percent for the above
data. The data are not financial.
It actually does not matter whether or not the data are financial.
The same formulas might apply.
What does matter is whether those numbers represent a time series --
snapshots of the value of the same thing(s) over time -- or something
else entirely.
For a time series, the month-to-month growth rate can be computed by
=B2/B1-1 for example, assuming that B2 is 2055.63 and B1 is 2125.
The (geometric) average monthly growth rate of a time series can be
computed by =RATE(5,0,-B1,B6) or (B6/B1)^(1/5)-1, formatted as
Percentage, where B6 is 2084.52 and 5 is the number of time periods
minus one (i.e. the number of changes).
Note that if that rate is in C6, =FV(C6,5,-B1) is B6. That is, it is
the compounded monthly rate.
However, for some purposes, it is desirable to compute the
__arithmetic__ average monthly growth rate -- for example, if you plan
to use that rate for simulation.
In that case, the average monthly growth rate would be about 0.7% as
you computed (I presume). You can use the following array formula
[*]:
=AVERAGE(B6:B2/B5:B1)-1
formatted as Percentage.
But if that rate is in C6, note that FV(C6,5,-B1) is __not__ B6. That
is, the arithmetic average rate is __not__ the compounded monthly
rate.
[*] Enter an array formula by pressing ctrl+shift+Enter instead of
Enter. Excel will display an array formula surrounded by curly braces
in the Formula Bar, i.e. {=formula}. You cannot type the curly braces
yourself. If you make a mistake, select the cell, press F2 and edit,
then press ctrl+shift+Enter.
