## How to calculate buy-and-hold return based on daily return data

Solved
Occasional Contributor
Posts: 18

# How to calculate buy-and-hold return based on daily return data

[ Edited ]

Hello,

Currently I have multiple daily data for multiple firms of the 5 day,  1 month(21 trading days), 3 months(63 trading days), 6 months(126 trading days), 9 months(189 trading days) and  12 months(252 trading days) stock buy-and-hold returns.

MY data is as below: (252 may not be the last data of a firm)

 Firm_ID date daye rtn 1101 2009/8/26 1 -0.0173 1101 2009/8/27 2 0.00147 … 1101 2010/8/25 252 0.03 1101 253 1101 … 1102 2011/3/23 1 0.0014 1102 2011/3/24 2 -0.007 … 1102 2011/9/9 252 0.045 1102 … 1205 2013/2/4 1 0.065 …

If I calculate it by hand then:

(1) 5 days: Buy and hold return=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day5)-1

(2) 1 month: BHR=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day21)-1

(3) 3 months: BHR=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day63)-1

....

(6) 12 months:BHR=(1+ rtn_day1)*(1+rtn_day2)*(1+rtn_day3)*...*(1+rtn_day252)-1

However, I don't know how to write a sas program to do this...

Please give me some suggestions. Thank you!

Accepted Solutions
Solution
‎10-26-2015 08:46 PM
Super Contributor
Posts: 355

## Re: How to calculate buy-and-hold return based on daily return data

A brute force approach would be (maybe proc timedata is worth looking):

%Let Span=5; *!;

Data A;
Do Firm=1 To 3;
Do i=0 To 20;
Date=IntNX('month',"01JAN2000"d,i,'same');
Rtn=RanNor(1)/10;
Output;
End;
End;
Drop i;
Format Date Date9.;
Run;

%Macro L;
%Do i=1 %To &Span.;
%If &i.=1 %Then %Do;
Data B;
Set A;
A_Rtn1=(1+Rtn);
Run;
%End;
%Else %Do;
Data B;
Set B;
By Firm;
A_Rtn&i.=Lag(A_Rtn%Eval(&i.-1));
If First.Firm Then Call Missing (A_Rtn&i.);
Run;
%End;
%End;
%Mend;
%L;

Data B (Drop=A_;
Set B;
Array A A_:;
Total_Rtn_&Span.=1;
Do over A;
Total_Rtn_&Span.=Total_Rtn_&Span.*A;
End;
Total_Rtn_&Span.=Total_Rtn_&Span.-1;
Run;

All Replies
Solution
‎10-26-2015 08:46 PM
Super Contributor
Posts: 355

## Re: How to calculate buy-and-hold return based on daily return data

A brute force approach would be (maybe proc timedata is worth looking):

%Let Span=5; *!;

Data A;
Do Firm=1 To 3;
Do i=0 To 20;
Date=IntNX('month',"01JAN2000"d,i,'same');
Rtn=RanNor(1)/10;
Output;
End;
End;
Drop i;
Format Date Date9.;
Run;

%Macro L;
%Do i=1 %To &Span.;
%If &i.=1 %Then %Do;
Data B;
Set A;
A_Rtn1=(1+Rtn);
Run;
%End;
%Else %Do;
Data B;
Set B;
By Firm;
A_Rtn&i.=Lag(A_Rtn%Eval(&i.-1));
If First.Firm Then Call Missing (A_Rtn&i.);
Run;
%End;
%End;
%Mend;
%L;

Data B (Drop=A_;
Set B;
Array A A_:;
Total_Rtn_&Span.=1;
Do over A;
Total_Rtn_&Span.=Total_Rtn_&Span.*A;
End;
Total_Rtn_&Span.=Total_Rtn_&Span.-1;
Run;

Super Contributor
Posts: 355

## Re: How to calculate buy-and-hold return based on daily return data

However, this would give the same result:

%Let Span=5; *!;

Data A;
Do Firm=1 To 3;
Do i=0 To 20;
Date=IntNX('month',"01JAN2000"d,i,'same');
Rtn=RanNor(1)/10;
Output;
End;
End;
Drop i;
Format Date Date9.;
Run;

Data B (Keep=Firm Date Rtn Total_Rtn_&Span.);
Retain dummy ;
Set A;
By Firm;
If First.Firm Then Do;
count=0;
dummy=1;
End;
count+1;
Drop_Value=Lag&Span.(Rtn);
If count gt &Span. Then Do;
dummy=dummy*(1+Rtn)/(1+Drop_Value);
End;
Else Do;
dummy=dummy*(1+Rtn);
End;
Total_Rtn_&Span.=dummy-1;
Run;

Occasional Contributor
Posts: 18