Carhart 4 factor model run through regression

polly95

New Member
Joined
Jul 15, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.


Thesis Data.xlsx
BCDEFGHIJKLMNOPQR
8
9Energy IndexS&P 500
10MONTHLY- Risk Free RateMONTHLY- Risk Free RateMkt-RFSMBHMLMOM
113/31/21-6.974.243.08-2.487.4-5.83S&P 500
122/26/21-13.392.612.782.117.08-7.67SUMMARY OUTPUT
131/29/216.28-1.11-0.037.192.854.36
1412/31/2019.333.704.634.81-1.36-2.42Regression Statistics
1511/30/2020.5410.7412.475.482.11-12.25Multiple R0.998252157
1610/30/205.76-2.78-2.14.444.03-3.03R Square0.996507368
179/30/207.60-3.93-3.630.06-2.513.05Adjusted R Square0.996266497
188/31/2018.687.007.63-0.25-2.940.51Standard Error0.265114258
197/31/2015.745.505.77-2.18-1.397.61Observations63
206/30/205.181.832.462.71-2.35-0.75
215/29/2010.234.525.582.49-5.050.67ANOVA
224/30/2011.7612.6813.652.75-1.39-5.49dfSSMSFSignificance F
233/31/20-23.02-12.63-13.38-5.03-13.968.54Regression41163.11283290.77820744137.0968311.68688E-70
242/28/202.23-8.53-8.131.02-3.92-0.23Residual584.0765630390.07028557
251/31/203.43-0.29-0.11-3.08-6.275.43Total621167.189393
2612/31/197.062.722.770.671.91-2.27
2711/29/192.793.283.870.91-2.05-2.6CoefficientsStandard Errort StatP-valueLower 95%Upper 95%Lower 95.0%Upper 95.0%
2810/31/19-1.951.892.060.24-1.880.29Intercept-0.1719757030.035440225-4.8525567619.55623E-06-0.24291702-0.101034385-0.242917-0.1010344
299/30/19-0.771.541.43-1.056.77-6.85Mkt-RF0.9704891170.008753794110.86497333.17241E-690.9529664950.9880117390.95296650.9880117
308/30/191.57-1.97-2.58-2.4-4.857.64SMB-0.1351699240.013970305-9.675517051.03875E-13-0.163134528-0.10720532-0.1631345-0.1072053
317/31/190.591.121.19-2.080.342.72HML0.0162995330.0118097751.3801730.172830197-0.0073403010.039939366-0.00734030.0399394
326/28/196.656.716.930.22-0.79-2.14MOM0.0007021780.0116284840.0603843410.95205712-0.0225747610.023979118-0.02257480.0239791
335/31/19-2.01-6.79-6.94-1.18-2.287.6
344/30/193.683.723.96-1.752.02-2.55
353/29/19-0.301.601.1-3.05-4.192.14Energy Index
362/28/192.582.793.42.12-2.710.86SUMMARY OUTPUT
371/31/1914.327.668.412.89-0.46-8.65
3812/31/18-6.94-9.37-9.55-2.48-1.891.85Regression Statistics
3911/30/1810.091.611.69-0.710.34-1.33Multiple R0.650795239
4010/31/18-5.62-7.13-7.68-4.783.46-1.84R Square0.423534443
419/28/18-2.020.280.06-2.28-1.63-0.06Adjusted R Square0.383778198
428/31/18-4.852.873.441.15-3.925.22Standard Error5.992816382
437/31/184.033.443.19-2.210.57-1.67Observations63
446/29/18-9.310.340.481.19-2.31-2.38
455/31/18-2.362.022.655.28-3.153.8ANOVA
464/30/184.030.130.291.130.50.27dfSSMSFSignificance F
473/30/180.50-2.81-2.354.05-0.12-1.28Regression41530.401232382.600307910.653280761.53358E-06
482/28/18-2.40-4.00-3.650.3-1.013.97Residual582083.00319535.91384819
491/31/182.775.515.58-3.09-1.323.94Total623613.404427
5012/29/175.290.891.06-1.290.17-1.63
5111/30/17-4.372.733.12-0.58-0.09-0.71CoefficientsStandard Errort StatP-valueLower 95%Upper 95%Lower 95.0%Upper 95.0%
5210/31/174.082.132.25-1.950.124.3Intercept0.0246003420.8011140620.0307076650.975608104-1.5790036831.628204367-1.57900371.6282044
539/29/17-1.901.842.514.473.14-1.18Mkt-RF1.0638771970.1978764915.3764709131.41509E-060.6677843661.4599700290.66778441.45997
548/31/170.41-0.040.16-1.62-2.193.55SMB0.3091132860.3157939270.9788449360.331721951-0.3230169390.941243511-0.32301690.9412435
557/31/175.401.861.87-1.47-0.361.66HML-0.3334219390.266955896-1.2489776170.216690751-0.8677922230.200948345-0.86779220.2009483
566/30/17-0.200.420.782.151.45-0.32MOM0.0835599480.262857870.3178902280.751709931-0.4426072460.609727143-0.44260720.6097271
575/31/171.471.101.06-2.61-3.671.46
584/28/17-0.800.861.090.72-2.110.44
593/31/17-1.48-0.070.171.17-3.33-0.92
602/28/174.463.683.57-2.01-1.79-1.63
611/31/174.261.751.94-1.06-2.81-0.98
6212/30/160.131.791.820.113.51-0.38
6311/30/16-9.283.414.865.478.22-4.07
6410/31/16-2.93-1.96-2.02-4.434.070.69
659/30/16-1.72-0.140.252.04-1.21-0.53
668/31/161.08-0.140.51.133.31-3.17
677/29/163.593.543.952.6-1.23-3.08
686/30/161.740.07-0.050.64-1.44.11
695/31/16-6.011.521.78-0.27-1.62.14
704/29/16-0.860.260.920.693.28-6.26
713/31/169.406.586.960.821.11-5.13
722/29/16-2.37-0.43-0.080.83-0.59-4.06
731/29/16-11.99-5.08-5.77-3.362.011.34
Carhart
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,148,011
Messages
5,744,334
Members
423,863
Latest member
teehexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top