(a) Download "returns.xls", and clear the contents of columns F - Z (you will only need the data contained in columns A - E). In columns G and H, compute the annual net simple return of value and growth stocks in natural units by tak- ing an weighted average of big value and small value for value and a weighted average of big growth and small growth for growth, with of weight of 0.9 on big and 0.1 on small (e.g., in G3, put =(B3*0.9+D3*0.1)/100, and in H3, put =(C3*0.9+E3*0.1)/100). Also compute the average annual return, variance and standard deviation for both value and growth, using the average, var, and stdev functions in Excel. These are our estimates of the expected return, variance and standard deviation. (b) Calculate the covariance of value stocks with growth stocks using the covar func- tion. If value had a high return in a year, what would you expect happened to growth? What is the correlation coefficient? (c) Now we are going to look at portfolios of value and growth stocks. Find four empty columns next to each other. Put -0.5 in the first cell of the first column, and -0.45 in the cell below it. Select both cells, grab the lower right corner of the selection box, and drag down until you have created a column that runs from -0.5 to 1.5 in increments of 0.05 - this column contains the possible portfolio weights of growth stocks. In the first cell of the second column, calculate the expected return of the associated portfolio. Fill the third column with variances based on the weights in the first column. Use the fourth column to take the square roots of these variances (the standard deviations). (d) Now plot the portfolio standard deviation as a function of weight on growth stocks. (e) Now plot the mean-standard deviation frontier for value and growth in the mean- stdev diagram using a similar procedure. Make sure that the standard deviation is on the horizontal axis. Where is the efficient frontier? What is (approximately) the global minimum variance portfolio? What is the mean and standard deviation of the return for that portfolio? How does it compare with the mean and standard deviation of value and growth? (f) Now we will construct the efficient frontier when there also exists a safe asset. Assume that the annual riskfree rate of return is r = 2%. In a fifth column next to the previous four columns, compute the Sharpe-ratio of the various portfolios of value and growth. Which portfolio has the highest Sharpe-ratio? What is the mean and standard deviation of this (approximate) tangency portfolio? Where is the efficient frontier now? = (g) Suppose that you would like to invest in a portfolio of value stocks, growth stocks, and the riskfree asset. If your coefficient of risk aversion is A : 5, what is the composition of your optimal portfolio (what are the shares of your wealth invested in value, growth and the riskfree asset)? What is the mean and standard deviation of the annual portfolio return? (h) Now suppose that you wish to hold a portfolio of value, growth and the riskfree asset such that the standard deviation of your annual return is equal to 16%. What is the composition of your optimal portfolio? What is the mean and standard deviation of the annual portfolio return?