Annualized Growth Rate Formula (Rate Function Plus)

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

1993 14.60%
1994 2.80%
1995 16.30%
1996 18.90%
1997 19.96%
1998 21.41%
1999 9.57%
2000 13.38%
2001 -6.23%
2002 -8.39%
2003 1.55%

Suppose I have the above annual returns. In one cell I
would like to calculate the annualized growth percent. For
these ten years the answer is 8.42%. But I had to use
multiple function and columns to get the answer. I created
another column and Invested $100 at the beginning of 1994.
then used the excell "rate" functions with an
embended "count" function to determin the number of years
(beause this will change).

Can you please help me figure out a one cell formula that
would produce the same 8.42% answer?

Thanks,
stumped
 
Hi Scott!

Method is easy here with Analysis ToolPak function FVSCHEDULE but you
have to take care with dates / period of investment. It's the old
fence posts vs.. panels issue)

I have your data in A1:B11.

If you are invested at the beginning of 1993 and sell / value at the
end of 2003, then the following applies:

=FVSCHEDULE(1,B1:B11)^(1/(A11-A1+1))-1
Returns: 8.96472181798733%

If you invested at the end of 1993, then the first growth rate for
1993 is otiose and for a sale / valuation at end 2003 the following
applies:

=FVSCHEDULE(1,B2:B11)^(1/(A11-A2)+1)-1
Returns: 8.41666507546308%

I've used direct calculation on your year numbers. Probably better for
general purpose use would be to use the COUNT function.

Same principle applies using Paul's suggestion of using the array
formula rather than the Analysis ToolPak:

Either:
=PRODUCT(1+B1:B11)^(1/(A11-A1+1))-1
Entered as an array by pressing and holding down Ctrl + Shift and then
pressing enter.

Or:
=PRODUCT(1+B2:B11)^(1/(A11-A2+1))-1

As to which? There's a general preference for non-Analysis ToolPak
solutions because of the possibilities that Analysis ToolPak may not
be installed or selected as an Addin.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Friday 31st July: Angola (Armed Forces Day),
Barbados, St. Lucia, Trinidad & Tobago (Emancipation Day), Benin
(Independence Day), Bermuda (Somer's Day), China (People's Liberation
Army Day), N. Cyprus (TMT Day), Ghana (Homowo), Nicaragua (St. Dominic
's Day), French Polynesia (Mini Fetes), Solomon Islands (Guadalcanal
Prov Only), Switzerland (Confederation Day), Zaire (Parent's Day).
Observances: Lughnasadh / Festival of Light (Celticism), Kamál
/Perfection (Bahá'í), Naga Panchami,
(Hinduism)[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top