Simple net returns
	Big Growth	Big Value	Small Growth	Small Value
1927	46.18	31.25	31.42	35.29
1928	48.05	23.63	34.86	40.96
1929	-21.07	-3.93	-44.23	-35.77
1930	-26.44	-43.16	-35.85	-46.38
1931	-36.96	-58.24	-42.70	-51.87
1932	-7.93	-3.26	-5.25	1.35
1933	44.65	116.91	159.41	118.69
1934	11.06	-21.51	35.89	8.51
1935	42.22	51.14	48.34	53.16
1936	26.46	48.12	37.10	73.19
1937	-34.12	-41.07	-48.64	-51.47
1938	33.20	25.20	43.81	26.21
1939	7.73	-12.51	10.72	-3.55
1940	-9.81	-2.62	0.57	-9.83
1941	-12.67	-0.88	-17.34	-4.82
1942	13.17	33.71	16.76	35.00
1943	22.04	44.02	45.08	91.82
1944	16.11	41.98	41.23	49.71
1945	31.95	49.06	64.28	74.61
1946	-8.29	-8.29	-12.40	-7.36
1947	4.10	8.66	-8.38	5.34
1948	3.35	5.09	-7.16	-2.30
1949	23.31	18.71	23.52	21.04
1950	23.11	55.22	31.01	52.16
1951	20.05	14.36	16.26	12.27
1952	13.38	19.54	8.55	8.59
1953	2.29	-7.04	-0.68	-6.92
1954	47.79	77.32	43.20	63.43
1955	28.50	29.78	13.95	23.47
1956	6.52	3.37	7.65	5.98
1957	-9.14	-22.72	-16.99	-15.90
1958	41.62	72.30	75.22	69.67
1959	13.15	18.82	21.42	17.42
1960	-2.36	-8.56	-1.78	-6.02
1961	26.43	28.89	22.20	30.85
1962	-10.89	-3.09	-22.33	-9.47
1963	21.88	32.35	7.98	28.34
1964	14.48	19.16	8.13	22.90
1965	13.36	22.42	39.99	42.50
1966	-10.77	-10.21	-5.32	-7.76
1967	29.17	31.74	88.42	67.55
1968	4.03	27.08	32.73	45.81
1969	2.88	-16.39	-23.68	-25.84
1970	-5.65	10.63	-20.25	6.62
1971	23.94	12.55	25.86	14.47
1972	21.32	18.62	0.39	7.28
1973	-21.79	-3.67	-45.07	-27.23
1974	-29.24	-23.40	-31.90	-19.02
1975	34.44	55.90	61.32	57.12
1976	17.54	44.62	38.20	59.13
1977	-9.46	1.64	19.35	23.82
1978	7.00	3.48	17.65	22.12
1979	16.59	22.67	48.84	38.33
1980	35.20	16.45	52.66	22.28
1981	-7.13	12.80	-11.53	17.68
1982	21.48	27.67	19.72	39.86
1983	14.67	26.92	22.12	47.58
1984	-0.72	16.17	-12.84	7.52
1985	32.64	31.75	28.91	32.12
1986	14.38	21.82	1.95	14.50
1987	7.43	-2.76	-12.24	-7.12
1988	12.53	25.96	16.63	30.76
1989	36.11	29.70	20.58	15.70
1990	1.06	-12.75	-17.74	-25.13
1991	43.33	27.35	54.73	40.56
1992	6.41	23.57	5.82	34.76
1993	2.38	19.51	12.64	29.41
1994	1.95	-5.78	-4.36	3.21
1995	37.16	37.68	35.13	27.69
1996	21.25	13.35	12.36	20.71
1997	31.61	31.88	15.29	37.29
1998	34.64	16.23	3.04	-8.63
1999	29.43	-0.22	54.75	5.59
2000	-13.63	5.80	-24.15	-0.80
2001	-15.59	-1.18	0.16	40.24
2002	-21.50	-32.53	-30.87	-12.41
2003	26.29	35.07	53.20	74.69
2004	6.53	18.91	12.54	26.59
2005	2.82	12.17	5.45	3.53
2006	8.88	22.61	11.67	21.76
2007	14.08	-6.45	7.36	-15.21
2008	-33.71	-49.03	-41.56	-44.39
2009	27.91	39.15	34.45	70.54
2010	15.87	21.61	30.66	33.54
2011	4.14	-9.04	-4.32	-7.04

Please answer a - h

The spreadsheet returns.xls contains annual return data for big growth stocks, big value stocks, small growth stocks, and small value stocks. We are going to use Excel to explore the efficient frontier of value and growth stocks using these data. Please turn in a printout of your two Excel graphs with your solution.

(a) Compute the annual net simple return of value and growth stocks in natural units by taking 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 function. 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 rf = 2%. In a fifth column nextto 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 riskfreeasset 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?