Find the appropriate weights for the optimal complete portfolio, which is formed by allocating your funds between optimal risky portfolio and the risk-free asset. Also,
Find the appropriate weights for the "optimal complete portfolio", which is formed by allocating your funds between optimal risky portfolio and the risk-free asset. Also, calculate the portfolio expected returns, standard deviation, Sharpe ratio and investor's utility.
Cells G28-G35. ( show formulas)
Nm Average Monthly Return Monthly Standard Deviation H. SBUX 1.69% 5.73% SBUX 20.32% 19.86% 18.72% 0.3482 TXN 2.02% 5.86% TXN 24.26% 20.30% 22.66% Annualized Return Annualized Standard Deviation Annualized Risk Premium Correlation Coefficient Variance-Covariance SBUX TXN SBUX 0.0395 0.0140 TXN 0.0140 0.0412 Risk-free rate 1.60% Investor's Risk aversion coefficient A = 3 A B C D E Adjusted Closing Monthly returns Date SBUX TXN sbux txn 3 7/1/2016 54.504204 64.170906 3.14% 0.23% 4 8/1/2016 52.795383 64.320641 -3.38% 0.92% 5 9/1/2016 51.010067 64.912605 -1.98% 0.95% 6 10/1/2016 50.001926 65.532341 9.23% 4.35% 7 11/1/2016 54.618652 68.381157 -3.78% -0.58% 8 12/1/2016 52.552597 67.987015 -0.54% 3.52% 9 1/1/2017 52.268635 70.381531 2.99% 2.07% 10 2/1/2017 53.830444 71.84095 3.14% 5.14% 11 3/1/2017 55.51825 75.535217 2.86% -1.71% 12 4/1/2017 57.106121 74.24128 5.91% 4.83% 13 5/1/2017 60.481525 77.824585 -7.95% -6.74% 14 6/1/2017 55.670574 72.579041 -7.43% 5.78% 15 7/1/2017 51.536572 76.777351 1.63% 2.39% 16 8/1/2017 52.37674 78.61219 -1.65% 8.23% 17 9/1/2017 51.510277 85.085686 2.10% 7.86% 18 10/1/2017 52.593998 91.777489 5.43% 1.26% 19 11/1/2017 55.451946 92.937988 -0.15% 7.35% 20 12/1/2017 55.369698 99.768166 -1.08% 5.01% 21 1/1/2018 54.771942 104.76421 0.51% -0.66% 22 2/1/2018 55.05154 104.07607 1.93% -4.12% 23 3/1/2018 56.115925 99.791992 -0.55% -2.37% 24 4/1/2018 55.805733 97.429031 -1.56% 10.33% 25 5/1/2018 54.933308 107.49564 -13.35% -0.89% 26 6/1/2018 47.600578 106.5387 7.25% 0.97% 27 7/1/2018 51.050041 107.5727 2.02% 1.53% 28 8/1/2018 52.082928 109.21771 7.08% -4.55% 29 9/1/2018 55.771816 104.25239 2.52% -13.48% 30 10/1/2018 57.174942 90.201782 14.50% 8.49% 31 11/1/2018 65.466148 97.858589 -2.96% -5.36% 32 12/1/2018 63.529015 92.615295 5.81% 6.54% 33 1/1/2019 67.218437 98.672043 3.11% 5.87% 34 2/1/2019 69.309769 104.46399 6.36% 0.27% 35 3/1/2019 73.7173 104.75037 4.49% 11.09% 36 4/1/2019 77.029335 116.36407 -2.09% -11.47% 371 5/1/2019 75.422897 103.01227 10.73% 10.74% 38 6/1/2019 83.513466 114.08011 12.95% 8.93% 39 7/1/2019 94.332458 124.26939 Optimal Risky Portfolio Weight - SBUX Weight - TXN 41.83% 58.17% Portfolio Expected Return Portfolio Standard Deviation Portfolio Sharpe ratio 22.61% 16.64% 1.2627 22.61% 16.64% 1.2627 Optimal Complete Portfolio Weight - Risky Portfolio Weight - Riskfree Portfolio Portfolio Expected Return Portfolio Standard Deviation Portfolio Sharpe ratio Investor Utility Nm Average Monthly Return Monthly Standard Deviation H. SBUX 1.69% 5.73% SBUX 20.32% 19.86% 18.72% 0.3482 TXN 2.02% 5.86% TXN 24.26% 20.30% 22.66% Annualized Return Annualized Standard Deviation Annualized Risk Premium Correlation Coefficient Variance-Covariance SBUX TXN SBUX 0.0395 0.0140 TXN 0.0140 0.0412 Risk-free rate 1.60% Investor's Risk aversion coefficient A = 3 A B C D E Adjusted Closing Monthly returns Date SBUX TXN sbux txn 3 7/1/2016 54.504204 64.170906 3.14% 0.23% 4 8/1/2016 52.795383 64.320641 -3.38% 0.92% 5 9/1/2016 51.010067 64.912605 -1.98% 0.95% 6 10/1/2016 50.001926 65.532341 9.23% 4.35% 7 11/1/2016 54.618652 68.381157 -3.78% -0.58% 8 12/1/2016 52.552597 67.987015 -0.54% 3.52% 9 1/1/2017 52.268635 70.381531 2.99% 2.07% 10 2/1/2017 53.830444 71.84095 3.14% 5.14% 11 3/1/2017 55.51825 75.535217 2.86% -1.71% 12 4/1/2017 57.106121 74.24128 5.91% 4.83% 13 5/1/2017 60.481525 77.824585 -7.95% -6.74% 14 6/1/2017 55.670574 72.579041 -7.43% 5.78% 15 7/1/2017 51.536572 76.777351 1.63% 2.39% 16 8/1/2017 52.37674 78.61219 -1.65% 8.23% 17 9/1/2017 51.510277 85.085686 2.10% 7.86% 18 10/1/2017 52.593998 91.777489 5.43% 1.26% 19 11/1/2017 55.451946 92.937988 -0.15% 7.35% 20 12/1/2017 55.369698 99.768166 -1.08% 5.01% 21 1/1/2018 54.771942 104.76421 0.51% -0.66% 22 2/1/2018 55.05154 104.07607 1.93% -4.12% 23 3/1/2018 56.115925 99.791992 -0.55% -2.37% 24 4/1/2018 55.805733 97.429031 -1.56% 10.33% 25 5/1/2018 54.933308 107.49564 -13.35% -0.89% 26 6/1/2018 47.600578 106.5387 7.25% 0.97% 27 7/1/2018 51.050041 107.5727 2.02% 1.53% 28 8/1/2018 52.082928 109.21771 7.08% -4.55% 29 9/1/2018 55.771816 104.25239 2.52% -13.48% 30 10/1/2018 57.174942 90.201782 14.50% 8.49% 31 11/1/2018 65.466148 97.858589 -2.96% -5.36% 32 12/1/2018 63.529015 92.615295 5.81% 6.54% 33 1/1/2019 67.218437 98.672043 3.11% 5.87% 34 2/1/2019 69.309769 104.46399 6.36% 0.27% 35 3/1/2019 73.7173 104.75037 4.49% 11.09% 36 4/1/2019 77.029335 116.36407 -2.09% -11.47% 371 5/1/2019 75.422897 103.01227 10.73% 10.74% 38 6/1/2019 83.513466 114.08011 12.95% 8.93% 39 7/1/2019 94.332458 124.26939 Optimal Risky Portfolio Weight - SBUX Weight - TXN 41.83% 58.17% Portfolio Expected Return Portfolio Standard Deviation Portfolio Sharpe ratio 22.61% 16.64% 1.2627 22.61% 16.64% 1.2627 Optimal Complete Portfolio Weight - Risky Portfolio Weight - Riskfree Portfolio Portfolio Expected Return Portfolio Standard Deviation Portfolio Sharpe ratio Investor Utility
