FINA-4472 Assignment 2 Due: 6pm, Wednesday, March 17 • Use Microsoft Excel to conduct your analysis, calculate values and plot figures. • Upload a write-up report of the answers (in PDF version) and a...

see attached


FINA-4472 Assignment 2 Due: 6pm, Wednesday, March 17 • Use Microsoft Excel to conduct your analysis, calculate values and plot figures. • Upload a write-up report of the answers (in PDF version) and a copy of your Excel spreadsheet to Brightspace Dropbox. – The write-up report should summarize the answers, show final results and provide all the figures. – The Excel spreadsheet should show detailed steps and Excel functions used. • Late assignments will have 20% of total marks for the assignment deducted for each day/partial day beyond the due time. Please download weekly price data of eight stocks contained in Don Jones Industrial Average index from January 4th, 2016 to December 30th, 2020 using Yahoo Finance. Assume a risk-free rate of 125 basis points. Use the “adjusted close” price when calculate returns. (a) For all eight firms compute the mean, variance and Sharpe ratio (both weekly and annual). (b) Compute the correlation matrix and variance-covariance matrix of the eight stocks. (c) Comment on the performance of two of the eight firms vis-à-vis the DJIA index over the past five years. Did these two stocks outperform or underperform DJIA index? How were the two stocks correlated with the index, positively or negatively? (d) Choose two stocks from the eight stocks. Draw the investment opportunity set with two stocks. (e) Draw the minimum variance frontier by creating portfolios of the eight stocks with short sale constraint. Here you should use Excel solver to come up with at least 8 portfolios including the global minimum risk portfolio. [Hint: add constraints that all weights are greater than zero or turn on the “making unconstrained variables non-negative” option in Excel solver. ] (f) Draw the minimum variance frontier by creating portfolios of the eight stocks without short sale constraint. Again use at least 8 portfolios to plot the frontier. How does the frontier change compared with the result in question (e)? (g) Plot the two stocks opportunity set you got in question (d) and the minimum variance frontier in question (f) in one picture. Does a eight-asset portfolio provide more diversification benefit according to your result? (h) Calculate the risk contribution of the eight stocks in the global minimum variance portfolio with short sale constraint. Which stock has the highest contribution to the portfolio variance? (i) Compute the weight of the optimal risky portfolio without short sale constraint. Comparing the Sharpe ratio of the optimal portfolio and the eight stocks. (j) Compute the weight of a portfolio on the efficient frontier, has a standard deviation 30% higher than the global minimum variance portfolio, and has no short sale constraint. What is the expected return of this portfolio? FINA-4472 Assignment 2 Page 2 of 2 (k) Assuming investors’ utility function is U = E(r) − Aσ2. Plot utility levels of portfolios on the investment opportunity set defined in question (k), for both investor James (risk aversion coefficient = 3) and Peter (risk aversion coefficient = 5). (l) Assuming investors’ utility function is U = E(r) − Aσ2. Find the optimal capital allocation solution for James (risk aversion coefficient = 3) and Peter (risk aversion coefficient = 5). Create pie charts of asset weights in the optimal complete portfolio for both investors. The End.