90.2K

Verified Solution

Link Copied!

The monthly excess return of 6 stocks are given in the excel file (See the attached excel file). Using the information given to you in the excel, create an optimal portfolio that comprised of a passive portfolio of a market index and an active portfolio that is made up of these 6 stocks. If you make any assumption, please clearly state your assumption and why you made such assumption and its justification. Verify whether or not the assumptions underlying Index Models are met. Also, calculate the beta and the Sharpe ratio of the portfolio. In all of your calculation, please make sure to use the formulas (not just numbers) in your excel file, so that I can evaluate your work. Cells with just number without any formulas won't receive any credit. Please use excel and upload the file to Blackboard.

AutoC DPH Taken - 2 Part 1 - Escal Such Jane Martine 0 File Home DW Formulier DATA RUM View Dwelope Add- Help Sharp Comments csilai - 11 - AA == - We Thai Gel Normal 2 Normal Normal22 Goad Y X Cut Le Format parter Cipherd Per Copy Pk A wyce === BIU. U, $ Muigw3 CHILE 8 Conditional Format Bad Formidling til Neutral Swivity Saw ELL Sort Find & Pas Film Select Fairing Insert Celente Farma Rent Nigar hurch Syter CALE ng Genery 163 X v fa Alpha L L M N P o 5 T U V WA x A B F G 1 Monthly Return 2 Date Market Index WMITGT VZT 1 GM Fard 3 2011-01 0.017757 0.098281 -0.06954 0.007753 -0.05007 -0.01144 -0.05143 4 2011-02 0.007065 -0.074045 -0.00812 0.005396 0.00015 -0.00222 -0.05753 5 2011-03 0.002766 0.007769 -0.04894 0.043279 0.077977 -0.07516 -0.003s 5 2011-04 0.028388 0.055097 -0.0185 0.10737 0.030409 0.03386 0.037250 2011 as 0.015135 0.010804 0.013649 2.0227 0.012939 O.ONCE 0.03575 0.003 2011-06 0.018595 0.037868 0.0631 0.007923 0.00495 0.04578 0.07594 9 2011-07 -0.022909 0.004492 0.097234 0.02941 0.055.0 0.04967 -0.11498 10 2011-09 -0.057672 0.015831 0.009-22 0.024595 0.02686 0.19206 -0.09947 11 2011-09 -0.084873 -0.024253 -0.0509 0.017558 0.001405 -0.16022 -0.1004 12 2011-10 0.113882 0.092771 0.1163.36 0.018378 0.012677 0280871 0.207759 13 2011-11 -0.006.176 0.038334 -0.02205 0.070181 -0.01136 0.1765 -0.09257 14 2011 12 0.003 702 0.020798 0.0280R 0.36345 0.042478 0.04791 0.015094 15 2012-01 0.053877 0.020574 C.CCEZi 0.04905 0.0121 0.194402 0.158722 16 2012-02 0.040388 0.037738 0.12.03 0.011349 0.039522 0.092004 -0.00392 17 2012-03 0.022294 0.042012 0.027271 0.002549 0.020922 -0.01482 0.007074 10 2012-04 -0.007501 -0.008118 -0.00636 0.068617 0.067183 -0.10401 -0.09248 19 2012-05 -0.066231 0.1233-15 0.00396 0.030501 0.037586 -0.03548 -0.05153 2n 2012-05 0.037656 0.058753 0.004335 0.066743 0.043106 -0.11771 -0.09236 21 2012 az 0.0005 0.066856 0.041575 0.026303 0.075015 0.00121 0.0372 22 2012-08 0.025265 0.020146 0.061754 0.04964 0.03466 0.0R2207 0.015324 23 2012-09 0.025229 0.015629 0.01037 O.ULU:43 0.02223 0.054474 0.054974 24 2012-10 -0.015201 0.015431 0.002212 0.01021 0.07192 0.119779 0.135017 25 2012-11 0.004893 -0.041289 -0.00538 -0.01295 -0.0146 0.013602 0.024686 26 2012-12 0.012131 -0.017505 -0.06313 -0.01967 -0.01271 0 113588 0130601 27 2013-01 0.05.1593 0.024700 0.020457 0.01626 0.044887 -0.02617 0.007272 2 2013 2 0.007531 0.011056 0.047371 0.066157 0.031303 0.03426 0.02706 29 2018 23 03 0.034474 0.6630S 0.axcasa O.SSS0S 0.020921 0.022278 0.042022 30 2013-04 0.014443 0.028121 0.020225 0.100224 0.032752 0.108055 0.042020 31 2013-05 0.018866 -0.031209 -0.01012 -0.10092 0.06614 0.099698 0.150785 32 2013-06 -0.015339 -0.004977 -0.00951 0.000066 0.011418 -0.01741 -0.01369 33 2013-07 0.052481 0.016115 0.034508 -0.00705 0.00884 0076654 0.097408 34 2013-08 -0.026114 -0.058OOR -0.1058 -0.04284 -0.04123 -0.0503 -0.04128 35 2013 au 09 0.037277 0.013728 0.010383 0.01508 a.DCOS 0 055258 0.041801 36 2013 10 0.028751 0.020623 0.01156 0.092429 0.012578 0.026145 0.019054 37 2013-11 0.024447 0.US5005 -0.00714 0.01812 0.02785 0.047073 0.00225 39 2013-12 0.025926 -0.022037 -0.01062 -0.00887 -0.00162 0.055054 -0.0968 39 2014-01 -0.090157 -0.051159 -0.10499 -0.01221 -0.09945 -0.1174 -0.02256 10 2014-02 0.045673 -0.000232 0.111259 -0.00866 -0.04222 0.002826 002B243 41 2014-03 0.003069 0.025085 -0.03206 -0.00071 0.09784 -0.04138 0.013145 42 2014 04 0.00146 0.042715 0.020203 0.00672 0.030381 0.001543 0.043069 43 2014 05 0.010918 0.021162 0.07415 0.06282 0.00674 0.0026 0.017697 44 2014 0.027741 0.022344 0.020700 0.02082 -0.0023 0.054215 0.0148402 15 02 tamannna nanen - Stock return into Rady Nitra GRF Partly cloudy RA TOTA HO qx 9:32 PM 1/27/2022 AutoC DPH %. Taken - 2 Part 1 - Escal Such Jane Martine File Home DW Formulier DATA RUM View Dwelope Add- Help Sharp Comments csilai - 11 - AA == - We Thai Gel Normal Y O 0 X Cut Le Format parter Cipherd Normal22 Goad Per Copy Normal 2 Conditional Format Bad Formidling til Pk A wyce U, === $ Muigw3 CHILE 8 Neutral Saw ELL Sort Find & Pas Film Select Fairing Insert Celente Farma Swivity Rent Nigar hurche Syter CALE ng Genery 163 X v fi Alpha L L M N P o 5 T U V WA x A 1 2 Date 45 2014-02 16 2014-08 47 2014-09 48 2014-10 49 2014 11 50 2014 12 51 2015-01 52 2015-02 53 2015-03 54 2015-04 55 2015-08 56 2015 05 57 2015-07 59 2015-O 59 2015-09 60 2015-10 61 2015-11 62 2015-12 63 Alpha 14 Market risk premium B F G Manthly Return Market Index WMITGT VZT 1 GM Fard -0.020724 -0.0200448 0.0281 0.041083 0.019314 -0.06852 -0.00571 0.009871 0.002318 0.016481 -0.0122 -0.018 0.028677 0022614 -0.025229 0.012748 0.043349 0.003312 0.007509 -0.07366 -0.15059 0.020076 -0.002815 -0.01.302 0.016003 0.001503 0.01711 -0.03908 0.02075 0.147365 0.205035 0.dogaco 0.015CCO 0.86425 0.115995 0.006266 0.002916 0.01378 0.025511 0.07561 O.OSC9 0.052946 0.01492 -0.022232 -0.02008 . -0.0205 0.01132 0.00025 0.0658 0.04149 0.055748 -0.012555 0.050606 0.08162 0.049618 0.143577 0.110609 -0.010639 -0.014378 0.068002 -0.01678 -0.05547 0.012933 -0.01244 0.008522 -0.051254 -0.03968 0.04833 0.075141 -0.06527 -0.01197 0.010273 -0.047753 0.017717 0.0103 0.002 0.025856 -0.0397 0.010258 0.044971 0.020123 0.05724 0.028273 0.06330 0.01055 0.011795 0.014503 0.002393 0.015362 0.00903 0.0549 0.0023 O.U60293 0.094215 0.04404 0.01097 0.04403 0.06599 -0.00509 -0.093735 0.001699 0.012225 -0.05434 -0.01868 0.031929 -0.02163 0.07385 -0.117312 -0.01892 0.090019 0.042871 0.152791 0.102032 0.001742 0.027252 -0.05408 -0.0312 0.001075 0.036252 -0.03311 -0.023661 0.0484.35 -0.00018 0.015747 0.020278 -0.05225 -0.01845 0.0150 -0.0100 -0.0050 0.0075 0.0025 0.0120 6% 66 67 GA 69 74 75 76 77 79 90 82 R3 A AS 96 ? Stock return into Rady Nitra GRF Partly cloud, RA TOTA HO qx 9:32 PM 1/27/2022 AutoC DPH Taken - 2 Part 1 - Escal Such Jane Martine 0 File Home DW Formulier DATA RUM View Dwelope Add- Help Sharp Comments csilai - 11 - AA == - We Thai Gel Normal 2 Normal Normal22 Goad Y X Cut Le Format parter Cipherd Per Copy Pk A wyce === BIU. U, $ Muigw3 CHILE 8 Conditional Format Bad Formidling til Neutral Swivity Saw ELL Sort Find & Pas Film Select Fairing Insert Celente Farma Rent Nigar hurch Syter CALE ng Genery 163 X v fa Alpha L L M N P o 5 T U V WA x A B F G 1 Monthly Return 2 Date Market Index WMITGT VZT 1 GM Fard 3 2011-01 0.017757 0.098281 -0.06954 0.007753 -0.05007 -0.01144 -0.05143 4 2011-02 0.007065 -0.074045 -0.00812 0.005396 0.00015 -0.00222 -0.05753 5 2011-03 0.002766 0.007769 -0.04894 0.043279 0.077977 -0.07516 -0.003s 5 2011-04 0.028388 0.055097 -0.0185 0.10737 0.030409 0.03386 0.037250 2011 as 0.015135 0.010804 0.013649 2.0227 0.012939 O.ONCE 0.03575 0.003 2011-06 0.018595 0.037868 0.0631 0.007923 0.00495 0.04578 0.07594 9 2011-07 -0.022909 0.004492 0.097234 0.02941 0.055.0 0.04967 -0.11498 10 2011-09 -0.057672 0.015831 0.009-22 0.024595 0.02686 0.19206 -0.09947 11 2011-09 -0.084873 -0.024253 -0.0509 0.017558 0.001405 -0.16022 -0.1004 12 2011-10 0.113882 0.092771 0.1163.36 0.018378 0.012677 0280871 0.207759 13 2011-11 -0.006.176 0.038334 -0.02205 0.070181 -0.01136 0.1765 -0.09257 14 2011 12 0.003 702 0.020798 0.0280R 0.36345 0.042478 0.04791 0.015094 15 2012-01 0.053877 0.020574 C.CCEZi 0.04905 0.0121 0.194402 0.158722 16 2012-02 0.040388 0.037738 0.12.03 0.011349 0.039522 0.092004 -0.00392 17 2012-03 0.022294 0.042012 0.027271 0.002549 0.020922 -0.01482 0.007074 10 2012-04 -0.007501 -0.008118 -0.00636 0.068617 0.067183 -0.10401 -0.09248 19 2012-05 -0.066231 0.1233-15 0.00396 0.030501 0.037586 -0.03548 -0.05153 2n 2012-05 0.037656 0.058753 0.004335 0.066743 0.043106 -0.11771 -0.09236 21 2012 az 0.0005 0.066856 0.041575 0.026303 0.075015 0.00121 0.0372 22 2012-08 0.025265 0.020146 0.061754 0.04964 0.03466 0.0R2207 0.015324 23 2012-09 0.025229 0.015629 0.01037 O.ULU:43 0.02223 0.054474 0.054974 24 2012-10 -0.015201 0.015431 0.002212 0.01021 0.07192 0.119779 0.135017 25 2012-11 0.004893 -0.041289 -0.00538 -0.01295 -0.0146 0.013602 0.024686 26 2012-12 0.012131 -0.017505 -0.06313 -0.01967 -0.01271 0 113588 0130601 27 2013-01 0.05.1593 0.024700 0.020457 0.01626 0.044887 -0.02617 0.007272 2 2013 2 0.007531 0.011056 0.047371 0.066157 0.031303 0.03426 0.02706 29 2018 23 03 0.034474 0.6630S 0.axcasa O.SSS0S 0.020921 0.022278 0.042022 30 2013-04 0.014443 0.028121 0.020225 0.100224 0.032752 0.108055 0.042020 31 2013-05 0.018866 -0.031209 -0.01012 -0.10092 0.06614 0.099698 0.150785 32 2013-06 -0.015339 -0.004977 -0.00951 0.000066 0.011418 -0.01741 -0.01369 33 2013-07 0.052481 0.016115 0.034508 -0.00705 0.00884 0076654 0.097408 34 2013-08 -0.026114 -0.058OOR -0.1058 -0.04284 -0.04123 -0.0503 -0.04128 35 2013 au 09 0.037277 0.013728 0.010383 0.01508 a.DCOS 0 055258 0.041801 36 2013 10 0.028751 0.020623 0.01156 0.092429 0.012578 0.026145 0.019054 37 2013-11 0.024447 0.US5005 -0.00714 0.01812 0.02785 0.047073 0.00225 39 2013-12 0.025926 -0.022037 -0.01062 -0.00887 -0.00162 0.055054 -0.0968 39 2014-01 -0.090157 -0.051159 -0.10499 -0.01221 -0.09945 -0.1174 -0.02256 10 2014-02 0.045673 -0.000232 0.111259 -0.00866 -0.04222 0.002826 002B243 41 2014-03 0.003069 0.025085 -0.03206 -0.00071 0.09784 -0.04138 0.013145 42 2014 04 0.00146 0.042715 0.020203 0.00672 0.030381 0.001543 0.043069 43 2014 05 0.010918 0.021162 0.07415 0.06282 0.00674 0.0026 0.017697 44 2014 0.027741 0.022344 0.020700 0.02082 -0.0023 0.054215 0.0148402 15 02 tamannna nanen - Stock return into Rady Nitra GRF Partly cloudy RA TOTA HO qx 9:32 PM 1/27/2022 AutoC DPH %. Taken - 2 Part 1 - Escal Such Jane Martine File Home DW Formulier DATA RUM View Dwelope Add- Help Sharp Comments csilai - 11 - AA == - We Thai Gel Normal Y O 0 X Cut Le Format parter Cipherd Normal22 Goad Per Copy Normal 2 Conditional Format Bad Formidling til Pk A wyce U, === $ Muigw3 CHILE 8 Neutral Saw ELL Sort Find & Pas Film Select Fairing Insert Celente Farma Swivity Rent Nigar hurche Syter CALE ng Genery 163 X v fi Alpha L L M N P o 5 T U V WA x A 1 2 Date 45 2014-02 16 2014-08 47 2014-09 48 2014-10 49 2014 11 50 2014 12 51 2015-01 52 2015-02 53 2015-03 54 2015-04 55 2015-08 56 2015 05 57 2015-07 59 2015-O 59 2015-09 60 2015-10 61 2015-11 62 2015-12 63 Alpha 14 Market risk premium B F G Manthly Return Market Index WMITGT VZT 1 GM Fard -0.020724 -0.0200448 0.0281 0.041083 0.019314 -0.06852 -0.00571 0.009871 0.002318 0.016481 -0.0122 -0.018 0.028677 0022614 -0.025229 0.012748 0.043349 0.003312 0.007509 -0.07366 -0.15059 0.020076 -0.002815 -0.01.302 0.016003 0.001503 0.01711 -0.03908 0.02075 0.147365 0.205035 0.dogaco 0.015CCO 0.86425 0.115995 0.006266 0.002916 0.01378 0.025511 0.07561 O.OSC9 0.052946 0.01492 -0.022232 -0.02008 . -0.0205 0.01132 0.00025 0.0658 0.04149 0.055748 -0.012555 0.050606 0.08162 0.049618 0.143577 0.110609 -0.010639 -0.014378 0.068002 -0.01678 -0.05547 0.012933 -0.01244 0.008522 -0.051254 -0.03968 0.04833 0.075141 -0.06527 -0.01197 0.010273 -0.047753 0.017717 0.0103 0.002 0.025856 -0.0397 0.010258 0.044971 0.020123 0.05724 0.028273 0.06330 0.01055 0.011795 0.014503 0.002393 0.015362 0.00903 0.0549 0.0023 O.U60293 0.094215 0.04404 0.01097 0.04403 0.06599 -0.00509 -0.093735 0.001699 0.012225 -0.05434 -0.01868 0.031929 -0.02163 0.07385 -0.117312 -0.01892 0.090019 0.042871 0.152791 0.102032 0.001742 0.027252 -0.05408 -0.0312 0.001075 0.036252 -0.03311 -0.023661 0.0484.35 -0.00018 0.015747 0.020278 -0.05225 -0.01845 0.0150 -0.0100 -0.0050 0.0075 0.0025 0.0120 6% 66 67 GA 69 74 75 76 77 79 90 82 R3 A AS 96 ? Stock return into Rady Nitra GRF Partly cloud, RA TOTA HO qx 9:32 PM 1/27/2022

Answer & Explanation Solved by verified expert

See Answer