Excel Annualized Return Formula

[Evan]

I am looking for a formula that will calculate annualized return.
Specifically, I have the annual returns for the S&P 500 from 1976 through
2005. I would like to be able to calculate the annualized (compounded or CAGR)
rate of return over various time frames. There is no formula listed under
tools for Annualized return or CAGR in excel. Geo metric mean (GEOMEAN) is
the closest option but it will not take negative numbers. Can anybody help?

thanks,

evanolsen[at]msn[dot]com

 

[Fred Smith]

The function to calculate rate of return is RATE. Feed it the start and end
index values and it will calculate the CAGR for you.

 

[Guest]

I am looking for a formula that will calculate annualized
return. Specifically, I have the annual returns for the S&P
500 from 1976 through 2005. I would like to be able to
calculate the annualized (compounded or CAGR) rate of
return over various time frames.

I assume you mean that you want to compute the average
annual return rate over subset ranges, e.g. 2001-2005.

The term "annualized return" is usually applied to return
rates for non-annual periods; e.g. the annualized return of
a monthly return rate.

There is no formula listed under tools for Annualized return
or CAGR in excel. Geometric mean (GEOMEAN) is the closest
option but it will not take negative numbers. Can anybody help?

GEOMEAN() should work just fine if you add 1 to all of your
simple returns, which I assume are year-to-year percentage
change. For example, if your annual simple return rates are
in A1:A30, enter the following array formula (ctrl-shift-enter):

=geomean(1+A1:A30) - 1

You will probably also want to compute the geometric std dev
(volatility). Enter the following array formula (ctrl-shift-enter):

=exp(stdev(ln(1+A1:A30))) - 1