Hello.
I am currently trying to run the Carhart 4 factor model through excel. I have found online to do this by running a regression.
I am trying to compare a clean energy index to the s&p 500. I have obtained the factors from the Kenneth French data and have calculated the returns and have taken the risk free rate from the returns.
1. Is regression the right data analysis to run for this model ?
2. For this am I suppose to run two separate regressions ?
3. If yes does anyone know if the regression produced seems right ?
4. Does anyone know what the best graph would be to represent my results. ie. residuals, residual plots, standardised residuals, line fit plots, normal probability plots.
Any help would be great and thank you so much in advance.
I am currently trying to run the Carhart 4 factor model through excel. I have found online to do this by running a regression.
I am trying to compare a clean energy index to the s&p 500. I have obtained the factors from the Kenneth French data and have calculated the returns and have taken the risk free rate from the returns.
1. Is regression the right data analysis to run for this model ?
2. For this am I suppose to run two separate regressions ?
3. If yes does anyone know if the regression produced seems right ?
4. Does anyone know what the best graph would be to represent my results. ie. residuals, residual plots, standardised residuals, line fit plots, normal probability plots.
Any help would be great and thank you so much in advance.
| Thesis Data.xlsx | |||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
| 8 | |||||||||||||||||||
| 9 | Energy Index | S&P 500 | |||||||||||||||||
| 10 | MONTHLY- Risk Free Rate | MONTHLY- Risk Free Rate | Mkt-RF | SMB | HML | MOM | |||||||||||||
| 11 | 3/31/21 | -6.97 | 4.24 | 3.08 | -2.48 | 7.4 | -5.83 | S&P 500 | |||||||||||
| 12 | 2/26/21 | -13.39 | 2.61 | 2.78 | 2.11 | 7.08 | -7.67 | SUMMARY OUTPUT | |||||||||||
| 13 | 1/29/21 | 6.28 | -1.11 | -0.03 | 7.19 | 2.85 | 4.36 | ||||||||||||
| 14 | 12/31/20 | 19.33 | 3.70 | 4.63 | 4.81 | -1.36 | -2.42 | Regression Statistics | |||||||||||
| 15 | 11/30/20 | 20.54 | 10.74 | 12.47 | 5.48 | 2.11 | -12.25 | Multiple R | 0.998252157 | ||||||||||
| 16 | 10/30/20 | 5.76 | -2.78 | -2.1 | 4.44 | 4.03 | -3.03 | R Square | 0.996507368 | ||||||||||
| 17 | 9/30/20 | 7.60 | -3.93 | -3.63 | 0.06 | -2.51 | 3.05 | Adjusted R Square | 0.996266497 | ||||||||||
| 18 | 8/31/20 | 18.68 | 7.00 | 7.63 | -0.25 | -2.94 | 0.51 | Standard Error | 0.265114258 | ||||||||||
| 19 | 7/31/20 | 15.74 | 5.50 | 5.77 | -2.18 | -1.39 | 7.61 | Observations | 63 | ||||||||||
| 20 | 6/30/20 | 5.18 | 1.83 | 2.46 | 2.71 | -2.35 | -0.75 | ||||||||||||
| 21 | 5/29/20 | 10.23 | 4.52 | 5.58 | 2.49 | -5.05 | 0.67 | ANOVA | |||||||||||
| 22 | 4/30/20 | 11.76 | 12.68 | 13.65 | 2.75 | -1.39 | -5.49 | df | SS | MS | F | Significance F | |||||||
| 23 | 3/31/20 | -23.02 | -12.63 | -13.38 | -5.03 | -13.96 | 8.54 | Regression | 4 | 1163.11283 | 290.7782074 | 4137.096831 | 1.68688E-70 | ||||||
| 24 | 2/28/20 | 2.23 | -8.53 | -8.13 | 1.02 | -3.92 | -0.23 | Residual | 58 | 4.076563039 | 0.07028557 | ||||||||
| 25 | 1/31/20 | 3.43 | -0.29 | -0.11 | -3.08 | -6.27 | 5.43 | Total | 62 | 1167.189393 | |||||||||
| 26 | 12/31/19 | 7.06 | 2.72 | 2.77 | 0.67 | 1.91 | -2.27 | ||||||||||||
| 27 | 11/29/19 | 2.79 | 3.28 | 3.87 | 0.91 | -2.05 | -2.6 | Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | ||||
| 28 | 10/31/19 | -1.95 | 1.89 | 2.06 | 0.24 | -1.88 | 0.29 | Intercept | -0.171975703 | 0.035440225 | -4.852556761 | 9.55623E-06 | -0.24291702 | -0.101034385 | -0.242917 | -0.1010344 | |||
| 29 | 9/30/19 | -0.77 | 1.54 | 1.43 | -1.05 | 6.77 | -6.85 | Mkt-RF | 0.970489117 | 0.008753794 | 110.8649733 | 3.17241E-69 | 0.952966495 | 0.988011739 | 0.9529665 | 0.9880117 | |||
| 30 | 8/30/19 | 1.57 | -1.97 | -2.58 | -2.4 | -4.85 | 7.64 | SMB | -0.135169924 | 0.013970305 | -9.67551705 | 1.03875E-13 | -0.163134528 | -0.10720532 | -0.1631345 | -0.1072053 | |||
| 31 | 7/31/19 | 0.59 | 1.12 | 1.19 | -2.08 | 0.34 | 2.72 | HML | 0.016299533 | 0.011809775 | 1.380173 | 0.172830197 | -0.007340301 | 0.039939366 | -0.0073403 | 0.0399394 | |||
| 32 | 6/28/19 | 6.65 | 6.71 | 6.93 | 0.22 | -0.79 | -2.14 | MOM | 0.000702178 | 0.011628484 | 0.060384341 | 0.95205712 | -0.022574761 | 0.023979118 | -0.0225748 | 0.0239791 | |||
| 33 | 5/31/19 | -2.01 | -6.79 | -6.94 | -1.18 | -2.28 | 7.6 | ||||||||||||
| 34 | 4/30/19 | 3.68 | 3.72 | 3.96 | -1.75 | 2.02 | -2.55 | ||||||||||||
| 35 | 3/29/19 | -0.30 | 1.60 | 1.1 | -3.05 | -4.19 | 2.14 | Energy Index | |||||||||||
| 36 | 2/28/19 | 2.58 | 2.79 | 3.4 | 2.12 | -2.71 | 0.86 | SUMMARY OUTPUT | |||||||||||
| 37 | 1/31/19 | 14.32 | 7.66 | 8.41 | 2.89 | -0.46 | -8.65 | ||||||||||||
| 38 | 12/31/18 | -6.94 | -9.37 | -9.55 | -2.48 | -1.89 | 1.85 | Regression Statistics | |||||||||||
| 39 | 11/30/18 | 10.09 | 1.61 | 1.69 | -0.71 | 0.34 | -1.33 | Multiple R | 0.650795239 | ||||||||||
| 40 | 10/31/18 | -5.62 | -7.13 | -7.68 | -4.78 | 3.46 | -1.84 | R Square | 0.423534443 | ||||||||||
| 41 | 9/28/18 | -2.02 | 0.28 | 0.06 | -2.28 | -1.63 | -0.06 | Adjusted R Square | 0.383778198 | ||||||||||
| 42 | 8/31/18 | -4.85 | 2.87 | 3.44 | 1.15 | -3.92 | 5.22 | Standard Error | 5.992816382 | ||||||||||
| 43 | 7/31/18 | 4.03 | 3.44 | 3.19 | -2.21 | 0.57 | -1.67 | Observations | 63 | ||||||||||
| 44 | 6/29/18 | -9.31 | 0.34 | 0.48 | 1.19 | -2.31 | -2.38 | ||||||||||||
| 45 | 5/31/18 | -2.36 | 2.02 | 2.65 | 5.28 | -3.15 | 3.8 | ANOVA | |||||||||||
| 46 | 4/30/18 | 4.03 | 0.13 | 0.29 | 1.13 | 0.5 | 0.27 | df | SS | MS | F | Significance F | |||||||
| 47 | 3/30/18 | 0.50 | -2.81 | -2.35 | 4.05 | -0.12 | -1.28 | Regression | 4 | 1530.401232 | 382.6003079 | 10.65328076 | 1.53358E-06 | ||||||
| 48 | 2/28/18 | -2.40 | -4.00 | -3.65 | 0.3 | -1.01 | 3.97 | Residual | 58 | 2083.003195 | 35.91384819 | ||||||||
| 49 | 1/31/18 | 2.77 | 5.51 | 5.58 | -3.09 | -1.32 | 3.94 | Total | 62 | 3613.404427 | |||||||||
| 50 | 12/29/17 | 5.29 | 0.89 | 1.06 | -1.29 | 0.17 | -1.63 | ||||||||||||
| 51 | 11/30/17 | -4.37 | 2.73 | 3.12 | -0.58 | -0.09 | -0.71 | Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | ||||
| 52 | 10/31/17 | 4.08 | 2.13 | 2.25 | -1.95 | 0.12 | 4.3 | Intercept | 0.024600342 | 0.801114062 | 0.030707665 | 0.975608104 | -1.579003683 | 1.628204367 | -1.5790037 | 1.6282044 | |||
| 53 | 9/29/17 | -1.90 | 1.84 | 2.51 | 4.47 | 3.14 | -1.18 | Mkt-RF | 1.063877197 | 0.197876491 | 5.376470913 | 1.41509E-06 | 0.667784366 | 1.459970029 | 0.6677844 | 1.45997 | |||
| 54 | 8/31/17 | 0.41 | -0.04 | 0.16 | -1.62 | -2.19 | 3.55 | SMB | 0.309113286 | 0.315793927 | 0.978844936 | 0.331721951 | -0.323016939 | 0.941243511 | -0.3230169 | 0.9412435 | |||
| 55 | 7/31/17 | 5.40 | 1.86 | 1.87 | -1.47 | -0.36 | 1.66 | HML | -0.333421939 | 0.266955896 | -1.248977617 | 0.216690751 | -0.867792223 | 0.200948345 | -0.8677922 | 0.2009483 | |||
| 56 | 6/30/17 | -0.20 | 0.42 | 0.78 | 2.15 | 1.45 | -0.32 | MOM | 0.083559948 | 0.26285787 | 0.317890228 | 0.751709931 | -0.442607246 | 0.609727143 | -0.4426072 | 0.6097271 | |||
| 57 | 5/31/17 | 1.47 | 1.10 | 1.06 | -2.61 | -3.67 | 1.46 | ||||||||||||
| 58 | 4/28/17 | -0.80 | 0.86 | 1.09 | 0.72 | -2.11 | 0.44 | ||||||||||||
| 59 | 3/31/17 | -1.48 | -0.07 | 0.17 | 1.17 | -3.33 | -0.92 | ||||||||||||
| 60 | 2/28/17 | 4.46 | 3.68 | 3.57 | -2.01 | -1.79 | -1.63 | ||||||||||||
| 61 | 1/31/17 | 4.26 | 1.75 | 1.94 | -1.06 | -2.81 | -0.98 | ||||||||||||
| 62 | 12/30/16 | 0.13 | 1.79 | 1.82 | 0.11 | 3.51 | -0.38 | ||||||||||||
| 63 | 11/30/16 | -9.28 | 3.41 | 4.86 | 5.47 | 8.22 | -4.07 | ||||||||||||
| 64 | 10/31/16 | -2.93 | -1.96 | -2.02 | -4.43 | 4.07 | 0.69 | ||||||||||||
| 65 | 9/30/16 | -1.72 | -0.14 | 0.25 | 2.04 | -1.21 | -0.53 | ||||||||||||
| 66 | 8/31/16 | 1.08 | -0.14 | 0.5 | 1.13 | 3.31 | -3.17 | ||||||||||||
| 67 | 7/29/16 | 3.59 | 3.54 | 3.95 | 2.6 | -1.23 | -3.08 | ||||||||||||
| 68 | 6/30/16 | 1.74 | 0.07 | -0.05 | 0.64 | -1.4 | 4.11 | ||||||||||||
| 69 | 5/31/16 | -6.01 | 1.52 | 1.78 | -0.27 | -1.6 | 2.14 | ||||||||||||
| 70 | 4/29/16 | -0.86 | 0.26 | 0.92 | 0.69 | 3.28 | -6.26 | ||||||||||||
| 71 | 3/31/16 | 9.40 | 6.58 | 6.96 | 0.82 | 1.11 | -5.13 | ||||||||||||
| 72 | 2/29/16 | -2.37 | -0.43 | -0.08 | 0.83 | -0.59 | -4.06 | ||||||||||||
| 73 | 1/29/16 | -11.99 | -5.08 | -5.77 | -3.36 | 2.01 | 1.34 | ||||||||||||
Carhart | |||||||||||||||||||
