SPREADSHEET QUESTIONS The following questions provide opportunities to practice each of the Excel functions. 1. (VAR.P and STDEV.P) Choose two well-known stocks and download the latest 61 months of adjusted prices from finance.yahoo.com. Calculate the monthly returns for each stock. Now find the variance and standard deviation of the returns for each stock by using VAR.P and STDEV.P. Annualize the variance by multiplying by 12 and the standard deviation by multiplying by the square root of 12.

2. (AVERAGE, VAR.P and STDEV.P) Now calculate the annualized variance and standard deviation for a portfolio that each month has equal holdings in the two stocks. Is the result more or less than the average of the standard deviations of the two stocks? Why?

3. (SLOPE) Download the Standard & Poors index for the same period (its symbol is GSPC). Find the beta of each stock and of the portfolio. (Note: You need to enter the stock returns as the Y-values and market returns as the X-values.) Is the beta of the portfolio more or less than the average of the betas of the two stocks? 4. (CORREL) Calculate the correlation between the returns on the two stocks. Use this measure and your earlier estimates of each stocks variance to calculate the variance of a portfolio that is evenly divided between the two stocks. (You may need to reread Section 7-3 to refresh your memory of how to do this.) Check that you get the same answer as when you calculated the portfolio variance directly.

5. (COVARIANCE.P) Repeat question 4, but now calculate the covariance directly, rather than from the correlations and variances.

6. (RSQ) For each of the two stocks calculate the proportion of the variance explained by the market index. Do the results square with your intuition?