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

  Page 1 of 6

FIN 3303 Homework #2

a) You want to develop an efficient portfolio using these assets and you'd 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 zx = Ax-1b0, where zx is a vector that contains the optimal portfolio weights. First, write-out the matrix Ax. Note: Ax 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 b0 (i.e., write-out the vector by hand and show the elements). Note, b0 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 you'd like to earn on the efficient portfolio and the last row always contains a 1.

d) Type the matrix Ax and the vector b0 into Excel and compute the inverse of matrix Ax 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 Ax is in cells A6 to E10 and you'd 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 Ax and vector b0. Again, you need to highlight an area that has the same dimension as the vector that will result from the calculation. For example, if you'd 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 Ax-1 and A18 to A22 is the location of b0. 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, we'll calculate the weights for the tangency portfolio. First, create vector of excess returns (i.e., define a vector = r - rf). You'll also need a row vector populated with 1s for each asset (i.e., create row with 3 cells, each with a 1 in it).