Month P a P b P c P d P e P market 0 61 96 53 36 62 74 48 60 434 69 1,756 54 1 64 17 52 09 64 12 52 46 461 60 1,805 81 2 65 50 55 19 68 83 56 08 477 94 1,848 36 3 61 43 60 53 71 18 53 06 495 06 1,782 59 4 65 57 62 37 73 98 57 54 538 44 1,859 45 5 63 25 57 09 75 81 58 89 537 10 1,872 34 6 63 92 51 65 80 41 57 21 533 89 1,883 95 7 64 50 52 63 78 45 61 84 532 50 1,923 57 8 65 09 58 08 83 39 59 51 536 24 1,960 23 9 65 38 54 25 77 32 60 30 517 03 1,930 67 10 75 61 59 09 84 23 62 96 538 84 2,003 37 11 74 19 57 53 76 48 63 07 509 66 1,972 29 12 79 26 63 99 78 31 68 00 553 52 2,018 05 13 80 79 59 87 78 28 72 64 577 71 2,067 56 14 85 31 59 31 71 22 76 58 619 11 2,058 90 15 85 27 56 45 62 23 78 05 596 96 1,994 99 16 93 71 69 38 65 04 82 88 642 68 2,104 50 17 92 78 66 81 62 79 82 36 682 16 2,067 89 18 87 81 72 82 68 16 79 23 672 66 2,085 51 19 91 46 72 75 67 49 81 98 673 62 2,107 39 20 91 22 69 63 67 09 83 98 666 90 2,063 11 21 96 57 73 30 62 20 90 06 700 94 2,103 84 22 96 10 69 36 61 02 82 25 715 99 1,972 18 23 95 30 69 43 52 18 77 50 723 83 1,920 03 24 102 55 77 71 58 27 79 34 784 41 2,079 36 25 111 04 79 69 58 65 75 83 783 77 2,080 41 26 109 69 78 40 54 86 78 80 741 91 2,043 94 27 104 77 68 06 50 24 77 85 767 39 1,940 24 28 103 40 67 75 55 34 78 67 774 57 1,932 23 29 111 15 73 83 62 56 83 98 796 69 2,059 74 30 112 15 75 80 63 53 81 36 765 23 2,065 30 31 110 67 83 71 59 84 78 41 762 20 2,096 95 32 107 51 79 41 62 57 77 83 793 84 2,098 86 33 116 39 81 80 68 30 75 38 813 97 2,173 60 34 112 93 79 42 68 29 76 26 741 80 2,170 95 35 108 90 71 33 73 97 72 66 768 34 2,168 27 36 103 26 75 16 69 55 68 67 742 16 2,126 15 37 109 51 72 00 80 33 63 09 783 18 2,198 81 38 114 07 68 46 77 96 64 75 789 79 2,238 83 39 117 05 79 10 80 42 64 67 724 98 2,278 87 40 123 29 81 35 81 93 66 52 736 55 2,363 64 41 124 92 82 49 78 62 64 80 723 05 2,362 72 42 133 62 86 12 86 68 68 06 692 19 2,384 20 43 131 40 89 64 90 11 63 83 605 92 2,411 80 44 132 06 86 60 91 84 66 85 570 46 2,423 41 45 128 79 90 80 97 39 66 41 539 82 2,470 30 46 129 02 95 49 101 14 64 67 528 44 2,471 65 47 141 64 93 42 107 35 68 00 595 11 2,519 36 48 143 57 102 34 116 90 57 31 589 50 2,575 26 49 155 72 104 32 122 23 64 48 686 76 2,647 58 50 164 96 102 23 136 46 61 03 711 37 2,673 61 51 174 86 113 91 140 96 66 24 765 44 2,823 81 52 158 65 116 25 134 52 57 36 664 72 2,713 83 53 155 14 116 30 128 22 52 69 648 69 2,640 87 54 161 77 120 99 125 59 59 14 624 52 2,648 05 55 163 30 129 33 132 82 54 10 649 32 2,705 27 56 171 74 136 40 118 62 54 92 670 93 2,718 37 57 173 87 137 15 125 73 55 35 705 53 2,816 29 58 176 73 152 68 122 15 64 70 766 88 2,901 52 59 183 28 159 03 134 15 67 69 775 70 2,913 98 60 155 61 137 24 106 73 62 25 733 47 2,711 74 61 159 54 142 76 120 11 69 44 809 07 2,760 17 62 152 93 136 97 112 50 56 73 838 34 2,506 85 63 163 35 151 97 117 89 56 76 847 34 2,704 10 64 164 79 163 65 122 37 50 45 938 97 2,784 49 65 170 79 158 37 120 72 47 05 1024 12 2,834 40 66 182 65 165 35 124 23 47 44 1028 31 2,945 83 67 170 24 151 41 107 40 46 13 1027 11 2,752 06 68 186 48 151 73 122 17 48 00 1099 47 2,941 76 69 192 96 154 50 118 03 49 21 1123 04 2,980 38 70 205 80 156 07 107 49 54 14 1101 69 2,926 46 71 209 51 148 44 114 10 56 05 1084 62 2,976 74 72 213 11 156 49 124 48 59 00 1144 38 3,037 56 73 200 33 162 89 131 76 67 40 1177 92 3,140 98 74 198 40 162 64 134 45 66 52 1191 31 3,230 78 75 208 55 182 31 119 58 60 73 1057 96 3,225 52 76 199 17 170 40 113 90 53 35 1032 51 2,954 22 77 170 71 143 98 106 39 53 49 846 00 2,584 59 78 202 34 161 95 106 70 55 49 1020 32 2,912 43 79 228 71 174 79 111 15 59 61 1147 86 3,044 31 80 230 57 187 33 117 04 59 06 1128 12 3,100 29 81 245 82 194 85 122 94 57 21 1207 42 3,271 12 82 263 92 272 65 132 65 56 91 1196 31 3,500 31 83 257 14 251 32 139 03 53 50 1177 64 3,363 00 84 248 25 232 27 146 39 51 39 1128 98 3,269 96 85 258 21 245 80 162 80 62 63 1137 65 3,621 63 86 247 24 222 53 170 71 63 10 1185 44 3,756 07 87 253 45 225 56 171 47 66 20 1118 37 3,714 24 88 241 77 216 50 203 54 63 37 1159 92 3,811 15 89 285 67 211 87 218 61 69 97 1404 30 3,972 89 90 304 81 230 32 215 07 71 06 1464 12 4,181 17 91 300 33 238 10 228 33 80 92 1406 60 4,204 11 92 300 31 244 27 206 12 78 12 1492 22 4,297 50 93 310 69 241 93 195 81 77 10 1623 57 4,395 26 94 308 79 265 27 200 79 81 37 1549 15 4,522 68 95 310 76 271 22 182 79 79 93 1697 99 4,307 54 96 353 71 299 69 194 25 84 09 1784 84 4,605 38 97 381 18 284 96 185 12 84 37 1817 07 4,567 00 98 394 88 254 13 197 94 97 73 2096 39 4,766 18 99 350 62 232 63 192 98 100 90 1986 35 4,515 55 100 301 75 210 53 180 47 98 72 1863 39 4,373 94 101 285 99 212 32 214 37 96 39 2044 58 4,530 41 102 288 74 175 94 202 56 91 55 1955 47 4,131 93 103 291 00 160 24 208 66 92 62 2059 65 4,132 15 104 263 63 165 04 172 81 88 71 2149 12 3,785 38 105 291 09 184 02 191 65 91 60 2137 39 4,130 29 106 278 98 156 12 179 80 94 51 2119 21 3,955 00 107 268 65 143 84 159 72 91 83 2141 93 3,585 62 108 288 30 162 59 210 71 91 19 2532 88 3,871 98 109 315 43 160 25 231 67 98 69 2579 00 4,080 11 110 309 37 132 59 234 75 90 27 2466 18 3,839 50 111 317 51 167 97 247 23 85 46 2438 85 4,076 60 112 290 45 163 61 235 86 81 49 2486 54 3,970 15 113 289 06 199 78 225 32 72 48 2458 15 4,109 31 114 296 50 198 37 215 43 71 51 2663 31 4,169 48 115 279 64 223 38 203 68 66 90 2386 84 4,179 83 116 308 67 211 26 243 58 67 98 2493 36 4,450 38 117 331 72 225 01 262 51 73 45 2481 72 4,588 96 118 328 20 221 46 279 68 64 62 2531 33 4,507 66 119 302 16 202 78 271 60 69 23 2539 99 4,288 05 120 283 31 204 22 248 27 68 38 2416 54 4,186 77 4 (30 Points) Please use the real world data available on MS Excel file attached to answer this question a (15 Points) Please calculate the alpha and beta of all five stocks using the regression model and the industry standard discussed during our lecture b (7 Points) Please plot the graph of stock E's excess returns, beta, alpha, and the epsilons (unexplained variation in the excess stock return) To have a better idea, you can check the MSFT figure in Lecture 10 slides c (4 Points) What is the portfolio beta where the weights are 40 stock B, 30 stock D, and 30 stock E d (4 Points) What is the portfolio beta where each of the five stocks have equal weights

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 27, 2024

Month P_a P_b P_c P_d P_e P_market 0 61.96 53.36 62.74 48.60 434.69 1,756.54 1 64.17 52.09 64.12 52.46 461.60 1,805.81 2 65.50 55.19 68.83

Month	P_a	P_b	P_c	P_d	P_e	P_market
0	61.96	53.36	62.74	48.60	434.69	1,756.54
1	64.17	52.09	64.12	52.46	461.60	1,805.81
2	65.50	55.19	68.83	56.08	477.94	1,848.36
3	61.43	60.53	71.18	53.06	495.06	1,782.59
4	65.57	62.37	73.98	57.54	538.44	1,859.45
5	63.25	57.09	75.81	58.89	537.10	1,872.34
6	63.92	51.65	80.41	57.21	533.89	1,883.95
7	64.50	52.63	78.45	61.84	532.50	1,923.57
8	65.09	58.08	83.39	59.51	536.24	1,960.23
9	65.38	54.25	77.32	60.30	517.03	1,930.67
10	75.61	59.09	84.23	62.96	538.84	2,003.37
11	74.19	57.53	76.48	63.07	509.66	1,972.29
12	79.26	63.99	78.31	68.00	553.52	2,018.05
13	80.79	59.87	78.28	72.64	577.71	2,067.56
14	85.31	59.31	71.22	76.58	619.11	2,058.90
15	85.27	56.45	62.23	78.05	596.96	1,994.99
16	93.71	69.38	65.04	82.88	642.68	2,104.50
17	92.78	66.81	62.79	82.36	682.16	2,067.89
18	87.81	72.82	68.16	79.23	672.66	2,085.51
19	91.46	72.75	67.49	81.98	673.62	2,107.39
20	91.22	69.63	67.09	83.98	666.90	2,063.11
21	96.57	73.30	62.20	90.06	700.94	2,103.84
22	96.10	69.36	61.02	82.25	715.99	1,972.18
23	95.30	69.43	52.18	77.50	723.83	1,920.03
24	102.55	77.71	58.27	79.34	784.41	2,079.36
25	111.04	79.69	58.65	75.83	783.77	2,080.41
26	109.69	78.40	54.86	78.80	741.91	2,043.94
27	104.77	68.06	50.24	77.85	767.39	1,940.24
28	103.40	67.75	55.34	78.67	774.57	1,932.23
29	111.15	73.83	62.56	83.98	796.69	2,059.74
30	112.15	75.80	63.53	81.36	765.23	2,065.30
31	110.67	83.71	59.84	78.41	762.20	2,096.95
32	107.51	79.41	62.57	77.83	793.84	2,098.86
33	116.39	81.80	68.30	75.38	813.97	2,173.60
34	112.93	79.42	68.29	76.26	741.80	2,170.95
35	108.90	71.33	73.97	72.66	768.34	2,168.27
36	103.26	75.16	69.55	68.67	742.16	2,126.15
37	109.51	72.00	80.33	63.09	783.18	2,198.81
38	114.07	68.46	77.96	64.75	789.79	2,238.83
39	117.05	79.10	80.42	64.67	724.98	2,278.87
40	123.29	81.35	81.93	66.52	736.55	2,363.64
41	124.92	82.49	78.62	64.80	723.05	2,362.72
42	133.62	86.12	86.68	68.06	692.19	2,384.20
43	131.40	89.64	90.11	63.83	605.92	2,411.80
44	132.06	86.60	91.84	66.85	570.46	2,423.41
45	128.79	90.80	97.39	66.41	539.82	2,470.30
46	129.02	95.49	101.14	64.67	528.44	2,471.65
47	141.64	93.42	107.35	68.00	595.11	2,519.36
48	143.57	102.34	116.90	57.31	589.50	2,575.26
49	155.72	104.32	122.23	64.48	686.76	2,647.58
50	164.96	102.23	136.46	61.03	711.37	2,673.61
51	174.86	113.91	140.96	66.24	765.44	2,823.81
52	158.65	116.25	134.52	57.36	664.72	2,713.83
53	155.14	116.30	128.22	52.69	648.69	2,640.87
54	161.77	120.99	125.59	59.14	624.52	2,648.05
55	163.30	129.33	132.82	54.10	649.32	2,705.27
56	171.74	136.40	118.62	54.92	670.93	2,718.37
57	173.87	137.15	125.73	55.35	705.53	2,816.29
58	176.73	152.68	122.15	64.70	766.88	2,901.52
59	183.28	159.03	134.15	67.69	775.70	2,913.98
60	155.61	137.24	106.73	62.25	733.47	2,711.74
61	159.54	142.76	120.11	69.44	809.07	2,760.17
62	152.93	136.97	112.50	56.73	838.34	2,506.85
63	163.35	151.97	117.89	56.76	847.34	2,704.10
64	164.79	163.65	122.37	50.45	938.97	2,784.49
65	170.79	158.37	120.72	47.05	1024.12	2,834.40
66	182.65	165.35	124.23	47.44	1028.31	2,945.83
67	170.24	151.41	107.40	46.13	1027.11	2,752.06
68	186.48	151.73	122.17	48.00	1099.47	2,941.76
69	192.96	154.50	118.03	49.21	1123.04	2,980.38
70	205.80	156.07	107.49	54.14	1101.69	2,926.46
71	209.51	148.44	114.10	56.05	1084.62	2,976.74
72	213.11	156.49	124.48	59.00	1144.38	3,037.56
73	200.33	162.89	131.76	67.40	1177.92	3,140.98
74	198.40	162.64	134.45	66.52	1191.31	3,230.78
75	208.55	182.31	119.58	60.73	1057.96	3,225.52
76	199.17	170.40	113.90	53.35	1032.51	2,954.22
77	170.71	143.98	106.39	53.49	846.00	2,584.59
78	202.34	161.95	106.70	55.49	1020.32	2,912.43
79	228.71	174.79	111.15	59.61	1147.86	3,044.31
80	230.57	187.33	117.04	59.06	1128.12	3,100.29
81	245.82	194.85	122.94	57.21	1207.42	3,271.12
82	263.92	272.65	132.65	56.91	1196.31	3,500.31
83	257.14	251.32	139.03	53.50	1177.64	3,363.00
84	248.25	232.27	146.39	51.39	1128.98	3,269.96
85	258.21	245.80	162.80	62.63	1137.65	3,621.63
86	247.24	222.53	170.71	63.10	1185.44	3,756.07
87	253.45	225.56	171.47	66.20	1118.37	3,714.24
88	241.77	216.50	203.54	63.37	1159.92	3,811.15
89	285.67	211.87	218.61	69.97	1404.30	3,972.89
90	304.81	230.32	215.07	71.06	1464.12	4,181.17
91	300.33	238.10	228.33	80.92	1406.60	4,204.11
92	300.31	244.27	206.12	78.12	1492.22	4,297.50
93	310.69	241.93	195.81	77.10	1623.57	4,395.26
94	308.79	265.27	200.79	81.37	1549.15	4,522.68
95	310.76	271.22	182.79	79.93	1697.99	4,307.54
96	353.71	299.69	194.25	84.09	1784.84	4,605.38
97	381.18	284.96	185.12	84.37	1817.07	4,567.00
98	394.88	254.13	197.94	97.73	2096.39	4,766.18
99	350.62	232.63	192.98	100.90	1986.35	4,515.55
100	301.75	210.53	180.47	98.72	1863.39	4,373.94
101	285.99	212.32	214.37	96.39	2044.58	4,530.41
102	288.74	175.94	202.56	91.55	1955.47	4,131.93
103	291.00	160.24	208.66	92.62	2059.65	4,132.15
104	263.63	165.04	172.81	88.71	2149.12	3,785.38
105	291.09	184.02	191.65	91.60	2137.39	4,130.29
106	278.98	156.12	179.80	94.51	2119.21	3,955.00
107	268.65	143.84	159.72	91.83	2141.93	3,585.62
108	288.30	162.59	210.71	91.19	2532.88	3,871.98
109	315.43	160.25	231.67	98.69	2579.00	4,080.11
110	309.37	132.59	234.75	90.27	2466.18	3,839.50
111	317.51	167.97	247.23	85.46	2438.85	4,076.60
112	290.45	163.61	235.86	81.49	2486.54	3,970.15
113	289.06	199.78	225.32	72.48	2458.15	4,109.31
114	296.50	198.37	215.43	71.51	2663.31	4,169.48
115	279.64	223.38	203.68	66.90	2386.84	4,179.83
116	308.67	211.26	243.58	67.98	2493.36	4,450.38
117	331.72	225.01	262.51	73.45	2481.72	4,588.96
118	328.20	221.46	279.68	64.62	2531.33	4,507.66
119	302.16	202.78	271.60	69.23	2539.99	4,288.05
120	283.31	204.22	248.27	68.38	2416.54	4,186.77

4. (30 Points) Please use the real-world data available on MS Excel file attached to answer this question. a. (15 Points) Please calculate the alpha and beta of all five stocks using the regression model and the industry standard discussed during our lecture. b. (7 Points) Please plot the graph of stock E's excess returns, beta, alpha, and the epsilons (unexplained variation in the excess stock return). To have a better idea, you can check the MSFT figure in Lecture 10 slides. c. (4 Points) What is the portfolio beta where the weights are 40% stock B, 30% stock D, and 30% stock E ? d. (4 Points) What is the portfolio beta where each of the five stocks have equal weights