Question
You are hired as an investment consultant by a hedge fund that focuses on the following five US stocks: Exxon Mobile (ticker XOM), Nike (ticker
You are hired as an investment consultant by a hedge fund that focuses on the following five US stocks: Exxon Mobile (ticker XOM), Nike (ticker NKE), Amazon (ticker AMZN), Goldman Sachs (ticker GS), and Visa (ticker V). Your analysis is based on implementing the Markowitz approach and monthly returns of the five stocks over the sample period from January 2010 to December 2022, as contained in the homework spreadsheet.
There are two individual investors, Tom and Amy who are interested in investing in your fund. Both investors utility is represented by: = () 1/2 ^2. Tom has a risk aversion coefficient () of 8 and Amy has a risk aversion coefficient of 12. Suppose each investor has $2 million USD to invest, calculate the portfolio allocation (in dollar amount) to the five stocks and risk-free asset you would recommend to the two investors to maximise their utilities, and summarise the expected returns, standard deviations, and Sharpe ratios of these recommended portfolios. Are these portfolios still attainable if investors are not allowed to take short positions. Answer is excel is appreciated, Thanks!
Date XOM NKE AMZN GS V 1/29/2010 -0.0551401 -0.0351143 -0.0677223 -0.119166 -0.0620855 2/26/2010 0.01536551 0.06039213 -0.0558967 0.05365791 0.04114348 3/31/2010 0.03046159 0.09127221 0.1467061 0.09133354 0.06742495 4/30/2010 0.01179446 0.03278917 0.00979599 -0.149036 -0.0087883 5/28/2010 -0.1013723 -0.0465025 -0.0849016 -0.0040634 -0.1955558 6/30/2010 -0.0560701 -0.0630008 -0.1291248 -0.0900457 -0.0235992 7/30/2010 0.04573332 0.09015539 0.07898588 0.1489297 0.03674909 8/31/2010 -0.0021783 -0.0494297 0.05886846 -0.089776 -0.0578731 9/30/2010 0.0453392 0.14871427 0.25819111 0.05586803 0.0765439 10/29/2010 0.07606404 0.01622165 0.05201833 0.11446952 0.0525182 11/30/2010 0.05278989 0.05758834 0.06155056 -0.0287967 -0.0532242 12/31/2010 0.05117891 -0.0046441 0.02622581 0.07698222 -0.0469872 1/31/2011 0.10339165 -0.0344181 -0.0575556 -0.0269981 -0.0075305 2/28/2011 0.06556766 0.07941313 0.02151612 0.003117 0.04795998 3/31/2011 -0.0163685 -0.1462429 0.03947147 -0.0316277 0.00780287 4/29/2011 0.04576258 0.0874505 0.0870482 -0.0478563 0.06112469 5/31/2011 -0.0459195 0.0258746 0.00449418 -0.0657572 0.03955447 6/30/2011 -0.025039 0.06915342 0.03965633 -0.0542884 0.03947699 7/29/2011 -0.0195379 0.00188929 0.08817057 0.01412582 0.01519106 8/31/2011 -0.0664244 -0.0388242 -0.0327611 -0.1363266 0.02910915 9/30/2011 -0.0187787 -0.0095788 0.00464619 -0.1864567 -0.0245789 10/31/2011 0.07517554 0.12676875 -0.0125792 0.15864621 0.08796081 11/30/2011 0.03611226 -0.0017644 -0.0993865 -0.1217709 0.04214024 12/30/2011 0.05370462 0.00571847 -0.0997971 -0.0566451 0.04702483 1/31/2012 -0.012034 0.0790702 0.12328131 0.23266616 -0.0087659 2/29/2012 0.0385718 0.0377921 -0.0758589 0.0360635 0.15848573 3/30/2012 0.002659 0.00815423 0.12699644 0.08016331 0.01400702 4/30/2012 -0.0044968 0.0316304 0.14512864 -0.0741336 0.04220342 5/31/2012 -0.0826963 -0.0297667 -0.0818887 -0.1649154 -0.0614735 6/29/2012 0.08826151 -0.1885746 0.07251891 0.00167193 0.07317708 7/31/2012 0.0149585 0.06345408 0.02167724 0.05257668 0.04400235 8/31/2012 0.01174444 0.04681309 0.06416631 0.05232903 -0.0046487 9/28/2012 0.04753716 -0.0251643 0.02436864 0.07529322 0.04701753 10/31/2012 -0.0030618 -0.0371932 -0.0842518 0.07661857 0.03336309 11/30/2012 -0.0269825 0.06675428 0.08225622 -0.0334995 0.08129153 12/31/2012 -0.0180394 0.06298721 -0.0046816 0.08294419 0.01249078 1/31/2013 0.03951471 0.04748064 0.05831708 0.15914083 0.04176014 2/28/2013 0.00166724 0.01147086 -0.0046328 0.01623153 0.00671266 3/28/2013 0.00625346 0.08354755 0.0084005 -0.0174279 0.07060008 4/30/2013 -0.0124293 0.07778343 -0.0475815 -0.0073394 -0.0081252 5/31/2013 0.02371057 -0.0272012 0.06063597 0.11302796 0.05942059 6/28/2013 -0.0013264 0.03276031 0.03153785 -0.0668189 0.02587853 7/31/2013 0.03763145 -0.0119347 0.08473477 0.08449586 -0.0314091 8/30/2013 -0.0635733 0.00174828 -0.0671934 -0.0694994 -0.0127676 9/30/2013 -0.01285 0.15631963 0.11267707 0.03996583 0.09563128 10/31/2013 0.04160858 0.04295158 0.16437431 0.01674985 0.029147 11/29/2013 0.05010043 0.04461453 0.0812845 0.05364915 0.03655875 12/31/2013 0.08258444 -0.0032853 0.01313453 0.04924821 0.09446567 1/31/2014 -0.089328 -0.0736267 -0.1005542 -0.0741284 -0.0325579 2/28/2014 0.05143221 0.07810576 0.00950683 0.01754815 0.05064295 3/31/2014 0.01464635 -0.056705 -0.0710716 -0.0156203 -0.0446136 4/30/2014 0.04842346 -0.0123207 -0.0958334 -0.0245957 -0.0613824 5/30/2014 -0.01162 0.05757378 0.02768547 0.00337874 0.06228716 6/30/2014 0.00149211 0.00832141 0.03912978 0.04774423 -0.0191779 7/31/2014 -0.0172825 -0.0054159 -0.0363015 0.03242948 0.0014237 8/29/2014 0.0122296 0.02152218 0.08322956 0.0392781 0.00905175 9/30/2014 -0.0543937 0.13558234 -0.0489618 0.02490094 0.00399958 10/31/2014 0.02828279 0.04226462 -0.052661 0.03497302 0.13150863 11/28/2014 -0.0566642 0.06797891 0.10862314 -0.0051582 0.07140791 12/31/2014 0.0210956 -0.0288045 -0.0835401 0.02876704 0.01553124 1/30/2015 -0.0544078 -0.0405616 0.14235538 -0.1105092 -0.0278032 2/27/2015 0.02070468 0.05582659 0.07229291 0.10428623 0.06621943 3/31/2015 -0.0399819 0.03305189 -0.0212016 -0.0095895 -0.0356418 4/30/2015 0.02788239 -0.014851 0.13351248 0.04495396 0.00978443 5/29/2015 -0.0164817 0.03146501 0.01766327 0.05304961 0.04163508 6/30/2015 -0.0234742 0.06245695 0.01132257 0.01260968 -0.0222772 7/31/2015 -0.0479567 0.06665437 0.23511261 -0.0178169 0.12196567 8/31/2015 -0.0409039 -0.0301163 -0.0433834 -0.0771444 -0.0520307 9/30/2015 -0.0118288 0.10290829 -0.0019497 -0.0786851 -0.0230014 10/30/2015 0.11284465 0.06554442 0.22272365 0.07907462 0.11369506 11/30/2015 -0.00423 0.0095398 0.06215044 0.01690669 0.02023718 12/31/2015 -0.0454324 -0.0526157 0.01668175 -0.0515209 -0.0184787 1/29/2016 -0.0012829 -0.00784 -0.1315155 -0.1035898 -0.0394585 2/29/2016 0.03892104 -0.0067731 -0.0587393 -0.0704382 -0.0263122 3/31/2016 0.04291946 0.00064947 0.07442264 0.04982276 0.05649957 4/29/2016 0.05754283 -0.0411583 0.11109428 0.04541983 0.00993717 5/31/2016 0.01549768 -0.063115 0.09581702 -0.0242521 0.02382191 6/30/2016 0.05302181 0.0025353 -0.0099199 -0.0683471 -0.0604257 7/29/2016 -0.0510988 0.00543477 0.06035326 0.06885177 0.05231232 8/31/2016 -0.0119168 0.03855855 0.01363975 0.07115427 0.03830875 9/30/2016 0.0016066 -0.0837959 0.08860318 -0.04833 0.02224963 10/31/2016 -0.0453712 -0.0469136 -0.0567173 0.10522726 -0.0022974 11/30/2016 0.05676913 -0.0021921 -0.0496949 0.23395415 -0.0609017 12/30/2016 0.03390606 0.01877376 -0.0009326 0.09193308 0.00905325 1/31/2017 -0.0705739 0.04072398 0.09816366 -0.0423052 0.06011283 2/28/2017 -0.0216951 0.08052927 0.02618157 0.08455429 0.06522795 3/31/2017 0.00848503 -0.0218684 0.0491101 -0.0739337 0.0105754 4/28/2017 -0.0043897 -0.005742 0.04337087 -0.0257705 0.0264431 5/31/2017 -0.004654 -0.0436744 0.07527649 -0.052681 0.0457685 6/30/2017 0.00285718 0.11681446 -0.026764 0.05036448 -0.0152263 7/31/2017 -0.008547 0.00084744 0.02043391 0.01545745 0.0616336 8/31/2017 -0.0367316 -0.1026249 -0.0072689 -0.0037278 0.04143229 9/29/2017 0.07402072 -0.0181784 -0.0196308 0.0601144 0.01661516 10/31/2017 0.01671133 0.06055937 0.1497166 0.02230277 0.04503996 11/30/2017 0.00851833 0.09874516 0.06466232 0.02437316 0.02550457 12/29/2017 0.00420217 0.03856341 -0.0061866 0.02875139 0.01270095 1/31/2018 0.04375901 0.09064751 0.24063897 0.05153878 0.08954576 2/28/2018 -0.1235968 -0.0174436 0.04242909 -0.0157155 -0.0086936 3/29/2018 -0.0149194 -0.0058182 -0.0430494 -0.0421024 -0.027005 4/30/2018 0.0420855 0.02934974 0.08207473 -0.0537203 0.06069215 5/31/2018 0.05543406 0.04986114 0.04053941 -0.0488818 0.03191996 6/29/2018 0.01834079 0.11253478 0.04306529 -0.023508 0.01323436 7/31/2018 -0.0147468 -0.034764 0.0456759 0.07643825 0.03238964 8/31/2018 -0.0063796 0.07138205 0.13236454 0.00496991 0.07576418 9/28/2018 0.06049643 0.03065699 -0.0048243 -0.0570623 0.02178499 10/31/2018 -0.0628087 -0.1142587 -0.2021917 0.0050392 -0.081551 11/30/2018 0.00803212 0.00399789 0.05767175 -0.1503305 0.02981502 12/31/2018 -0.1422641 -0.0130458 -0.1113498 -0.1239708 -0.0689436 1/31/2019 0.07464432 0.10439706 0.14431714 0.18533368 0.02326809 2/28/2019 0.08965611 0.0470201 -0.045906 -0.0025756 0.09895564 3/29/2019 0.02239661 -0.0151639 0.08593576 -0.0239451 0.0544829 4/30/2019 -0.0064357 0.0429878 0.08185878 0.07255583 0.05275619 5/31/2019 -0.1076233 -0.1192076 -0.0786133 -0.1096542 -0.0173325 6/28/2019 0.08280346 0.08828101 0.06679177 0.12115733 0.07574537 7/31/2019 -0.0296228 0.02477667 -0.0141791 0.0759042 0.02564101 8/30/2019 -0.0673749 -0.0152272 -0.0484738 -0.0680053 0.01724723 9/30/2019 0.03110393 0.11147927 -0.0227328 0.01628165 -0.0487226 10/31/2019 -0.0430534 -0.0465289 0.02347472 0.02967722 0.0398233 11/29/2019 0.0211632 0.04673361 0.0135873 0.0432093 0.03326621 12/31/2019 0.02421843 0.08364531 0.02612168 0.03876208 0.01837298 1/31/2020 -0.1097736 -0.0494521 0.08706382 0.03401038 0.05891436 2/28/2020 -0.1579202 -0.0693147 -0.0622137 -0.1502839 -0.0849877 3/31/2020 -0.2618584 -0.0742895 0.03502056 -0.2300145 -0.1135563 4/30/2020 0.22386093 0.05366211 0.26890016 0.18649332 0.10923539 5/29/2020 -0.0027975 0.13357423 -0.0127849 0.07807223 0.09411372 6/30/2020 -0.0164944 -0.0053763 0.12956674 0.00575088 -0.0106024 7/31/2020 -0.059034 -0.0044875 0.14711356 0.00172053 -0.0143397 8/31/2020 -0.0301807 0.14880647 0.09046097 0.04122039 0.11496855 9/30/2020 -0.1404606 0.12199483 -0.0875785 -0.0190364 -0.0567008 10/30/2020 -0.0498107 -0.0434921 -0.0357541 -0.0593621 -0.0913137 11/30/2020 0.19558561 0.12175212 0.04343993 0.21974191 0.15937482 12/31/2020 0.08103856 0.05230144 0.02805832 0.14910221 0.03983831 1/29/2021 0.08782142 -0.0557009 -0.015576 0.02828874 -0.1164906 2/26/2021 0.23193574 0.01096641 -0.0353284 0.1781539 0.10069858 3/31/2021 0.0268531 -0.0140228 0.00037186 0.02745082 -0.0031075 4/30/2021 0.02525523 -0.0020318 0.12066263 0.06559636 0.10310302 5/28/2021 0.03494055 0.03102859 -0.0704702 0.07122966 -0.0254324 6/30/2021 0.08069219 0.13212661 0.06735498 0.02018711 0.02868458 7/30/2021 -0.0873494 0.08427723 -0.0327223 -0.012252 0.05375927 8/31/2021 -0.0378669 -0.0148946 0.04303413 0.10838136 -0.0688745 9/30/2021 0.07887013 -0.1184291 -0.0535181 -0.0858021 -0.0277172 10/29/2021 0.09605579 0.15189698 0.02660238 0.09343176 -0.0492929 11/30/2021 -0.0581666 0.01165648 0.03992378 -0.0782872 -0.0832271 12/31/2021 0.02256013 -0.0133834 -0.0492519 0.00934407 0.11838779 1/31/2022 0.24137932 -0.1115977 -0.10283 -0.0728532 0.04365277 2/28/2022 0.0439705 -0.0778011 0.02667252 -0.0377523 -0.0427776 3/31/2022 0.05317519 -0.0123398 0.06143731 -0.0269273 0.02614293 4/29/2022 0.03220734 -0.0732759 -0.2375251 -0.0745532 -0.0389593 5/31/2022 0.13642229 -0.0469126 -0.0327643 0.07646737 -0.0027448 6/30/2022 -0.1079167 -0.1375263 -0.1164592 -0.0912652 -0.0720177 7/29/2022 0.13183093 0.12446185 0.27059597 0.12244976 0.07730205 8/31/2022 -0.0047457 -0.0737035 -0.060615 0.00533909 -0.0614068 9/30/2022 -0.0866199 -0.2162987 -0.1086219 -0.1190971 -0.1059837 10/31/2022 0.26915589 0.1150144 -0.0934513 0.17560151 0.1661132 11/30/2022 0.01299521 0.18353477 -0.0575947 0.12812395 0.04967173 12/30/2022 -0.0093407 0.06983316 -0.1298943 -0.11076 -0.0425807
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started