Calculation of Cumulative 3-year returns based on annual return data

Occasional Contributor
Posts: 14

Calculation of Cumulative 3-year returns based on annual return data

Hello,

I am new to this message board, and I appreciate the help provided on my first question that I posted earlier (How to calculate annual compoundin​g returns based on monthly return data starting with June month).  Now that I have the annual return data by stock and date (July to June annual intervals), I want to do the following:

- Calculate 3-year cumulative returns by stock and date - specifically, I want to calculate the 3-year returns for the prior 3 years (label it as Formation Return) and also the 3-year returns for the next 3 years (label it as Evaluation Return).

Therefore, each stock and date combination will have a Formation Return and an Evaluation Return.

Can someone please show me how me the SAS code, but WITHOUT using PROC SQL?

Thanks again!

Super User
Posts: 23,776

Re: Calculation of Cumulative 3-year returns based on annual return data

Please post sample data and expected output.

Please define how are you calculating 3 year return - you had monthly data previously.

Occasional Contributor
Posts: 14

Re: Calculation of Cumulative 3-year returns based on annual return data

Good points - thanks.  In terms of sample data, here it is:

 CUSIP DATE Annret 00016510 19840629 0.045 00016510 19850628 -0.08964 00035410 19840629 -0.24608 00035410 19850628 -0.32526 00036110 19800630 -0.11823 00036110 19810630 0.366891 00036110 19820630 -0.37803 00036110 19830630 1.203394 00036110 19840629 0.338852 00036110 19850628 -0.02172 00037010 19800630 0.057132 00037010 19810630 1.928082 00037010 19820630 -0.41945 00057310 19850628 -0.4663 00077110 19850628 0.184763 00077410 19800630 -0.19 00077410 19810630 -0.05556 00077410 19820630 -0.69907 00077410 19830630 -0.06667 00077410 19840629 -0.21429 00077P10 19840629 0.316407 00077P10 19850628 0.031313 00078110 19820630 -0.21633 00078110 19830630 0.333333 00078110 19840629 0.281444 00078110 19850628 0.533266

So, for each CUSIP and date, I want to see the following - just an example:

CUSIP               DATE           ANNRET          FORMRET           EVALRET

00016510        19840629       0.045                   0.045                  -0.0486

FORMRET = the cumulative return for the last 3 years; therefore, it would have to use a lag function?  If there is no full data for last 3 years, then just provide the cumulative return for the number of years available.  In the example above, since there was no prior annual data than 1984029 for CUSIP 00016510, the FORMRET is equal to the ANNRET.

EVALRET = the cumulative return fo the next 3 years.  The cumulative returns are calculated by ((1+Annret1)*(1+Annret2)*(1+Annret3))-1.  If there is no full data for the next 3 years, then just provide the cumulative return for the number of years available.

Thank you again and look forward to hearing from you.

Super User
Posts: 10,787

Re: Calculation of Cumulative 3-year returns based on annual return data

Yeah. if an individual obs represent one year, you can combine LAG skill and MERGE skill together . Like :

data want;

merge have

have(firstobs=2 keep=CUSIP  Annret rename=(CUSIP=CUSIP1 Annret=Annret1))

have(firstobs=3 keep=CUSIP  Annret rename=(CUSIP=CUSIP2 Annret=Annret2));

lag1=lag(Annret);

lag2=lag2(Annret);

if CUSIP=lag(CUSIP) and CUSIP=lag2(CUSIP) then FORMRET=sum(Annret,lag1,lag2);

if CUSIP=CUSIP1 and CUSIP=CUSIP2 then EVALRET=sum(Annret,Annret1,Annret2);

run;

Discussion stats
• 3 replies
• 354 views
• 0 likes
• 3 in conversation