Note: For this problem, you will compute an efficient portfolio in Excel using matrix algebra. To receive credit for this problem you must attach a printout of your calculations in Excel.

You have $1 million in cash and you would like to allocate it between the common stock of the following three firms: firm A, firm B, and firm C. You know the risk free rate is 0% and you have the following information about the stock of the three firms:

	Stock A	Stock B	Stock C
Expected Return	7%	6%	10%
Standard Deviation of Returns ()	30%	19%	16%
Correlation(retA, retb)	0.0954	0.0954
Correlation(retA, retc)	0.0145		0.0145
Correlation(retb, retc)		0.1500	0.1500

a) You want to develop an efficient portfolio using these assets and youd like the resulting portfolio to have an expected return of 10%. First, define the return vector, r, and the covariance matrix, , of the assets (i.e., write-out the vector and matrix and show the elements in them using the data in the table)

b) You know the solution to the portfolio optimization problem is z_x = A_x^-1b₀, where z_x is a vector that contains the optimal portfolio weights. First, write-out the matrix A_x. Note: A_x is defined as 2x the covariance matrix (i.e., multiply every element in the covariance matrix by 2) and then add two additional rows to the bottom of the matrix and two additional columns to the right of the matrix. The first of these additional rows and columns should show the expected returns from vector a, and the last of these rows and columns should have a 1 for each asset and then zeros elsewhere.

c) Then, define the vector b₀ (i.e., write-out the vector by hand and show the elements). Note, b₀ is a vector that has a 0 for each asset and then two additional rows. The first of these rows contains the target rate of return youd like to earn on the efficient portfolio and the last row always contains a 1.

d) Type the matrix A_x and the vector b₀ into Excel and compute the inverse of matrix A_x using the function =MINVERSE. Note: To use matrix functions in Excel, you need to highlight an area that has the same dimensions as the matrix that will result from the calculation. For example, if matrix A_x is in cells A6 to E10 and youd like to create the answer in cells A29 to E33, you would highlight cells A29 to E33, type =MINVERSE(A6:AE10) and hit ctr + shift + enter at the same time to calculate the result.

e) Using your answer from part (d), calculate the optimal weight vector using the matrix multiplication function MMULT. You want to multiply the inverse of A_x and vector b₀. Again, you need to highlight an area that has the same dimension as the vector that will result from the calculation. For example, if youd like the optimal weight vector to appear in cells A41 to A45, highlight A41 to A45 and type =MMULT(A29:E33,A18:A22), where A29 to E33 is the location of A_x^-1 and A18 to A22 is the location of b₀. What is the optimal weight vector?

f) Given your answer in (e), what is the matrix algebra equation for the expected return on the optimal portfolio? What is the expected return on the optimal portfolio? [Hint: Use the MMULT function again and multiply the transpose of the optimal weight vector by the expected return vector a.]

g) Given your answer in (e), what is the matrix algebra equation for the expected standard deviation on the optimal portfolio? What is the expected standard deviation on the optimal portfolio? [Hint: you will use the MMULT function again, but you need to break the problem into two steps. First, multiply the transpose of the optimal weights by the covariance matrix; the answer will be a 1x3 vector. Then, multiply this vector by the optimal weight vector to receive the expected portfolio standard deviation.]

h) What is the Sharpe Ratio of the portfolio you just created?

i) In the steps above, you found the weights of the most efficient portfolio that resulted in an expected return of 10%. We saw in class that if we choose different target expected returns and solve for the efficient portfolio, we can trace-out the Markowitz efficient frontier. We also saw that it is possible to find one unique portfolio on the frontier that is the best possible portfolio (a.k.a., the tangency portfolio or the optimal portfolio). In the next steps, well calculate the weights for the tangency portfolio. First, create a vector of excess returns (i.e., define a vector = r r_f). Youll also need a row vector populated with 1s for each asset (i.e., create a row with 3 cells, each with a 1 in it).

j) The weights for tangency portfolio are given by the formula:

t =

where t is the vector of optimal weights. Thus, youll need the inverse of the covariance matrix (). Calculate the inverse of the covariance matrix using the function = MINVERSE.

k) Well calculate the solution vector in two steps. First, calculate the numerator of t by multiplying and the excess return vector (r r_f) using the =MMULT function. Your solution will by a vector that is 1 column wide and 3 rows long (one for each asset).

l) Next, well calculate the denominator by multiplying and the excess return vector (r r_f) and then take the row vector of 1s and multiply it by the solution. For example, your calculation will look something like =MMULT(A96:C96,MMULT(A102:C104,A91:A93)) where cells A96:C96 represent the row vector of 1s, cells A102:C104 contain the inverse of the covariance matrix, and cells A91:A93 contain the vector of excess returns.

m) Then, divide the numbers you calculated in step (k) by the number you calculated in step (l) to get the optimal weights for each asset. Your solution will by a vector that is 1 column wide and 3 rows long (one for each asset). The optimal weights are:

n) Finally, calculate the expected return, standard deviation, and Sharpe Ratio of this portfolio using the same techniques you used in steps F through H. What is the Sharpe Ratio of the best possible portfolio?