Calculatiing cumulative return

[Linh Nguyen]

Hi everyone,

I want to calculate the cumulative return, which t0=1/30/2020 is the first day, as follows:

cum_re (at t=0) = return (at t=0)
cum_re (at t=1) = [(1 + return (at t=0)) * (1 + return (at t=1))]^(1/2) - 1
cum_re (at t=2) = [(1 + return (at t=0)) * (1 + return (at t=1)) * (1 + return (at t=2))]^(1/3) - 1
...
cum_re (at t=10)

However, I don't know how to code it in Stata.

I would really appreciate all the help I can get.

Best regards

P/s: I attache the small sample for example.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int company_id float(date return t cum_re)
1 21937   .004804236 -5 .
1 21938 -.0028854576 -4 .
1 21941  -.029846454 -3 .
1 21942   .027896367 -2 .
1 21943    .02071675 -1 .
1 21944 -.0014503612  0 .
1 21945   -.04535168  1 .
1 21948  -.002750865  2 .
1 21949   .032480955  3 .
1 21950   .008121479  4 .
1 21951   .011628815  5 .
1 21952  -.013652723  6 .
1 21955   .004738188  7 .
1 21956    -.0060511  8 .
1 21957    .02346898  9 .
1 21958  -.007145829 10 .
2 21937   .006136745 -5 .
2 21938  -.010127876 -4 .
2 21941  -.016864497 -3 .
2 21942    .01940629 -2 .
2 21943   .015472523 -1 .
2 21944    .02781715  0 .
2 21945  -.014868793  1 .
2 21948    .02408653  2 .
2 21949   .032386478  3 .
2 21950 -.0012224098  4 .
2 21951    .02052206  5 .
2 21952  .0014147813  6 .
2 21955   .025820585  7 .
2 21956   -.02283419  8 .
2 21957  .0014628787  9 .
2 21958  -.005428527 10 .
3 21937 -.0015270904 -5 .
3 21938  -.012246813 -4 .
3 21941  -.018049529 -3 .
3 21942    .01353241 -2 .
3 21943   .002559859 -1 .
3 21944   .006800847  0 .
3 21945    .07119624  1 .
3 21948 -.0022526423  2 .
3 21949    .02243314  3 .
3 21950  -.004792484  4 .
3 21951   .005066657  5 .
3 21952   .014068715  6 .
3 21955   .025935084  7 .
3 21956    .00788418  8 .
3 21957   .004267564  9 .
3 21958  -.004700152 10 .
end
format %tdnn/dd/CCYY date

[Attaullah Shah]

This is the case of the geometric mean. You can use asrol for this. asrol can be downloaded from SSC

ssc install asrol

gen cumret1 = return if t == 0

bys company_id : asrol return if inrange(t, 0, 1), gen(cumret2) stat(gmean) add(1)

bys company_id : asrol return if inrange(t, 0,  2), gen(cumret3) stat(gmean) add(1)

drop if cumret3 == .

list

     +-------------------------------------------------------------------------------------+
     | compan~d        date      return   t   cum_re     cumret1      cumret2      cumret3 |
     |-------------------------------------------------------------------------------------|
  1. |        1   1/30/2020   -.0014504   0        .   -.0014504   -.02364774   -.01673123 |
  2. |        1   1/31/2020   -.0453517   1        .           .   -.02364774   -.01673123 |
  3. |        1    2/3/2020   -.0027509   2        .           .            .   -.01673123 |
  4. |        2   1/30/2020    .0278172   0        .    .0278172    .00624786    .01215929 |
  5. |        2   1/31/2020   -.0148688   1        .           .    .00624786    .01215929 |
     |-------------------------------------------------------------------------------------|
  6. |        2    2/3/2020    .0240865   2        .           .            .    .01215929 |
  7. |        3   1/30/2020    .0068008   0        .    .0068008    .03849954    .02473381 |
  8. |        3   1/31/2020    .0711962   1        .           .    .03849954    .02473381 |
  9. |        3    2/3/2020   -.0022526   2        .           .            .    .02473381 |
     +-------------------------------------------------------------------------------------+

You can read more about asrol here https://fintechprofessor.com/stata-p...tics-in-stata/

Further variations on geometric mean are here https://fintechprofessor.com/2018/12...oups-in-stata/

[Linh Nguyen]

Thanks Attaullah Shah.

However, if I want to calculate cumulative return in 2 months (~ 60 days), I have to write the following code 60 times, don't I?

 
 bys company_id : asrol return if inrange(t, 0, xx), gen(cumret3) stat(gmean) add(1)

Do you know how I can shorten this code? by loop, for example?

Best regards,

[Dung Le]

Thanks Attaullah Shah.

However, if I want to calculate cumulative return in 2 months (~ 60 days), I have to write the following code 60 times, don't I?

bys company_id : asrol return if inrange(t, 0, xx), gen(cumret3) stat(gmean) add(1)

Do you know how I can shorten this code? by loop, for example?

Best regards,

Hi Linh Nguyen,

Try the following codes. Note that in the codes below I dropped all t values that are negative for simplicity so now we have only t0-t10

drop if t<0
foreach x of varlist return {
    forval i = 0(1)10 {
        bys company_id: asrol `x' if inrange(t, 0, `i'), gen(`x'`i'cum) stat(gmean) add(1)
    }
}

Hope this helps!

DL

[Linh Nguyen]

Thanks Dung Le.

I will try it now .

[Romalpa Akzo]

I guess you just need one variable.

bys company_id (date): gen double Wanted = exp(sum(ln(1+return))/(t+1)) - 1 if t>=0

[Attaullah Shah]

In reply to the question

if I want to calculate cumulative return in 2 months (~ 60 days)

there is no need to run loop, just use the options window for this

bys company_id: asrol return if t>-1, gen(cumret) stat(gmean) add(1) window(t 60)
list in 1/10

     +----------------------------------------------------+
     | compan~d        date      return    t       cumret |
     |----------------------------------------------------|
  1. |        1   1/23/2020    .0048042   -5            . |
  2. |        1   1/24/2020   -.0028855   -4            . |
  3. |        1   1/27/2020   -.0298465   -3            . |
  4. |        1   1/28/2020    .0278964   -2            . |
  5. |        1   1/29/2020    .0207167   -1            . |
     |----------------------------------------------------|
  6. |        1   1/30/2020   -.0014504    0   -.00145036 |
  7. |        1   1/31/2020   -.0453517    1   -.02364774 |
  8. |        1    2/3/2020   -.0027509    2   -.01673123 |
  9. |        1    2/4/2020     .032481    3   -.00465257 |
 10. |        1    2/5/2020    .0081215    4   -.00211078 |
     +----------------------------------------------------+

.

[Linh Nguyen]

Attaullah Shah:
I check your code in #7 manually, and this is what I need. Thank you so much.