Estimation of Expected Return and Variance/Covariance Matrix Go to Yahoo Finance on the Internet (http://tinancc.yahoo.conV). Retrieve 37 month>> of monthly price data (from December 2011 to December 2014) for the following stocks: BSX, BXP, SAM, and SPY. This can be located by clicking "historical prices." which will be located on left of the page under quotes. You can click on "download to spreadsheet." and this will save the data as an Excel spreadsheet. Calculate 36 monthly returns for each stock from this data. The adjusted close reflects dividends and stock splits, so you can assume that there are no dividends and use it to calculate returns. You should use Excel. Consolidate all returns in the same spreadsheet. This can be accomplished by copying and pasting across spreadsheets. What is the arithmetic average return for each stock? What is the geometric average return for each stock? What is each stock's return standard deviation? Construct a return variance-covariance matrix for the four securities. Use Excel's "Data Analysis" function (located in the "Tools" menu). If you do not see "Data Analysis." click on "Add Ins" and install the "Analysis Toolpak." What is the return standard deviation of a portfolio that only holds BSX? Construct a second portfolio of that has equal weights in BSX and BXP. Third, construct a portfolio that has equal weights of BSX, BXP, and SAM. Fourth, construct a portfolio with equal weights in BSX, BXP, SAM, and SPY. Report the standard deviation of each portfolio. Perform two ordinary least square regressions, regressing the returns of BSX on BXP, and the returns of SAM on SPY. What does the slope coefficient tell us? What does the R^2 tell us? Use Excel to graph two minimum variance frontiers. Graph the minimum variance frontier from various combinations of BSX and BXP. Also, graph the minimum variance frontier from combinations of SAM and SPY. Assume that each stock has an expected return equal to its 36- month average and assume the covariance structure estimated in part 2). Create a column of various portfolio weights. Use the formulas provided in class to calculate the expected return and standard deviations for various portfolio weights. A graph of expected return and standard deviation could be constructing by highlighting the expected return and standard deviation columns, and clicking the chart option in Excel. A scatter plot is sufficient. Make sure you have enough different observations (at least 50), such that the shape of the frontier is apparent. Also, make sure that you include portfolios that involve short-selling. Do you think it is good to assume (as in part 5) that future return will be equal to average return? Explain. On your plot, mark the regions that involve short selling. If you wish, this may be done manually with a pen, instead of using Excel
