Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Annualized Return Formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Annualized Return Formula
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. -- Regards, Fred "Evan" <u22336@uwe wrote in message news:60e38e78130f6@uwe... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Annualized Return Formula
"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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Excel 4 formula to Excel 2003 format | Excel Worksheet Functions | |||
Recurring Excel Formula error - multiple users affected! | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How do create a formula to evalute a # to return 1 of 4 conditions | Excel Worksheet Functions |