ECMT2130: Financial econometrics 1 Tutorial 03: Portfolio optimisation in Excel This week we give R a rest, to focus on the skills needed to solve small-scale portfolio optimi- sation problems. This will be very helpful to you in the mid-semester exam. The answers that you need to enter into the Canvas quiz this week are based upon Excel analysis. Use the example Excel spreadsheet from lecture 03 to get a start on how to do this week’s Excel exercises. It is going to develop the skills you need to do the first half of the mid-semester exam. 1 Matrix multiplication This part of the assignment is for discussion in the tutorial. Before attending the tutorial try these yourself then check your solutions against those provided in the Canvas Module for this assignment. That way the tutorial can be efficient enough to cover the more interesting portfolio optimisation questions. Answers are not to be uploaded to a Canvas quiz. 1. Matrix operations in Excel and R. Do the following matrix multiplications by hand, in Excel, and in R. (a) Given the 1× 3 row vector v = [1, 3, 5], compute v′v and vv′ by hand. What are the dimensions (rows and columns) of the two calculations? Can you check your calculations using Excel and using R? (b) Given the 1×2 row vector v= [1, −1], and the square matrix: A= ( 3 1 1 2 ) compute vAv′ by hand, in Excel, and in R. What are the dimensions of the calculation result? (c) An investor has access to two risky assets with returns given by the 2 by 1 vector [r1 r2]. The investor forms a portfolio using the two assets with weights given by the 2 by 1 vector [w1 w2]. The variance-covariance matrix for the asset returns is: V = ( σ21 σ12 σ12 σ22 ) i. Using algebra, perform the matrix multiplication needed to express the variance of the portfolio return, w′Vw, without matrix notation. ii. If the correlation (σ12/(σ1σ2)) between the two asset returns is equal to zero, are there benefits to diversification? iii. What about if the correlation is -1? ECMT2130: Financial econometrics 2 Simple portfolio optimisation problems Enter your numeric answers to this part of the assignment in the Canvas quiz. To complete this part of the tutorial, you will need to use Excel. Also upload the Excel spread- sheet with your calculations in the Canvas quiz. Data for the various questions: • The risk-free rate of return is 2% • The expected return on risky asset A is 5% • The expected return on risky asset B is 4% • The expected return on risky asset C is 7% • The variance-covariance matrix of risky asset returns is (ordering rows and columns as A, B, C): V = 0.032 0.5×0.03×0.04 00.5×0.03×0.04 0.042 0 0 0 0.102 • The investor’s expected utility is given by: E(U) = E(rp)−0.5×40σ2p 1. If an investor has access to (can invest in) all three risky assets and the risk-free asset, what is the weight on the risk-free asset in the portfolio with the lowest variance? Solve by hand. HINT: this should be pretty trivial. If you find yourself doing complex algebra, step back from the work and think about what a risk-free asset is. 2. If an investor can lend at the risk-free rate of return but cannot borrow at the risk-free rate of return and cannot short (have a negative weight for) any risky assets, what is the maximum expected return that can be achieved and what portfolio weights achieve this maximum? Solve by hand. HINT: Again this should not involve complex calculations. 3. If the investor can only invest in assets A and B, what is the minimum portfolio rate of return standard deviation that can be achieved in the investor’s fully invested portfolio? Solve by hand and solve using the Excel solver. ECMT2130: Financial econometrics 3 4. If the investor can invest in all of the risky assets but not the risk-free asset, what is the minimum variance that can be achieved in the investor’s portfolio while also having an expected return of 10%? What weights achieve this minimum variance for the given expected return? Solve using the Excel solver. To solve it by hand, you would need to solve the two constraints for wB and wC in terms of wA and then uses these equations to express the expected utility function in terms of wA. This would get messy. 5. If the investor can only invest in the risky assets A and B, what portfolio weights maximise their expected utility? What is the expected return of the portfolio that maximises the investor’s expected utility? Solve by hand and solve using the Excel solver. 6. If the investor can only invest in the risk-free asset and the risky assets A and B, what is the standard deviation of the tangency portfolio (that portfolio of risky assets only that maximises the portfolio Sharpe Ratio? Solve using the Excel solver. 7. If the investor can only invest in the risk-free asset and the risky asset C, what is the expected rate of return of the optimal portfolio for the investor? Solve by hand and using the Excel solver.
欢迎咨询51作业君
