Hi there, I am currently doing the below question. Could you please assist me in constructing this tangency portfolio as well as graphing it on the efficient frontier using the following data in the screenshot of the excel spreadsheet? Any help would be greatly appreciated.
Q5. Suppose the annual rate of return on the risk-ee asset is 1.5%. Construct the tangency portfolio of the 5 selected stocks (M). Calculate and provide the following values in the worksheet \"Q5 \" in the Excel template. Show your working in the Excel le and explain clearly the steps and formulas involved in your Word report. I. The weights of the selected 5 stocks in the tangency portfolio (M) [2 marks] 11. The monthly expected return and standard deviation for the tangency portfolio (M) [2 marks] 1H. Explicitly label the tangency portfolio (M) in the graph sketched in Q4 (III) [1 mark] IV. The Sharpe ratio of the tangency portfolio (M) [1 mark] B C D E F G H K L M N o P Q5: Tangency Portfolio of 5 Stocks (6 Marks Q R S T U V W X Y Z AA 1. Weights of Stocks A2M BXB QBE SYD WPL Provide the names or issuer codes of the stocks here. W1 W2 W3 W W5 Tangency Portfolio 1.207 -0.336 0.747 -0.110 -0.508 Total = 1 Return on RF asset = 1.50% 9 II. Expected Return and Risk 10 Return (%) Std Dev % Tangency Portfolio 1.05% 16% 12 Ill. Show Tangency Portfolio on Efficient Frontier Below E(R) Diagram for Efficient Frontier 0.016 0.014 0.012 0.01 0.008 0.006 0.004 0.002 0 0.056 0.0565 0.057 0.058 0.0585 0.0595 IV Sharpe Ratio 5.729% Ill. Variance-Covariance Matrix from Q2 Issuer Code A2M BXE QBE SYD WPL 0.0165 Inverse var-cov matrix A2M 0.0013 0.0000 0.000618 -4E-05 Z BX 0.0013 0.0026 0.002348 0.002864 62.75298 -24.7973 4.6356 -7.1716 10.17444 0.0044 3.013729 QBE 0.0000 0.00348 0.00492 -24.7973 336.1371 -47.04 -66.3415 -56.2791 -0.83838 wi 1.206967 0.0026 4.63556 -47.0365 216.25 -45.0783 -92.3155 w2 1.206967 -0.33576 0.74724 -0.11003 -0.50841 -0.33576 SYD 0.0006 0.0023 0.0035 0.005586 0.00426 1.865816 w3 0.74724 WPL -4E-05 0.002864191 2864191 0.0049222 0.00426 0.007989 7.1716 -66.3415 -45.08 331.2379 -125.118 0.27475 N4 10.17444 -56.2791 -92.32 -125.118 269.0054 0.11003 1.26948 w5 -0.50841 Total z 2.496945 E Expected return of the portfolio from Q3 0.0486931 0.001008 0.0057027 0.000554 -0.004651
