Chapter 11 Internet Exercise

Choose two stocks from different industries that you think would have a low correlation. Get the closing prices for each month over the past five years for both stocks. To get the prices from Yahoo! Finance (http://finance.yahoo.com), follow the same procedure as was used in Chapter 8 to get the dividends, but this time select "Monthly" rather than "Dividends." Download the data to your disk drive and then load the files into Excel.

1. Calculate the returns for both stocks in each month during the five year period.
   
   
2. Calculate the average monthly return and standard deviation of monthly returns for each stock. Using only this information, which of the two stocks would you have preferred to own over this period?
   
   
3. Calculate the correlation coefficient for the two sets of returns. Is it as low as you expected?
   
   
4. Assuming that the historical returns, standard deviations, and correlation fairly represent the future, calculate the expected return and standard deviation of a portfolio consisting of 50% invested in each stock. How does the portfolio compare on a risk/return basis with the individual stocks?
   
   
5. Using the Solver, find the weights for each stock that would result in the minimum portfolio standard deviation.