Use the table of annual returns in Problem 5.9 for Home Depot (HD) and Lowe’s (LOW) to create an Excel spreadsheet that calculates the average returns for portfolios that comprise HD and LOW using the following, respective, weightings: (1.0, 0.0), (0.9, 0.1), (0.8, 0.2), (0.7, 0.3), (0.6, 0.4), (0.5, 0.5), (0.4, 0.6), (0.3, 0.7), (0.2, 0.8), (0.1, 0.9), and (0.0, 1.0). Also, calculate the standard deviation associated for each portfolio.

Data From Problem 5.9

The following table contains annual returns for the stocks of Home Depot (HD) and Lowe’s (LOW). The returns are calculated using end-of-year prices (adjusted for dividends and stock splits) retrieved from http://www.finance.yahoo.com/. Use Excel to create a spreadsheet that calculates the return that an equally weighted portfolio of these two stocks would have earned in each year. Then calculate the average return and standard deviation for each stock over this period. Next, calculate the average return and standard deviation for a portfolio that invests 50% in HD and 50% in LOW. What is the correlation between HD and LOW returns over this period?

Transcribed Image Text:

Year 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 HD Returns -4.3% 1.0% -31.1% -11.4% 30.5% 25.0% 23.5% 50.3% 35.9% 30.2% 28.6% 4.0% 44.6% LOW Returns 16.1% -6.1% -26.8% -3.3% 10.6% 9.2% 3.4% 42.9% 41.8% 41.2% 12.1% -0.1% 26.9%