This Take-home assignment comprises three parts. Your task is; (1) to compute the monthly, the average and the standard deviations of the returns for the
This Take-home assignment comprises three parts. Your task is; (1) to compute the monthly, the average and the standard deviations of the returns for the two stocks (Caterpillar and Merck), (2) compute the monthly, the average and the standard deviation for the portfolio returns consisting of the two stocks, and (3), through manual trial and error, to estimate the optimal portfolio weights that produce the highest Sharpe Ratio, a measure for investment return / risk efficiency we discussed in class. Please download the Excel file attached to this assignment and use it as the template
M 1. Compute monthly retums, the average and the standard deviation of the monthly returns for the Individual stocks in the portfolio 2. Compute monthly returns, the average and the standard deviation of the monthly returns for the portfolio 3. Trhough trial and error, find the optimal portfolio weights for Caterpillar and Merck that produce the highest Sharpe Ratio. Use 0.18% as the risk free rate. Optimal Portfolio Weights Caterpillar, Inc Merck & Co Portfolio Weights: Sharpe Rates 1 teaserleigheter Cell Caterplein Merck & Co Total 0.0% -The Iconect. The sum of the Toportoghes you must be 100% Merck & Co. Portfolio weight Portfolio tum 0 0 Date Monthly Botareturn Dividend 051 Monthly Bolum Caterpillar Merca Monthly portent 31-Dec-18 31-lan-19 DK SW $0.00 $0.00 2-Teb-19 31-Mar-19 -Apr-19 21-May-19 -lun 0.0% 0.0% 0.08 DON DON DO D.ON . 0.0% 0.0% 0.0 . 0.0% 0.0 0.01 0.05 0.0 SO.53 $0.00 $0.00 $0.55 50.00 50.00 $0.55 $0.00 $ $0.00 $0.61 $0.00 $0.00 119 Caterpillar, Inc. Dund Stack SI Prices $ $133.16 SON $137, 50.00 $13.3 50.00 $139.40 50.86 SL19.81 50.00 $1.29 50.00 $ $131.6 $1.03 $11.00 30.00 $126.31 $ $0.00 $12.0 $ $1.03 $144,73 50.00 $147.68 $0.00 $133.35 $ $1.00 $121.24 $0.00 $116.04 50.00 $116.30 $1.03 $129,13 50.00 $126.50 50.00 $ $232. $1.03 $142.31 $ 50.00 $149.15 $ $0.00 $15.05 $ 51.02 $123.59 90.00 $173.87 15% 20% 25W 30 358 40W 45 SON 55% Stock Price ISO 57078 577.29 $75.00 575.36 535.84 580.28 $79. $8.34 581.13 SON 5845 S88.24 583.46 57479 $25.10 $ S78.15 579.51 576.17 575.66 584.65 582.35 575.21 SIO20 . 561.83 31 Aug 19 20-Sep-29 31-Oct-29 30-Nov-19 31 Dec 19 21-lan-20 25-Feb-20 31-Mar-20 20. Apr. 20 3-May-20 -lu-20 31-10-20 31 Aug 20 20-Sep-20 11-Oct-20 30-Nov-20 0.0 O.UK 0.0 30.65 DON DON 0.0 D. 0.0% DOW 0.0% D. 0.0% 0.0 O. 0.0 % 0.00 0.00 65W 30% 75 BUX 85 90 55 BOOK $0.00 50.00 SO 6 $0.00 50.00 $06 $0.00 50.00 0.0 % 0.0 0.0 0.0 G.OS C. .UX DUX Merck & Co. Caterpillar Inc Average monthly retum Porfolio with the two stocks Average monthly retum Average monthly return Standard devi Standard devor Standard deviation Compute the portfolie's standard deviation using the forma: = Varro Vwc+wc+2wW.PATA Portfolio with the two stotis womando M 1. Compute monthly retums, the average and the standard deviation of the monthly returns for the Individual stocks in the portfolio 2. Compute monthly returns, the average and the standard deviation of the monthly returns for the portfolio 3. Trhough trial and error, find the optimal portfolio weights for Caterpillar and Merck that produce the highest Sharpe Ratio. Use 0.18% as the risk free rate. Optimal Portfolio Weights Caterpillar, Inc Merck & Co Portfolio Weights: Sharpe Rates 1 teaserleigheter Cell Caterplein Merck & Co Total 0.0% -The Iconect. The sum of the Toportoghes you must be 100% Merck & Co. Portfolio weight Portfolio tum 0 0 Date Monthly Botareturn Dividend 051 Monthly Bolum Caterpillar Merca Monthly portent 31-Dec-18 31-lan-19 DK SW $0.00 $0.00 2-Teb-19 31-Mar-19 -Apr-19 21-May-19 -lun 0.0% 0.0% 0.08 DON DON DO D.ON . 0.0% 0.0% 0.0 . 0.0% 0.0 0.01 0.05 0.0 SO.53 $0.00 $0.00 $0.55 50.00 50.00 $0.55 $0.00 $ $0.00 $0.61 $0.00 $0.00 119 Caterpillar, Inc. Dund Stack SI Prices $ $133.16 SON $137, 50.00 $13.3 50.00 $139.40 50.86 SL19.81 50.00 $1.29 50.00 $ $131.6 $1.03 $11.00 30.00 $126.31 $ $0.00 $12.0 $ $1.03 $144,73 50.00 $147.68 $0.00 $133.35 $ $1.00 $121.24 $0.00 $116.04 50.00 $116.30 $1.03 $129,13 50.00 $126.50 50.00 $ $232. $1.03 $142.31 $ 50.00 $149.15 $ $0.00 $15.05 $ 51.02 $123.59 90.00 $173.87 15% 20% 25W 30 358 40W 45 SON 55% Stock Price ISO 57078 577.29 $75.00 575.36 535.84 580.28 $79. $8.34 581.13 SON 5845 S88.24 583.46 57479 $25.10 $ S78.15 579.51 576.17 575.66 584.65 582.35 575.21 SIO20 . 561.83 31 Aug 19 20-Sep-29 31-Oct-29 30-Nov-19 31 Dec 19 21-lan-20 25-Feb-20 31-Mar-20 20. Apr. 20 3-May-20 -lu-20 31-10-20 31 Aug 20 20-Sep-20 11-Oct-20 30-Nov-20 0.0 O.UK 0.0 30.65 DON DON 0.0 D. 0.0% DOW 0.0% D. 0.0% 0.0 O. 0.0 % 0.00 0.00 65W 30% 75 BUX 85 90 55 BOOK $0.00 50.00 SO 6 $0.00 50.00 $06 $0.00 50.00 0.0 % 0.0 0.0 0.0 G.OS C. .UX DUX Merck & Co. Caterpillar Inc Average monthly retum Porfolio with the two stocks Average monthly retum Average monthly return Standard devi Standard devor Standard deviation Compute the portfolie's standard deviation using the forma: = Varro Vwc+wc+2wW.PATA Portfolio with the two stotis womando
