Cumulative 48 month returns after portfolio formation

[Fawad Ahmad]

Hello everyone,

I am trying to create a line graph showing the average cumulative size-adjusted return 48 months after portfolio formation, as illustrated in the graph I shared. The portfolio formation month is July. My question is how I can use the data provided below to calculate the average cumulative return for the 48 months following the portfolio formation (i.e., July) for each year. Once I have that information, I would like to create a graph similar to the one in the attached image. Thank you!

mdate	VHGL	VLGH
1975m7	23.6409	-1.85747
1975m8	31.54199	0.73268
1975m9	0.954142	-4.01983
1975m10	-1.535	0.88313
1975m11	-14.7582	1.010599
1975m12	-6.11982	1.342863
1976m1	-2.90207	1.868955
1976m2	-1.6811	0.360809
1976m3	-8.87342	-0.44749
1976m4	-9.57213	-0.64756
1976m5	-4.56338	-1.31126
1976m6	9.151813	0.655089
1976m7	6.384994	0.97325
1976m8	4.011305	-0.91055
1976m9	-1.86398	0.414956
1976m10	2.538613	1.340248
1976m11	0.792573	2.22174
1976m12	-5.66595	-0.20418
1977m1	0.436415	-0.24952
1977m2	-2.13109	-1.79169
1977m3	-0.66175	0.798705
1977m4	1.063871	0.077594
1977m5	-0.77494	-0.02389
1977m6	-1.59114	-0.74734
1977m7	2.465556	2.369507
1977m8	3.427579	-0.48278
1977m9	3.805694	0.61698
1977m10	4.206738	-1.27621
1977m11	5.445038	-0.23388
1977m12	3.110342	-2.30217
1978m1	-4.13888	-0.27477
1978m2	13.72831	-0.72537
1978m3	1.768703	0.489982
1978m4	-15.8859	1.186175
1978m5	-7.0416	-1.66244
1978m6	-9.17977	-0.50811
1978m7	12.39175	-0.03046
1978m8	-1.16092	1.454933
1978m9	-3.75086	-1.01483
1978m10	3.287768	-0.43056
1978m11	2.223697	-0.07466
1978m12	-4.74282	0.388672
1979m1	0.071485	2.665838
1979m2	0.480683	-4.52052
1979m3	-3.76925	-0.88655
1979m4	-2.79804	0.892778
1979m5	-6.82006	1.924172
1979m6	-5.40483	-0.9567
1979m7	6.608725	-3.16023
1979m8	-0.50962	-3.24603
1979m9	-11.3123	5.229182
1979m10	-5.6694	-1.82821
1979m11	-2.07615	0.633353
1979m12	-3.51627	-1.62592
1980m1	-5.21505	-2.03005
1980m2	-1.71093	-4.16325
1980m3	-1.8438	-2.90885
1980m4	-4.18158	-6.87552
1980m5	-15.9804	-3.34684
1980m6	-1.23333	0.70561
1980m7	10.46757	3.781955
1980m8	4.214205	3.598901
1980m9	4.376321	2.136222
1980m10	6.718448	-3.45151
1980m11	4.507873	-0.19619
1980m12	10.46925	5.664802
1981m1	-5.11891	3.950102
1981m2	-0.82537	7.439048
1981m3	7.271362	10.5414
1981m4	-7.52433	-6.88301
1981m5	7.763652	5.372219
1981m6	3.728716	1.938912
1981m7	7.520769	-0.67035
1981m8	15.36724	9.905046
1981m9	7.32041	-0.86461
1981m10	-0.15662	-12.0911
1981m11	4.564418	4.078289
1981m12	9.424789	7.016879
1982m1	5.205188	1.950946
1982m2	-6.30241	7.080321
1982m3	7.726932	-2.50636
1982m4	-2.19646	-7.01274
1982m5	11.77144	-9.1987
1982m6	3.176275	-5.1605
1982m7	8.859365	-17.653
1982m8	10.47657	-4.86719
1982m9	6.899945	2.123599
1982m10	3.54281	-4.06846
1982m11	3.319257	-7.34575
1982m12	2.878529	-2.40194
1983m1	1.894752	8.880918
1983m2	-1.3308	5.806592
1983m3	2.448322	6.564387
1983m4	5.336121	11.92559
1983m5	-1.42986	3.70648
1983m6	2.887483	6.772998
1983m7	10.02269	4.222769

[Clyde Schechter]

You don't explain what the variables in your example data are and how they relate to your question. I'm going to guess that VHGL and VLGH represent two separate portfolios, and that the values of those variables represent the monthly returns in percents. If that is correct, then

* Example generated by -dataex-. For more info, type help dataex
clear
input float(mdate vhgl vlgh)
186  23.6409 -1.85747
187 31.54199   .73268
188  .954142 -4.01983
189   -1.535   .88313
190 -14.7582 1.010599
191 -6.11982 1.342863
192 -2.90207 1.868955
193  -1.6811  .360809
194 -8.87342  -.44749
195 -9.57213  -.64756
196 -4.56338 -1.31126
197 9.151813  .655089
198 6.384994   .97325
199 4.011305  -.91055
200 -1.86398  .414956
201 2.538613 1.340248
202  .792573  2.22174
203 -5.66595  -.20418
204  .436415  -.24952
205 -2.13109 -1.79169
206  -.66175  .798705
207 1.063871  .077594
208  -.77494  -.02389
209 -1.59114  -.74734
210 2.465556 2.369507
211 3.427579  -.48278
212 3.805694   .61698
213 4.206738 -1.27621
214 5.445038  -.23388
215 3.110342 -2.30217
216 -4.13888  -.27477
217 13.72831  -.72537
218 1.768703  .489982
219 -15.8859 1.186175
220  -7.0416 -1.66244
221 -9.17977  -.50811
222 12.39175  -.03046
223 -1.16092 1.454933
224 -3.75086 -1.01483
225 3.287768  -.43056
226 2.223697  -.07466
227 -4.74282  .388672
228  .071485 2.665838
229  .480683 -4.52052
230 -3.76925  -.88655
231 -2.79804  .892778
232 -6.82006 1.924172
233 -5.40483   -.9567
234 6.608725 -3.16023
235  -.50962 -3.24603
236 -11.3123 5.229182
237  -5.6694 -1.82821
238 -2.07615  .633353
239 -3.51627 -1.62592
240 -5.21505 -2.03005
241 -1.71093 -4.16325
242  -1.8438 -2.90885
243 -4.18158 -6.87552
244 -15.9804 -3.34684
245 -1.23333   .70561
246 10.46757 3.781955
247 4.214205 3.598901
248 4.376321 2.136222
249 6.718448 -3.45151
250 4.507873  -.19619
251 10.46925 5.664802
252 -5.11891 3.950102
253  -.82537 7.439048
254 7.271362  10.5414
255 -7.52433 -6.88301
256 7.763652 5.372219
257 3.728716 1.938912
258 7.520769  -.67035
259 15.36724 9.905046
260  7.32041  -.86461
261  -.15662 -12.0911
262 4.564418 4.078289
263 9.424789 7.016879
264 5.205188 1.950946
265 -6.30241 7.080321
266 7.726932 -2.50636
267 -2.19646 -7.01274
268 11.77144  -9.1987
269 3.176275  -5.1605
270 8.859365  -17.653
271 10.47657 -4.86719
272 6.899945 2.123599
273  3.54281 -4.06846
274 3.319257 -7.34575
275 2.878529 -2.40194
276 1.894752 8.880918
277  -1.3308 5.806592
278 2.448322 6.564387
279 5.336121 11.92559
280 -1.42986  3.70648
281 2.887483 6.772998
282 10.02269 4.222769
end
format %tm mdate

tsset mdate
foreach v of varlist vhgl vlgh {
    gen `v'_cum = 1+`v'/100 in 1
    replace `v'_cum = L1.`v'_cum * (1+`v'/100) in 2/L
    replace `v'_cum = 100*(`v'_cum - 1)
}

will calculate the cumulative return series, also in percents. As for the graph, I don't know what you want. The graph you show has three curves on it and their labels are names that don't seem to be relatable to your data. Moreover the graph has three curves plotted, and I don't know how you want to derive a third curve from the two return series. Also, you refer to a 48 month period, but your data extends for 97 months, twice as long. I don't know what your intent is here, but the code above just calculates the cumulative returns for the entire series.

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Fawad Ahmad]

Thanks, Clyde, for your answer. So thank you, Clyde, for your response. I apologize for not providing complete information earlier. You are correct that VHGL and VLGH are two investment strategies. However, the graph I have shows three curves representing three different investment strategies, while my data focuses specifically on the two strategies. I am looking for a graph with only two curves.

To clarify, my data spans 72 months, and portfolios are developed each year in July. The graph reflects the returns for each portfolio over a four-year period, which means it displays the cumulative returns for strategies starting from the portfolio development dates up to 48 months later. Each year, portfolios are rebalanced, and the cumulative return is calculated over the subsequent 48 months.

For example, the portfolio formulated in July 1975 (1975m7) shows the cumulative returns from that date until June 1979 (1979m6). The next portfolio formation date is July 1976 (1976m7), and its returns are calculated from that date through June 1980 (1980m6), and so on. In this way, cumulative returns for each portfolio formation date are calculated. Ultimately, all the cumulative returns for the different portfolios over 48 months are combined to derive the curves.

Thanks in advnace.

[Clyde Schechter]

OK. The calculation of the cumulative return itself doesn't change. The trick is to start a 48 month window at each July and move that forward by 1 year.

capture program drop one_portfolio
program define one_portfolio
    tsset mdate
    foreach v of varlist vhgl vlgh {
        gen `v'_cum = 1+`v'/100 in 1
        replace `v'_cum = L1.`v'_cum * (1+`v'/100) in 2/L
        replace `v'_cum = 100*(`v'_cum - 1)
        replace `v'_cum = `v'_cum[_N]
    }
    exit
end

isid mdate, sort
gen start = cond(month(dofm(mdate)) == 7, mdate, 1)
gen stop = cond(month(dofm(mdate)) == 7, mdate + 47, 0)
rangerun one_portfolio, interval(mdate start stop)
drop start stop

-rangerun- is written by Robert Picard and is available from SSC. To use it, you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also available from SSC.

The variables start and stop tell -rangerun- which observations start a new window and what its endpoint is. The final result contains the variables vhgl_cum and vlgh_cum, which contain the cumulative 48 month returns over the following 48 months in the observations that are the start of a rebalancing.