"Evan" wrote:
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