Using Excel, estimate the average return and the standard deviation for the four stocks and the market.

Estimate the alphas and betas for the four stocks using a linear regression, which are statistically significant?

Is CAPM valid?

If cant use provided numbers, please provide example of how one would solve. Thank you

Ex MSFT Ex FB Ex AAPL Ex AMZN Ex Market 0.000271 0.03747 Date 1/1/13 2/1/13 3/1/13 4/1/13 5/1/13 6/1/13 7/1/13 81/13 9/1/13 10/1/13 11/1/13 12/1/13 1/1/14 21/14 3/1/14 4/1/14 5/1/14 6/1/14 7/1/14 B/1/14 9/1/14 10/1/14 11/1/14 12 1/14 1/1/15 2/1/15 31/15 4/1/15 5/1/15 6/1/15 7/1/15 8/1/15 9/1/15 10/1/15 11/1/15 12/1/15 1/1/16 2/1/16 31/16 Risk-free Rate Microsoft Facebook Apple Amazon VW Market 0.000042 0.027717 0.163837 -0.144094 0.058312 0.054145 0.000067 0.021129 -0.120429 -0.025116 -0.004633 0.00834 0.000067 0.028957 -0.061284 0.002855 0.008401 0.035294 0.000042 0.15714 0.085575 -0.047581 0.014947 0.000027 0.061329 -0.123195 0.022596 0.060636 0.019074 0.000025 -0.010172 0.02185 -0.118303 0.031538 -0.015036 0.000017 -0.078304 0.4791 0.141225 0.084735 0.052717 0.000033 0,056219 0.12212 0.083389 -0.067193 -0.025727 0.000017 -0.003593 0.2164 -0.021481 0.112677 0.000092 0.063852 -0.000498 0.096386 0.164374 0.039895 0.000042 0.084875 -0.063639 0.069673 0.081285 0.024965 0.000017 -0.018883 0.162497 0.008902 0.01313 0.02614 0.000027 0.011494 0.144943 -0.107697 -0.100554 -0.029987 0.000042 0.01982 0.094135 0.057311 0.009507 0.046186 0.000042 0.069956 -0.12007 0.019953 -0.071072 0.004501 0.000017 -0.014394 -0.007636 0.099396 -0.095833 0.00167 0.000025 0.020297 0.058883 0.078293 0.027685 0.020223 0.000017 0.018564 0.063033 0.027662 0.03913 0.027961 0.000017 0.035012 0.079655 0.028731 -0.036302 -0.020536 0.000025 0.059082 0.029869 0.077092 0.08323 0.040205 0.000008 0.020471 0.056402 -0.017073 -0.048962 -0.025132 0.000027 0.012726 -0.05:24 0.07196 -0.052661 0.021195 0.000033 0.02492 0.036138 0.105556 0.108623 0.021159 0.000025 -0.028446 0.004118 -0.071891 -0.08354 -0.003623 0.000017 -0.130248 -0.130248 -0.027044 0.062424 0.142355 -0.0272 0.000017 0.093069 0.040311 0.100461 0.072293 0.056017 0.000017 -0.072862 0.041091 -0.031372 -0.021202 -0.010454 0.000017 0.196409 -0.041902 0.005786 0.133512 0.008716 0.000008 -0.030222 0.005332 0.045146 0.017663 0.010341 0.000008 -0.057832 0.083028 -0.037266 0.011323 -0.019262 0.000025 0.057758 0.096135 -0.032888 0.235113 0.012113 0.000033 -0.061456 -0.048718 -0.066117 -0.043383 0.060046 0.000008 0.017004 0.005256 -0.021816 -0.021816 -0.00195 -0.033769 0.000000 0.189336 0.13426 0.083409 0.222724 0.274021 0.000058 0.039324 0.022261 -0.00569 0.06215 0.002448 0.000142 0.020791 0.004029 -0.110228 0.016682 0.022287 0.000192 -0.00703 0.072138 -0.075242 -0.131516 -0.057094 0.000217 -0.069886 -0.047144 -0.001335 -0.058739 0.000685 0.000208 0.085495 0.067153 0.127211 0.074423 0.070518 0.000158 -0.097049 0.0305 -0.139921 0.111094 0.011814 0.000192 0.069982 0.010461 0.071368 0.095817 0.014316 0.000183 -0.034528 -0.038128 -0.04266 -0.00992 0.003111 0.000217 0.10768 0.084529 0.090063 0.060353 0.038775 0.000217 0.020113 0.017589 0.023606 0.01364 0.002789 0.000158 0.002436 0.017047 0.065504 0.088603 0.003017 0.000200 0.040278 0.021205 0.004334 -0.056717 0.021594 0.000250 0.012183 -0.095962 -0.021578 -0.049695 0.040491 0.000350 0.031198 0.031198 -0.028458 0.047955 -0.000933 0.018792 0.000417 0.040393 0.132725 0.047746 0.098164 0.02219 0.000400 -0.004331 0.040055 0.133581 0.026182 0.032665 0.000550 0.029384 0.04803 0.04869 0.04911 0.002092 0.000625 0.039478 0.057726 -0.00007 0.043371 0.009615 0.000608 0.025854 0.008053 0.067804 0.075276 0.009335 0.000700 -0.01303 -0.003169 -0.057214 -0.026764 0.009469 0.000808 0.054693 0.121009 0.032704 0.020434 0.02033 0.000817 0.033838 0.016071 0.016071 0.106905 0.106905 -0.007269 0.001589 0.000825 -0.003745 -0.006396 -0.060244 -0.019631 0.023734 0.000833 0.11666 0.053784 0.096808 0.149717 0.019281 0.000908 0.016951 -0.015995 0.02035 0.064662 0.027309 0.001000 0.016277 -0.004064 -0.015246 -0.006187 0.012173 0.001083 0.110708 0.059107 -0.010636 0.240639 0.050656 0.001150 -0.008631 -0.045856 0.06761 0.042429 0.039465 0.001367 -0.026661 -0.103914 -0.058051 -0.043049 0.018433 0.001383 0.024652 0.076413 -0.01502 0.082075 0.004769 0.001425 0.061377 0.115 0.135181 0.040539 0.02617 0.001508 -0.002327 0.013244 -0.009418 0.043065 0.005334 0.001575 0.075753 -0.111877 0.027983 0.045676 0.031605 0.001617 0.062877 0.018252 0.200063 0.132365 0.030221 0.001700 0.018161 -0.064132 -0.008303 -0.004824 0.001808 -0.066101 -0.07704 -0.030478 -0.202192 0.074031 0.001867 0.042505 -0.073654 -0.180709 0.057672 0.01853 0.001975 -0.084047 -0,067705 -0.116698 -0.11135 0.08981 4/1/16 5/1/16 6/1/16 7/1/16 8/1/16 9/1/16 10/1/16 11/1/16 121/16 1/1/17 21/17 3/1/17 4/1/17 5/1/17 6/1/17 7/1/17 8/1/17 9/1/17 10/1/17 11/1/17 12/1/17 1/1/18 2/1/18 3/1/18 4/1/18 5/1/18 6/1/18 7/1/18 8/1/18 9/1/18 10/1/18 11/1/18 12/1/18 0.000443 Ex MSFT Ex FB Ex AAPL Ex AMZN Ex Market 0.000271 0.03747 Date 1/1/13 2/1/13 3/1/13 4/1/13 5/1/13 6/1/13 7/1/13 81/13 9/1/13 10/1/13 11/1/13 12/1/13 1/1/14 21/14 3/1/14 4/1/14 5/1/14 6/1/14 7/1/14 B/1/14 9/1/14 10/1/14 11/1/14 12 1/14 1/1/15 2/1/15 31/15 4/1/15 5/1/15 6/1/15 7/1/15 8/1/15 9/1/15 10/1/15 11/1/15 12/1/15 1/1/16 2/1/16 31/16 Risk-free Rate Microsoft Facebook Apple Amazon VW Market 0.000042 0.027717 0.163837 -0.144094 0.058312 0.054145 0.000067 0.021129 -0.120429 -0.025116 -0.004633 0.00834 0.000067 0.028957 -0.061284 0.002855 0.008401 0.035294 0.000042 0.15714 0.085575 -0.047581 0.014947 0.000027 0.061329 -0.123195 0.022596 0.060636 0.019074 0.000025 -0.010172 0.02185 -0.118303 0.031538 -0.015036 0.000017 -0.078304 0.4791 0.141225 0.084735 0.052717 0.000033 0,056219 0.12212 0.083389 -0.067193 -0.025727 0.000017 -0.003593 0.2164 -0.021481 0.112677 0.000092 0.063852 -0.000498 0.096386 0.164374 0.039895 0.000042 0.084875 -0.063639 0.069673 0.081285 0.024965 0.000017 -0.018883 0.162497 0.008902 0.01313 0.02614 0.000027 0.011494 0.144943 -0.107697 -0.100554 -0.029987 0.000042 0.01982 0.094135 0.057311 0.009507 0.046186 0.000042 0.069956 -0.12007 0.019953 -0.071072 0.004501 0.000017 -0.014394 -0.007636 0.099396 -0.095833 0.00167 0.000025 0.020297 0.058883 0.078293 0.027685 0.020223 0.000017 0.018564 0.063033 0.027662 0.03913 0.027961 0.000017 0.035012 0.079655 0.028731 -0.036302 -0.020536 0.000025 0.059082 0.029869 0.077092 0.08323 0.040205 0.000008 0.020471 0.056402 -0.017073 -0.048962 -0.025132 0.000027 0.012726 -0.05:24 0.07196 -0.052661 0.021195 0.000033 0.02492 0.036138 0.105556 0.108623 0.021159 0.000025 -0.028446 0.004118 -0.071891 -0.08354 -0.003623 0.000017 -0.130248 -0.130248 -0.027044 0.062424 0.142355 -0.0272 0.000017 0.093069 0.040311 0.100461 0.072293 0.056017 0.000017 -0.072862 0.041091 -0.031372 -0.021202 -0.010454 0.000017 0.196409 -0.041902 0.005786 0.133512 0.008716 0.000008 -0.030222 0.005332 0.045146 0.017663 0.010341 0.000008 -0.057832 0.083028 -0.037266 0.011323 -0.019262 0.000025 0.057758 0.096135 -0.032888 0.235113 0.012113 0.000033 -0.061456 -0.048718 -0.066117 -0.043383 0.060046 0.000008 0.017004 0.005256 -0.021816 -0.021816 -0.00195 -0.033769 0.000000 0.189336 0.13426 0.083409 0.222724 0.274021 0.000058 0.039324 0.022261 -0.00569 0.06215 0.002448 0.000142 0.020791 0.004029 -0.110228 0.016682 0.022287 0.000192 -0.00703 0.072138 -0.075242 -0.131516 -0.057094 0.000217 -0.069886 -0.047144 -0.001335 -0.058739 0.000685 0.000208 0.085495 0.067153 0.127211 0.074423 0.070518 0.000158 -0.097049 0.0305 -0.139921 0.111094 0.011814 0.000192 0.069982 0.010461 0.071368 0.095817 0.014316 0.000183 -0.034528 -0.038128 -0.04266 -0.00992 0.003111 0.000217 0.10768 0.084529 0.090063 0.060353 0.038775 0.000217 0.020113 0.017589 0.023606 0.01364 0.002789 0.000158 0.002436 0.017047 0.065504 0.088603 0.003017 0.000200 0.040278 0.021205 0.004334 -0.056717 0.021594 0.000250 0.012183 -0.095962 -0.021578 -0.049695 0.040491 0.000350 0.031198 0.031198 -0.028458 0.047955 -0.000933 0.018792 0.000417 0.040393 0.132725 0.047746 0.098164 0.02219 0.000400 -0.004331 0.040055 0.133581 0.026182 0.032665 0.000550 0.029384 0.04803 0.04869 0.04911 0.002092 0.000625 0.039478 0.057726 -0.00007 0.043371 0.009615 0.000608 0.025854 0.008053 0.067804 0.075276 0.009335 0.000700 -0.01303 -0.003169 -0.057214 -0.026764 0.009469 0.000808 0.054693 0.121009 0.032704 0.020434 0.02033 0.000817 0.033838 0.016071 0.016071 0.106905 0.106905 -0.007269 0.001589 0.000825 -0.003745 -0.006396 -0.060244 -0.019631 0.023734 0.000833 0.11666 0.053784 0.096808 0.149717 0.019281 0.000908 0.016951 -0.015995 0.02035 0.064662 0.027309 0.001000 0.016277 -0.004064 -0.015246 -0.006187 0.012173 0.001083 0.110708 0.059107 -0.010636 0.240639 0.050656 0.001150 -0.008631 -0.045856 0.06761 0.042429 0.039465 0.001367 -0.026661 -0.103914 -0.058051 -0.043049 0.018433 0.001383 0.024652 0.076413 -0.01502 0.082075 0.004769 0.001425 0.061377 0.115 0.135181 0.040539 0.02617 0.001508 -0.002327 0.013244 -0.009418 0.043065 0.005334 0.001575 0.075753 -0.111877 0.027983 0.045676 0.031605 0.001617 0.062877 0.018252 0.200063 0.132365 0.030221 0.001700 0.018161 -0.064132 -0.008303 -0.004824 0.001808 -0.066101 -0.07704 -0.030478 -0.202192 0.074031 0.001867 0.042505 -0.073654 -0.180709 0.057672 0.01853 0.001975 -0.084047 -0,067705 -0.116698 -0.11135 0.08981 4/1/16 5/1/16 6/1/16 7/1/16 8/1/16 9/1/16 10/1/16 11/1/16 121/16 1/1/17 21/17 3/1/17 4/1/17 5/1/17 6/1/17 7/1/17 8/1/17 9/1/17 10/1/17 11/1/17 12/1/17 1/1/18 2/1/18 3/1/18 4/1/18 5/1/18 6/1/18 7/1/18 8/1/18 9/1/18 10/1/18 11/1/18 12/1/18 0.000443