Calculating excess returns with preceding data

[Juan Gonzalex]

Hi there, I'm writing my master thesis. I have monthly returns and I would like to calculate the excess returns. To do this, I want to first estimate the average return of the fifth year prior to the observation year. So, for example, if the monthly return for stock X in March 2020 is 5%, I want Stata to take the average monthly return of the whole year 2015 (fifth year prior) and then substract it from the 5%. I have managed to calculate the average monthly returns per year with the following formula:

egen avg_return = mean(return), by(cusip year)

But I don't know how to tell Stata to substract the average monthly return of the whole fifth year prior to the observation. A sample of my data is as following:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte month int year str9 cusip double return float avg_return
 1 2007 "00130H105"   -5.671507   .4771828
 2 2007 "00130H105"   2.6480436   .4771828
 3 2007 "00130H105"   .93809366   .4771828
 4 2007 "00130H105"   2.1840096   .4771828
 5 2007 "00130H105"   7.9126835   .4771828
 6 2007 "00130H105"   -4.745322   .4771828
 7 2007 "00130H105"  -10.191954   .4771828
 8 2007 "00130H105"  -7.8371468   .4771828
 9 2007 "00130H105"   12.837839   .4771828
10 2007 "00130H105"    6.836319   .4771828
11 2007 "00130H105"    2.920401   .4771828
12 2007 "00130H105"  -2.1052659   .4771828
 1 2008 "00130H105"  -10.752684  -6.600425
 2 2008 "00130H105"   -4.208851  -6.600425
 3 2008 "00130H105"   -7.285869  -6.600425
 4 2008 "00130H105"    6.372559  -6.600425
 5 2008 "00130H105"   12.211979  -6.600425
 6 2008 "00130H105"  -1.3860404  -6.600425
 7 2008 "00130H105" -15.9812565  -6.600425
 8 2008 "00130H105"   -8.072292  -6.600425
 9 2008 "00130H105"    -23.3945  -6.600425
10 2008 "00130H105"  -31.822067  -6.600425
11 2008 "00130H105"  -2.0382166  -6.600425
12 2008 "00130H105"     7.15214  -6.600425
 1 2009 "00130H105"  -2.4660885   5.415221
 2 2009 "00130H105"  -27.335638   5.415221
 3 2009 "00130H105"  -7.7777805   5.415221
 4 2009 "00130H105"    25.13274   5.415221
 5 2009 "00130H105"    47.56278   5.415221
 6 2009 "00130H105"   16.216219   5.415221
 7 2009 "00130H105"   10.163652   5.415221
 8 2009 "00130H105"   6.8803787   5.415221
 9 2009 "00130H105"   6.4655185   5.415221
10 2009 "00130H105"  -11.808366   5.415221
11 2009 "00130H105"  -2.5248647   5.415221
12 2009 "00130H105"   4.4741035   5.415221
 1 2010 "00130H105"   -7.675439 -1.1598202
 2 2010 "00130H105"  -10.558534 -1.1598202
 3 2010 "00130H105"  -5.9024754 -1.1598202
 4 2010 "00130H105"   4.2457104 -1.1598202
 5 2010 "00130H105"  -12.071914 -1.1598202
 6 2010 "00130H105"  -10.029215 -1.1598202
 7 2010 "00130H105"   11.580097 -1.1598202
 8 2010 "00130H105"   -.6789565 -1.1598202
 9 2010 "00130H105"    9.134626 -1.1598202
10 2010 "00130H105"    4.828787 -1.1598202
11 2010 "00130H105"   -9.463978 -1.1598202
12 2010 "00130H105"  12.6734495 -1.1598202
 1 2011 "00130H105"   1.8062353   .1640612
 2 2011 "00130H105"           0   .1640612
 3 2011 "00130H105"   5.0929666   .1640612
 4 2011 "00130H105"   3.1152606   .1640612
 5 2011 "00130H105"  -2.1148026   .1640612
 6 2011 "00130H105"  -.15674233   .1640612
 7 2011 "00130H105"  -2.3015857   .1640612
 8 2011 "00130H105"  -11.779046   .1640612
 9 2011 "00130H105"   -8.356804   .1640612
10 2011 "00130H105"   14.959013   .1640612
11 2011 "00130H105"   3.6909938   .1640612
12 2011 "00130H105"   -1.986754   .1640612
 1 2012 "00130H105"    7.770276  -1.114432
 2 2012 "00130H105"   5.8547974  -1.114432
 3 2012 "00130H105"  -3.6135733  -1.114432
 4 2012 "00130H105"   -4.208106  -1.114432
end

I want Stata to take the last observation for example, which has a return of -4.21% and look at the fifth year prior, so 2007, and take the average return of 0.48% and substract it from the -4.21%. How can I do this?

Thanks

[Nick Cox]

Various ways to approach this. Here is one using rangestat from SSC.

rangestat wanted=return, int(year -5 -5) by(cusip)

[Juan Gonzalex]

Thank you very much, it works!

However, I have one more question. How can I take the average excess return for every four years without overlapping? So for example, for the years 2011-2014, calculate the average excess return for each cusip, then for the years 2015-2018 and so on. This should result in those observations from 2011-2014 having the same value.

Thanks

[Nick Cox]

You just to need to bin the years into four-year groups. Here is some technique. Now use egen to get means by cusip and the new variable.
See also https://journals.sagepub.com/doi/pdf...867X1801800311

. clear

. set obs 12
Number of observations (_N) was 0, now 12.

. gen year = 2010 + _n

. gen year4 = floor((year - 2011) / 4)

. l, sep(4)

     +--------------+
     | year   year4 |
     |--------------|
  1. | 2011       0 |
  2. | 2012       0 |
  3. | 2013       0 |
  4. | 2014       0 |
     |--------------|
  5. | 2015       1 |
  6. | 2016       1 |
  7. | 2017       1 |
  8. | 2018       1 |
     |--------------|
  9. | 2019       2 |
 10. | 2020       2 |
 11. | 2021       2 |
 12. | 2022       2 |
     +--------------+

.

[Juan Gonzalex]

Thanks!!