Converting CRSP monthly returns that start on April year t and ends at March year t+1 -to- annual returns

[Mike Kraft]

Dear All
I have a monthly return dataset (from CRSP) that I want to convert to annual returns.
The idea is that annual returns = (1+first month ret) * (1+ second month return) * ........(1+ the 12th month return) - 1
However, I want to calculate annual returns for each year such that the year-annual return starts from April the same year (i.e.after four months of year end) and ends at March of the next year.
My dataset is exactly as follows:

permno date ret 10001 29jan1988 .063829787
10001 29feb1988 .079999998
10001 31mar1988 -.0762963
10001 29apr1988 .030612245
10001 31may1988 .01980198
10001 30jun1988 -.012038835
10001 29jul1988 .029999999
10001 31aug1988 .029126214
10001 30sep1988 -.021132076
10001 31oct1988 .039215688
10001 30nov1988 0
10001 30dec1988 -.021132076
10001 31jan1989 .019607844
10001 28feb1989 .03846154
10001 31mar1989 .017777778
10001 28apr1989 .074074075
10001 31may1989 -.034482758
10001 30jun1989 .017142856
10001 31jul1989 .035714287
.
.
.
10002 29jan1988 .037500001
10002 29feb1988 -.036144577
10002 31mar1988 .025
10002 29apr1988 -.048780486
10002 31may1988 -.012820513
10002 30jun1988 .025974026
10002 29jul1988 -.012658228
10002 31aug1988 -.025641026
10002 30sep1988 -.013157895
10002 31oct1988 0
10002 30nov1988 -.06666667
10002 30dec1988 -.042857144
10002 31jan1989 .059701491
10002 28feb1989 -.014084507
10002 31mar1989 -.028571429
10002 28apr1989 -.029411765
10002 31may1989 -.030303031
10002 30jun1989 .046875

and so on.....

permno is the stock identifier


I have posted a similar question before and got some feedback to be clear in my question. Hope this is clear to most of you.

Thanks in advance

[Mike Kraft]

I also asked a similar question on Friday night few weeks ago, and didn't get an answer.....Hope it will work this time. Again also on Friday

Thanks

[Clyde Schechter]

So the idea is to create a "fiscal year" variable that runs from 4/1 through 3/31 of the next calendar year. I assume the date variable is an actual Stata date variable, not a string that is human-readable as a date.

[code]
gen int fy = year(date)
replace fy = fy-1 if month(date) < 4
[/code

Then you want to create your annual return over those fiscal years. Calculating the product within groups is most easily done by exponentiating the sum of logarithms. This will fail if any of the values of ret are -1 or smaller.

gen log_one_plus_return = log(1+ret)
egen annual_return = total(log_one_plus_return), by(permno fy)
replace annual_return = exp(annual_return) - 1

should do it.

It looks as if you do not have complete data for all 12 months of each of these fiscal years. The code above will calculate the cumulative return for as much of the year as is represented in the data. I don't know how you should handle those gaps, though.

[Ayub UOM]

Hello statalist members ,
i am also interested to calculate cumulative return, but i don't know how to manage the date and to sum monthly return from April of the current year to next year march of next year.
sir could you please guide me.ym is my stata date i.e 2019m9

* Example generated by -dataex-. To install: ssc install dataex
clear
input long code str7 tradingmonth float Monthlyret int year byte month float ym
2 "2019-09"  .003876 2019  9 716
2 "2019-08" -.067328 2019  8 715
2 "2019-07"   .03416 2019  7 714
2 "2019-06"  .041573 2019  6 713
2 "2019-05" -.075485 2019  5 712
2 "2019-04" -.059896 2019  4 711
2 "2019-03"  .097535 2019  3 710
2 "2019-02"  .008649 2019  2 709
2 "2019-01"  .164987 2019  1 708
2 "2018-12" -.063679 2018 12 707
2 "2018-11"  .049938 2018 11 706
2 "2018-10" -.002881 2018 10 705
2 "2018-09"  .010395 2018  9 704
2 "2018-08"  .071976 2018  8 703
2 "2018-07" -.052033 2018  7 702
2 "2018-06" -.038687 2018  6 701
2 "2018-05" -.098944 2018  5 700
2 "2018-04" -.146891 2018  4 699
2 "2018-03"  .018043 2018  3 698
2 "2018-02" -.129393 2018  2 697
2 "2018-01"  .209272 2018  1 696
2 "2017-12" -.005125 2017 12 695
2 "2017-11"  .078039 2017 11 694
2 "2017-10"  .103238 2017 10 693
2 "2017-09"  .130004 2017  9 692
2 "2017-08"  .027624 2017  8 691
2 "2017-07" -.064077 2017  7 690
2 "2017-06"  .174506 2017  6 689
2 "2017-05"  .090256 2017  5 688
2 "2017-04" -.052478 2017  4 687
2 "2017-03"  .002435 2017  3 686
2 "2017-02" -.007253 2017  2 685
2 "2017-01"  .006326 2017  1 684
2 "2016-12" -.238325 2016 12 683
2 "2016-11"  .082665 2016 11 682
2 "2016-10" -.047765 2016 10 681
2 "2016-09"  .049739 2016  9 680
2 "2016-08"  .454492 2016  8 679
2 "2016-07" -.268932 2016  7 678
2 "2015-12"  .621102 2015 12 671
2 "2015-11"  .102414 2015 11 670
2 "2015-10"  .073841 2015 10 669
2 "2015-09" -.077536 2015  9 668
2 "2015-08" -.026798 2015  8 667
2 "2015-07"  .010236 2015  7 666
2 "2015-06"  .011847 2015  6 665
2 "2015-05" -.017123 2015  5 664
2 "2015-04"   .05644 2015  4 663
2 "2015-03"  .083922 2015  3 662
2 "2015-02" -.028201 2015  2 661
2 "2015-01" -.056115 2015  1 660
2 "2014-12"  .275229 2014 12 659
2 "2014-11"  .159574 2014 11 658
2 "2014-10"  .023965 2014 10 657
2 "2014-09"  .006579 2014  9 656
2 "2014-08" -.078788 2014  8 655
2 "2014-07"  .197098 2014  7 654
2 "2014-06" -.032749 2014  6 653
2 "2014-05"  .142649 2014  5 652
2 "2014-04" -.023486 2014  4 651
2 "2014-03"  .203869 2014  3 650
2 "2014-02" -.089431 2014  2 649
2 "2014-01" -.080946 2014  1 648
2 "2013-12" -.083333 2013 12 647
2 "2013-11"  -.04886 2013 11 646
2 "2013-10"  .008762 2013 10 645
2 "2013-09" -.034884 2013  9 644
2 "2013-08" -.006303 2013  8 643
2 "2013-07" -.033503 2013  7 642
2 "2013-06" -.177796 2013  6 641
2 "2013-05"  .102837 2013  5 640
2 "2013-04"  .025093 2013  4 639
2 "2013-03" -.103333 2013  3 638
2 "2013-02" -.000833 2013  2 637
2 "2013-01"  .186759 2013  1 636
2 "2012-12"  .156571 2012 12 635
2 "2012-11"  .051683 2012 11 634
2 "2012-10" -.013049 2012 10 633
2 "2012-09"  .051122 2012  9 632
2 "2012-08" -.131094 2012  8 631
2 "2012-07"   .05041 2012  7 630
2 "2012-06" -.036757 2012  6 629
2 "2012-05"  .030067 2012  5 628
2 "2012-04"  .084541 2012  4 627
2 "2012-03"        0 2012  3 626
2 "2012-02"  .082353 2012  2 625
2 "2012-01"  .024096 2012  1 624
2 "2011-12"  .058074 2011 12 623
2 "2011-11"   -.1175 2011 11 622
2 "2011-10"  .104972 2011 10 621
2 "2011-09" -.111656 2011  9 620
2 "2011-08"        0 2011  8 619
2 "2011-07" -.035503 2011  7 618
2 "2011-06"  .077806 2011  6 617
2 "2011-05" -.063769 2011  5 616
2 "2011-04" -.024166 2011  4 615
2 "2011-03"  .064951 2011  3 614
2 "2011-02" -.004878 2011  2 613
2 "2011-01" -.002433 2011  1 612
2 "2010-12"   .01107 2010 12 611
end
format %tm ym

Thank you in advance

[Ayub UOM]

Clyde Schechter Hello Sir,
sir could you please guide.me regarding my previous post #4
thank you in advance

[Clyde Schechter]

So there are two issues. The first is that the grouping is not calendar year but instead runs from April of one calendar year through March of another. This is solved by creating a "fake" year which is based on the offset of ym by 3 months.

The next issue is how to calculated total returns. In #4 you seem to want just the simple sum, so that is what the code below gives. But that would be different from the actual return of something that was held over those periods because of compounding. Nevertheless, I am not a financier, nor do I know precisely what your purpose here is, so we'll leave it at that:

gen fake_year = year(dofm(ym-3))

by code fake_year (ym), sort: egen total_return = total(Monthlyret)

[Ayub UOM]

Thank you so much sir sir, it can just sum the monthly return, and i need commutative monthly return, so i will reask this question on my other previous post,hopefully i will get some other response.
https://www.statalist.org/forums/for...ulative-return