In Chapter 9, we learned how to calculate the geometric mean return for an investment. In this assignment, you will use that concept, download data from Yahoo! Finance, determine monthly returns for two stock market indices, find out how much money you will have saved during 40 years of working and saving, and ultimately calculate how much money you will be able to withdraw each month in retirement.

Setting up: First, you need historical price data for the S&P 500 Index (Yahoo! Ticker = ^GSPC) and the Russell 2000 Index (Yahoo! Ticker = ^RUT). The S&P 500 is an index made up of 500 of large, publicly traded stocks, while the Russell 2000 Index is made up of small publicly traded stocks. Several ETFs track these two indices. In this assignment, we will assume you are considering investing in an ETF that tracks one of these indices. Go to Yahoo! Finance (finance.yahoo.com) and download the last 10 years of price data for both of these indices and put into an excel spreadsheet.

From the main Yahoo! Finance page, enter the ticker symbols for the indices. Next select Historical Data. In the time period box, under start date, type in 12/31/2010. Enter 12/31/2020 for the end date. Then select monthly for frequency and press the Apply button. Download so that you get an excel spreadsheet of the prices each month for that company. Important note: the data are downloaded in a comma separated value format (CSV). These files do not support Excel functions or equations. So, save the file as an Excel workbook. If you dont, you will lose formatting and functions.

We are going to calculate the return for each month from February 2011 through December 2020 (since you dont have earlier data, you cannot calculate the January 2011 return, even though you have the January 2011 price). The indices do not show dividends, so the return formula from Chapter 9 is just: (Ending price - Beginning price) / Beginning price. Make sure to use the closing price (or adjusted close theyre the same) for each month to calculate returns (e.g., the return for February 2011 is (Feb 2011 close price Jan 2011 close price) / Jan 2011 close price).

1. Once you have calculated the monthly returns for both indices, do the following: a) calculate the average monthly return, b) the standard deviation of monthly returns, and c) the geometric mean monthly return for each index. An investment in which index provides the greater geometric mean monthly return?

2. Assume you have an opportunity to deposit $250 per month into your retirement savings for the next 40 years. Using the geometric mean as the interest rate (the geometric mean is the compounded return), calculate how much money will be in your retirement account in 40 years for both indices (S&P 500 and Russell 2000). An investment in which index provides the greater future value?

3. Assume you move your money to a safe retirement account paying 3% annual interest. Further, assume you will live 25 years after retirement. How much money can you withdraw per month? An investment in which index provides the greater retirement income?