Average portfolio return over ten years following the initial year of R&D spending

[Alexander Schmidt]

Dear all,

I have time series for 3 stock portfolios ("0","5" and "10") with data for R&D spending ("pfxrd") and portfolio return ("pfrt"). For each Portfolio ("pf") I want to calculate the average portfolio return over ten years following the initial year of R&D spending. I will provide the data and elaborate a bit more on it then:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(year pf pfxrd pfrt)
1962 0         0  2.0433624
1963 0         0   3.235416
1964 0         0  2.0289717
1965 0         0   4.715116
1966 0       .77   2.840973
1967 0       .48    .876097
1968 0      1.41  2.4592624
1969 0      7.08  -.6605892
1970 0         0   .4436268
1971 0         0   2.881102
1972 0         0  1.0571196
1973 0         0  -3.090473
1974 0         0 -2.3816926
1975 0         0   5.344191
1976 0         0   2.180875
1977 0      4.44   .6919739
1978 0     6.248   1.340753
1979 0     8.422  2.3498313
1980 0    12.246   3.059075
1981 0    13.362   .8223675
1982 0    19.994  4.2377257
1983 0    34.747  2.1897767
1984 0    49.399 -.07765996
1985 0    54.386   2.788236
1986 0   109.911  1.9860017
1987 0   296.832   1.192912
1988 0  7507.789   2.677546
1989 0  7573.345  1.8918866
1990 0  8001.991   .7434589
end
format %ty year

Example: In 1966 the R&D spending of portfolio 0 was .77. I would like to get the portfolio return for 1 year after that date, 2 years after that date,....and 10 years after that date. This should be done for each portfolio and each year where R&D spending is positive. After that I would like to calculate the mean values of those returns for 1-10 years after R&D spending. Can anybody provide some advice, please?

Any help is much appreciated.

Best,
Alex

[Nick Cox]

Your rules seem contradictory to me. Sure, R and D spending starts in 1966 but spending is not positive in all the following ten years. However, this shows some technique. If you want the first string of years with ten consecutive positive spending, the code will differ.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(year pf pfxrd pfrt)
1962 0         0  2.0433624
1963 0         0   3.235416
1964 0         0  2.0289717
1965 0         0   4.715116
1966 0       .77   2.840973
1967 0       .48    .876097
1968 0      1.41  2.4592624
1969 0      7.08  -.6605892
1970 0         0   .4436268
1971 0         0   2.881102
1972 0         0  1.0571196
1973 0         0  -3.090473
1974 0         0 -2.3816926
1975 0         0   5.344191
1976 0         0   2.180875
1977 0      4.44   .6919739
1978 0     6.248   1.340753
1979 0     8.422  2.3498313
1980 0    12.246   3.059075
1981 0    13.362   .8223675
1982 0    19.994  4.2377257
1983 0    34.747  2.1897767
1984 0    49.399 -.07765996
1985 0    54.386   2.788236
1986 0   109.911  1.9860017
1987 0   296.832   1.192912
1988 0  7507.789   2.677546
1989 0  7573.345  1.8918866
1990 0  8001.991   .7434589
end
format %ty year

egen first = min(cond(pfxrd > 0, year, .)), by(pf) 
gen after = year - first if inrange(year - first, 1, 10) 
egen total = total(cond(inrange(after, 1, 10), pfxrd, .)), by(pf) 

list 

     +----------------------------------------------------------+
     | year   pf      pfxrd        pfrt   first   after   total |
     |----------------------------------------------------------|
  1. | 1962    0          0    2.043362    1966       .    8.97 |
  2. | 1963    0          0    3.235416    1966       .    8.97 |
  3. | 1964    0          0    2.028972    1966       .    8.97 |
  4. | 1965    0          0    4.715116    1966       .    8.97 |
  5. | 1966    0        .77    2.840973    1966       .    8.97 |
     |----------------------------------------------------------|
  6. | 1967    0        .48     .876097    1966       1    8.97 |
  7. | 1968    0       1.41    2.459262    1966       2    8.97 |
  8. | 1969    0       7.08   -.6605892    1966       3    8.97 |
  9. | 1970    0          0    .4436268    1966       4    8.97 |
 10. | 1971    0          0    2.881102    1966       5    8.97 |
     |----------------------------------------------------------|
 11. | 1972    0          0     1.05712    1966       6    8.97 |
 12. | 1973    0          0   -3.090473    1966       7    8.97 |
 13. | 1974    0          0   -2.381693    1966       8    8.97 |
 14. | 1975    0          0    5.344191    1966       9    8.97 |
 15. | 1976    0          0    2.180875    1966      10    8.97 |
     |----------------------------------------------------------|
 16. | 1977    0       4.44    .6919739    1966       .    8.97 |
 17. | 1978    0      6.248    1.340753    1966       .    8.97 |
 18. | 1979    0      8.422    2.349831    1966       .    8.97 |
 19. | 1980    0     12.246    3.059075    1966       .    8.97 |
 20. | 1981    0     13.362    .8223675    1966       .    8.97 |
     |----------------------------------------------------------|
 21. | 1982    0     19.994    4.237726    1966       .    8.97 |
 22. | 1983    0     34.747    2.189777    1966       .    8.97 |
 23. | 1984    0     49.399     -.07766    1966       .    8.97 |
 24. | 1985    0     54.386    2.788236    1966       .    8.97 |
 25. | 1986    0    109.911    1.986002    1966       .    8.97 |
     |----------------------------------------------------------|
 26. | 1987    0    296.832    1.192912    1966       .    8.97 |
 27. | 1988    0   7507.789    2.677546    1966       .    8.97 |
 28. | 1989    0   7573.345    1.891887    1966       .    8.97 |
 29. | 1990    0   8001.991    .7434589    1966       .    8.97 |
     +----------------------------------------------------------+

. 
.

[Alexander Schmidt]

Thank you Nick! Worked very well.