calculating a compound annual growth rate

[Celeste]

I used to be able to calculate CAGR very easily in Lotus -
but have been unable to find the formula in Excel.
Normally one inputs the following: begining value,
ending value and n for number of years - formula provides
the compound annual growth rate. How can I do this in
excel??

 

[Norman Harker]

Hi Celeste!

Use the RATE function:

=RATE(nper,pmt,pv,[fv],[type],[guess])

To calculate CAGR

=RATE(5,0,-100,150,0,0)
Returns: 8.44717711976986%

Alternative / check by formula

=(150/100)^(1/5)-1
Returns: 8.44717711976985%

Note the use of the strict sign convention for direction of money
flows. I've adopted the persona of the investor. The initial
investment is given the negative sign and the terminal value is given
the positive sign.

Also not that the rate of return is the effective rate of return per
period of time "counted" by the nper argument.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Thursday 31st July: Bermuda (Cup Match Day),
Congo (Upswing of the Revolution), Mexico (Day of Mourning), Peru (San
Ignacio). Observances: Lunasa / Lammas (Pagan N. Hemisphere), Oimelc /
Brigid (Pagan S. Hemisphere)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.