Hi everyone,
I want to calculate the excess return using the Fama and French 3-factor model.
I want to do the following:
" To arrive at our estimate of the excess return, we use the 25 Fama and French portfolios formed on size and book-to-market as our benchmark portfolios. A portfolio return is a value-weighted return based on market capitalization within each of the 25 portfolios. For each year, we group every firm into one of 25 size and BE/ME portfolios based on the intersection between the size and book-to-market independent sorts. Fama and French (1993) conclude that size and the book-to-market of equity proxy for sensitivity to common risk factors in stock returns, which implies that stocks in different size and book-to-market portfolios may have different expected returns. Therefore, stock i’s benchmark return at year t is the return of the portfolio to which stock i belongs at the beginning of fiscal year t. To form a size- and BE/ME-excess return for any stock, we simply subtract the return of the portfolio to which it belongs from the realized return of the stock.
Futher Note: While the Fama and French 25 portfolios are formed at the end of each June, the fiscal yearend of a firm could be any month during the year. Therefore, a firm could change the portfolio to which it belongs during the year. Consider a firm whose fiscal year ends in December in year t − 1. From January to June of year t, it belongs to the portfolio according to the size and BE/ME breakpoints of year t − 1 and from July to December of year t, it belongs to the portfolio according to the size and BE/ME breakpoints of year t. Since we have value-weighted monthly returns of the portfolios, we calculate the benchmark return by annualizing the monthly returns from the portfolio it belongs to each month"
The dependent variable in our regression is the excess stock return, ri,t − RB i,t , where ri,t is the stock return for firm i during fiscal year t and RB i,t is stock i’s benchmark return at year t.
Could you assist me on this?
I have downloaded the following data for fama french 3 factor plus premium data for the period of Jan 1980- Dec 2022.
input double(mktrf smb hml rf umd) long dateff
.055099999999999996 .016200000000000003 .0175 .008 .0755 7335
-.012199999999999999 -.018500000000000003 .0060999999999999995 .0089 .0788 7364
-.129 -.0664 -.0101 .0121 -.0955 7395
.0397 .0105 .0106 .0126 -.0043 7425
.0526 .0213 .0038 .008100000000000001 -.011200000000000002 7455
.030600000000000002 .0166 -.0076 .0060999999999999995 .016200000000000003 7486
.0649 .0414 -.0641 .0053 .0039000000000000003 7517
.018000000000000002 .0392 -.026000000000000002 .0064 .0321 7546
.0219 .0098 -.045899999999999996 .0075 .053899999999999997 7578
.0106 .024700000000000003 -.0276 .0095 .073 7609
.0959 -.0336 -.0833 .0096 .1522 7637
-.0452 -.0026 .0279 .0131 -.0668 7670
-.0504 .0292 .0672 .0104 -.0795 7700
.005699999999999999 -.0034000000000000002 .0102 .010700000000000001 -.013500000000000002 7728
.0356 .0354 .0064 .0121 .0074 7760
-.021099999999999997 .044000000000000004 .022799999999999997 .0108 -.0091 7790
.0011 .02 -.0042 .0115 .037000000000000005 7819
-.0236 -.0083 .0513 .013500000000000002 -.0581 7851
-.0154 -.0219 -.005 .0124 -.0245 7882
-.0704 -.0195 .047599999999999996 .0128 -.0115 7913
-.0717 -.0265 .051699999999999996 .0124 .0195 7943
.0492 .0223 -.0421 .0121 .0401 7973
.0336 -.0103 .0183 .010700000000000001 -.0027 8004
-.0365 .012 .008100000000000001 .0087 .013000000000000001 8035
-.032400000000000005 -.0128 .0319 .008 .0172 8064
-.058600000000000006 .0044 .0605 .0092 .048799999999999996 8092
-.0187 -.0021 .0381 .0098 .0296 8125
.0327 .0147 -.027000000000000003 .0113 -.004 8155
-.039900000000000005 .0052 .0175 .0106 .025 8183
-.030899999999999997 -.004 .015300000000000001 .0096 .0496 8216
-.0319 .0083 .0009 .0105 .044500000000000005 8246
.1114 -.0414 .0095 .0076 -.0352 8278
.0129 .029500000000000002 .0028000000000000004 .0051 .04190000000000001 8308
.113 .023399999999999997 -.0366 .0059 .0018 8337
.0467 .0467 -.0187 .0063 .059500000000000004 8369
.0055000000000000005 -.0022 -.0002 .0067 .0004 8400
.036000000000000004 .0273 -.0075 .0069 -.0173 8431
.0259 .0327 .006999999999999999 .0062 .0377 8459
.0282 .0173 .0202 .0063 .0092 8490
.0667 .005 .0049 .0070999999999999995 .0172 8519
.0052 .062400000000000004 -.013999999999999999 .0069 -.015600000000000001 8551
.030699999999999998 .0095 -.039 .0067 .018000000000000002 8581
-.0407 .015 .0562 .0074 -.0313 8610
-.005 -.042800000000000005 .0554 .0076 -.054299999999999994 8643
.0091 .0060999999999999995 .0101 .0076 -.0003 8673
-.0344 -.035699999999999996 .049699999999999994 .0076 -.0452 8704
.0216 .020099999999999996 -.0072 .006999999999999999 -.0013 8734
-.0178 -.0027 .0173 .0073 .0084 8764
-.0192 -.0038 .0758 .0076 -.0256 8796
-.0482 -.0169 .0333 .0070999999999999995 .0022 8825
.0063 .0007000000000000001 .0046 .0073 .010700000000000001 8855
-.0051 -.0116 .012 .008100000000000001 .0207 8886
-.059699999999999996 .0006 .0031 .0078000000000000005 .0151 8917
.0182 -.003 -.026600000000000002 .0075 -.006999999999999999 8946
-.0274 -.022099999999999998 .0036 .008199999999999999 .0285 8978
.10279999999999999 -.0024 -.0182 .0083 -.0559 9009
-.008 .0017000000000000001 .0528 .0086 .0362 9037
-.0084 -.0123 .0045000000000000005 .01 .0316 9070
-.0176 -.006500000000000001 .0406 .0073 .016399999999999998 9100
.0184 -.005600000000000001 -.0026 .0064 .0149 9131
.0799 .0331 -.0535 .006500000000000001 -.0682 9162
.012199999999999999 .0079 -.001 .0058 .018799999999999997 9190
-.0084 -.0108 .0407 .0062 .0158 9219
-.0096 .0014000000000000002 .037200000000000004 .0072 .0304 9251
.0509 -.0222 -.0096 .0066 .039900000000000005 9282
.0127 .0052 .0038 .0055000000000000005 .035699999999999996 9310
-.0074 .028399999999999998 -.016200000000000003 .0062 -.0391 9343
-.0102 -.003 .0232 .0055000000000000005 .018500000000000003 9373
-.0454 -.0159 .0129 .006 .0151 9404
.04019999999999999 -.0151 .0075 .006500000000000001 .048799999999999996 9435
.06480000000000001 .0025 -.0285 .0060999999999999995 -.004699999999999999 9464
.0388 -.0051 -.0154 .006500000000000001 -.0023 9496
.006500000000000001 .0121 .0044 .005600000000000001 .03 9527
.0713 -.0054 -.0072 .0053 .0276 9555
.048799999999999996 -.0059 -.0039000000000000003 .006 .0241 9586
-.0131 .0278 -.0287 .0052 -.0037 9616
.0462 -.013500000000000002 -.0021 .0049 .020499999999999997 9646
.0103 -.0096 .0128 .0052 .0507 9677
-.0645 -.0336 .047 .0052 .0183 9708
.060700000000000004 -.0417 .0351 .0046 -.0528 9737
-.086 .0236 .0322 .0045000000000000005 -.0579 9769
.0466 -.025 -.014199999999999999 .0046 .0446 9800
.011699999999999999 -.0191 -.0007000000000000001 .0039000000000000003 -.0026 9828
-.0327 .0013 .0036 .0049 .0034999999999999996 9861
.1247 -.0181 -.0316 .0042 .022000000000000002 9891
.043899999999999995 .0349 -.0591 .0043 -.0207 9919
.016399999999999998 .0045000000000000005 .0161 .004699999999999999 .0168 9951
-.021099999999999997 -.0169 -.0039000000000000003 .0044 .0022 9981
.0011 -.005 .0023 .0038 -.0074 10010
.0394 -.0212 .0104 .0048 -.0025 10042
.0385 -.0069 .0068000000000000005 .0046 .027200000000000002 10073
.0352 -.0077 -.009300000000000001 .004699999999999999 -.0086 10104
-.0259 .0054 .0027 .0045000000000000005 .0077 10134
-.2324 -.0843 .0424 .006 -.0786 10164
-.07769999999999999 .027200000000000002 .029500000000000002 .0034999999999999996 -.011699999999999999 10195
.0681 .0013 -.0443 .0039000000000000003 .0587 10226
.0421 -.0072 .0501 .0029 -.0724 10255
.0475 .0334 -.0171 .0046 -.0147 10286
-.0227 .061500000000000006 .0073 .0044 .0058 10317
.005600000000000001 .01 .0167 .0046 .022400000000000003 10346
end
format %td dateff
[/CODE]
Furthermore, from CRSP, I have the following data:
Is there any other data required for this?
I want to calculate the excess return using the Fama and French 3-factor model.
I want to do the following:
" To arrive at our estimate of the excess return, we use the 25 Fama and French portfolios formed on size and book-to-market as our benchmark portfolios. A portfolio return is a value-weighted return based on market capitalization within each of the 25 portfolios. For each year, we group every firm into one of 25 size and BE/ME portfolios based on the intersection between the size and book-to-market independent sorts. Fama and French (1993) conclude that size and the book-to-market of equity proxy for sensitivity to common risk factors in stock returns, which implies that stocks in different size and book-to-market portfolios may have different expected returns. Therefore, stock i’s benchmark return at year t is the return of the portfolio to which stock i belongs at the beginning of fiscal year t. To form a size- and BE/ME-excess return for any stock, we simply subtract the return of the portfolio to which it belongs from the realized return of the stock.
Futher Note: While the Fama and French 25 portfolios are formed at the end of each June, the fiscal yearend of a firm could be any month during the year. Therefore, a firm could change the portfolio to which it belongs during the year. Consider a firm whose fiscal year ends in December in year t − 1. From January to June of year t, it belongs to the portfolio according to the size and BE/ME breakpoints of year t − 1 and from July to December of year t, it belongs to the portfolio according to the size and BE/ME breakpoints of year t. Since we have value-weighted monthly returns of the portfolios, we calculate the benchmark return by annualizing the monthly returns from the portfolio it belongs to each month"
The dependent variable in our regression is the excess stock return, ri,t − RB i,t , where ri,t is the stock return for firm i during fiscal year t and RB i,t is stock i’s benchmark return at year t.
Could you assist me on this?
I have downloaded the following data for fama french 3 factor plus premium data for the period of Jan 1980- Dec 2022.
input double(mktrf smb hml rf umd) long dateff
.055099999999999996 .016200000000000003 .0175 .008 .0755 7335
-.012199999999999999 -.018500000000000003 .0060999999999999995 .0089 .0788 7364
-.129 -.0664 -.0101 .0121 -.0955 7395
.0397 .0105 .0106 .0126 -.0043 7425
.0526 .0213 .0038 .008100000000000001 -.011200000000000002 7455
.030600000000000002 .0166 -.0076 .0060999999999999995 .016200000000000003 7486
.0649 .0414 -.0641 .0053 .0039000000000000003 7517
.018000000000000002 .0392 -.026000000000000002 .0064 .0321 7546
.0219 .0098 -.045899999999999996 .0075 .053899999999999997 7578
.0106 .024700000000000003 -.0276 .0095 .073 7609
.0959 -.0336 -.0833 .0096 .1522 7637
-.0452 -.0026 .0279 .0131 -.0668 7670
-.0504 .0292 .0672 .0104 -.0795 7700
.005699999999999999 -.0034000000000000002 .0102 .010700000000000001 -.013500000000000002 7728
.0356 .0354 .0064 .0121 .0074 7760
-.021099999999999997 .044000000000000004 .022799999999999997 .0108 -.0091 7790
.0011 .02 -.0042 .0115 .037000000000000005 7819
-.0236 -.0083 .0513 .013500000000000002 -.0581 7851
-.0154 -.0219 -.005 .0124 -.0245 7882
-.0704 -.0195 .047599999999999996 .0128 -.0115 7913
-.0717 -.0265 .051699999999999996 .0124 .0195 7943
.0492 .0223 -.0421 .0121 .0401 7973
.0336 -.0103 .0183 .010700000000000001 -.0027 8004
-.0365 .012 .008100000000000001 .0087 .013000000000000001 8035
-.032400000000000005 -.0128 .0319 .008 .0172 8064
-.058600000000000006 .0044 .0605 .0092 .048799999999999996 8092
-.0187 -.0021 .0381 .0098 .0296 8125
.0327 .0147 -.027000000000000003 .0113 -.004 8155
-.039900000000000005 .0052 .0175 .0106 .025 8183
-.030899999999999997 -.004 .015300000000000001 .0096 .0496 8216
-.0319 .0083 .0009 .0105 .044500000000000005 8246
.1114 -.0414 .0095 .0076 -.0352 8278
.0129 .029500000000000002 .0028000000000000004 .0051 .04190000000000001 8308
.113 .023399999999999997 -.0366 .0059 .0018 8337
.0467 .0467 -.0187 .0063 .059500000000000004 8369
.0055000000000000005 -.0022 -.0002 .0067 .0004 8400
.036000000000000004 .0273 -.0075 .0069 -.0173 8431
.0259 .0327 .006999999999999999 .0062 .0377 8459
.0282 .0173 .0202 .0063 .0092 8490
.0667 .005 .0049 .0070999999999999995 .0172 8519
.0052 .062400000000000004 -.013999999999999999 .0069 -.015600000000000001 8551
.030699999999999998 .0095 -.039 .0067 .018000000000000002 8581
-.0407 .015 .0562 .0074 -.0313 8610
-.005 -.042800000000000005 .0554 .0076 -.054299999999999994 8643
.0091 .0060999999999999995 .0101 .0076 -.0003 8673
-.0344 -.035699999999999996 .049699999999999994 .0076 -.0452 8704
.0216 .020099999999999996 -.0072 .006999999999999999 -.0013 8734
-.0178 -.0027 .0173 .0073 .0084 8764
-.0192 -.0038 .0758 .0076 -.0256 8796
-.0482 -.0169 .0333 .0070999999999999995 .0022 8825
.0063 .0007000000000000001 .0046 .0073 .010700000000000001 8855
-.0051 -.0116 .012 .008100000000000001 .0207 8886
-.059699999999999996 .0006 .0031 .0078000000000000005 .0151 8917
.0182 -.003 -.026600000000000002 .0075 -.006999999999999999 8946
-.0274 -.022099999999999998 .0036 .008199999999999999 .0285 8978
.10279999999999999 -.0024 -.0182 .0083 -.0559 9009
-.008 .0017000000000000001 .0528 .0086 .0362 9037
-.0084 -.0123 .0045000000000000005 .01 .0316 9070
-.0176 -.006500000000000001 .0406 .0073 .016399999999999998 9100
.0184 -.005600000000000001 -.0026 .0064 .0149 9131
.0799 .0331 -.0535 .006500000000000001 -.0682 9162
.012199999999999999 .0079 -.001 .0058 .018799999999999997 9190
-.0084 -.0108 .0407 .0062 .0158 9219
-.0096 .0014000000000000002 .037200000000000004 .0072 .0304 9251
.0509 -.0222 -.0096 .0066 .039900000000000005 9282
.0127 .0052 .0038 .0055000000000000005 .035699999999999996 9310
-.0074 .028399999999999998 -.016200000000000003 .0062 -.0391 9343
-.0102 -.003 .0232 .0055000000000000005 .018500000000000003 9373
-.0454 -.0159 .0129 .006 .0151 9404
.04019999999999999 -.0151 .0075 .006500000000000001 .048799999999999996 9435
.06480000000000001 .0025 -.0285 .0060999999999999995 -.004699999999999999 9464
.0388 -.0051 -.0154 .006500000000000001 -.0023 9496
.006500000000000001 .0121 .0044 .005600000000000001 .03 9527
.0713 -.0054 -.0072 .0053 .0276 9555
.048799999999999996 -.0059 -.0039000000000000003 .006 .0241 9586
-.0131 .0278 -.0287 .0052 -.0037 9616
.0462 -.013500000000000002 -.0021 .0049 .020499999999999997 9646
.0103 -.0096 .0128 .0052 .0507 9677
-.0645 -.0336 .047 .0052 .0183 9708
.060700000000000004 -.0417 .0351 .0046 -.0528 9737
-.086 .0236 .0322 .0045000000000000005 -.0579 9769
.0466 -.025 -.014199999999999999 .0046 .0446 9800
.011699999999999999 -.0191 -.0007000000000000001 .0039000000000000003 -.0026 9828
-.0327 .0013 .0036 .0049 .0034999999999999996 9861
.1247 -.0181 -.0316 .0042 .022000000000000002 9891
.043899999999999995 .0349 -.0591 .0043 -.0207 9919
.016399999999999998 .0045000000000000005 .0161 .004699999999999999 .0168 9951
-.021099999999999997 -.0169 -.0039000000000000003 .0044 .0022 9981
.0011 -.005 .0023 .0038 -.0074 10010
.0394 -.0212 .0104 .0048 -.0025 10042
.0385 -.0069 .0068000000000000005 .0046 .027200000000000002 10073
.0352 -.0077 -.009300000000000001 .004699999999999999 -.0086 10104
-.0259 .0054 .0027 .0045000000000000005 .0077 10134
-.2324 -.0843 .0424 .006 -.0786 10164
-.07769999999999999 .027200000000000002 .029500000000000002 .0034999999999999996 -.011699999999999999 10195
.0681 .0013 -.0443 .0039000000000000003 .0587 10226
.0421 -.0072 .0501 .0029 -.0724 10255
.0475 .0334 -.0171 .0046 -.0147 10286
-.0227 .061500000000000006 .0073 .0044 .0058 10317
.005600000000000001 .01 .0167 .0046 .022400000000000003 10346
end
format %td dateff
[/CODE]
Furthermore, from CRSP, I have the following data:
Code:
* Example generated by -dataex-. For more info, type help dataex clear input double permno long date double(prc altprc) 10000 9496 . -2.5625 10000 9527 -4.375 -4.375 10000 9555 -3.25 -3.25 10000 9586 -4.4375 -4.4375 10000 9616 -4 -4 10000 9646 -3.109375 -3.109375 10000 9677 -3.09375 -3.09375 10000 9708 -2.84375 -2.84375 10000 9737 -1.09375 -1.09375 10000 9769 -1.03125 -1.03125 10000 9800 -.78125 -.78125 10000 9828 -.828125 -.828125 10000 9861 -.515625 -.515625 10000 9891 -.40625 -.40625 10000 9919 -.40625 -.40625 10000 9951 -.25 -.25 10000 9981 -.234375 -.234375 10000 10010 -.21875 -.21875 10000 10042 . -.21875 10001 9496 . -5.75 10001 9527 -6.125 -6.125 10001 9555 -6.25 -6.25 10001 9586 -6.3125 -6.3125 10001 9616 -6.375 -6.375 10001 9646 -6.3125 -6.3125 10001 9677 -6.125 -6.125 10001 9708 -6.0625 -6.0625 10001 9737 -6.5 -6.5 10001 9769 6.375 6.375 10001 9800 6.625 6.625 10001 9828 7 7 10001 9861 7 7 10001 9891 6.75 6.75 10001 9919 6.25 6.25 10001 9951 6.375 6.375 10001 9981 -6.125 -6.125 10001 10010 -5.6875 -5.6875 10001 10042 5.875 5.875 10001 10073 6 6 10001 10104 6.5 6.5 10001 10134 6.25 6.25 10001 10164 6.375 6.375 10001 10195 6.1875 6.1875 10001 10226 5.875 5.875 10001 10255 6.25 6.25 10001 10286 6.75 6.75 10001 10317 6.125 6.125 10001 10346 -6.3125 -6.3125 10001 10378 -6.4375 -6.4375 10001 10408 6.25 6.25 10001 10437 -6.4375 -6.4375 10001 10470 6.625 6.625 10001 10500 -6.375 -6.375 10001 10531 6.625 6.625 10001 10561 6.625 6.625 10001 10591 6.375 6.375 10001 10623 6.5 6.5 10001 10651 6.75 6.75 10001 10682 6.75 6.75 10001 10710 7.25 7.25 10001 10743 7 7 10001 10773 7 7 10001 10804 7.25 7.25 10001 10835 9.25 9.25 10001 10864 8.875 8.875 10001 10896 9.5 9.5 10001 10926 9.875 9.875 10001 10955 10.125 10.125 10001 10988 -9.9375 -9.9375 10001 11016 -9.875 -9.875 10001 11046 -9.875 -9.875 10001 11077 -9.875 -9.875 10001 11108 9.75 9.75 10001 11137 9.75 9.75 10001 11169 -10 -10 10001 11200 9.5 9.5 10001 11228 9.75 9.75 10001 11261 -9.625 -9.625 10001 11291 9.625 9.625 10001 11322 -9.5 -9.5 10001 11353 9.625 9.625 10001 11381 9.75 9.75 10001 11409 9.5 9.5 10001 11442 -9.875 -9.875 10001 11473 -9.875 -9.875 10001 11501 10.5 10.5 10001 11534 10.125 10.125 10001 11564 11.5 11.5 10001 11595 11.5 11.5 10001 11626 13 13 10001 11655 14.75 14.75 10001 11687 14.5 14.5 10001 11718 13.75 13.75 10001 11746 11 11 10001 11778 11.75 11.75 10001 11808 -11.875 -11.875 10001 11837 -12 -12 10001 11869 11.75 11.75 10001 11900 12.5 12.5 10001 11931 13 13 end format %td date
Is there any other data required for this?
