I am trying to calculate the cumulative returns for multiple different 6 month periods for a market index.
In my first table, I have the daily returns for an index.
date daily return 1 19251231 . 2 19260102 0.005689 3 19260104 0.000706 4 19260105 -0.004821 5 19260106 -0.000423 6 19260107 0.004988 7 19260108 -0.003238
In my second table, I have a list of transaction dates. Each transaction date is the start_date and the end date is 6 months after the start date.
I would like to calculate the 6 month cumulative returns for all these different transaction dates.
Filing identifier Transaction Date 1 0000897069-07-000506 02/15/2007 2 0000897069-07-000606 02/20/2007 3 0000897069-07-000757 03/07/2007 4 0000897069-07-000745 03/07/2007 5 0000897069-07-000750 03/07/2007 6 0000897069-07-000753 03/07/2007 7 0000897069-07-000747 03/07/2007 8 0000897069-07-001409 06/13/2007 9 0000897069-07-001417 06/14/2007
I have been using the following equation to calculate cumulative returns
exp(sum(log(1+a.vwretd))) - 1 as cum_return
Thank you!
Hey Reeza,
Right now I am using SAS studio.
As for my calculation, right now I have daily stock return data. But I want to calculate the return for a 6 month holding period starting from the transaction date.
Make a series of daily cumulative returns (plus 1) for the entire history of the index. Put it in an array indexed by date (array cumret_plus1_hist below). Fill missing dates with the most recent prior cumulative return (plus 1).
Then a 6-month forward-looking cumulative return is a simple calculation:
cumret_plus1(6 months forward) / cumret_plus1(transaction date) - 1:
This is an untested program:
%let begdate=31dec1925;
%let enddate=31dec2020;
data want (drop=vwretd date _:);
if _n_=1 then do;
array cumret_plus1_hist{%sysevalf("&begdate"d):%sysevalf("&enddate"d)} _temporary_;
_cumret_plus1=1; /* initialize to cumret=0 */
/* Populate the array */
do until (end_of_index);
set index end=end_of_index;
_cumret_plus1=_cumret_plus1 * (1+vwretd);
cumret_plus1_hist{date}=_cumret_plus1;
end;
/* Carry forward to any dates with missing vwretd */
do _d="&begdate"d to "&enddate"d;
if cumret_plus1_hist{_d}=. then cumret_plus1_hist{_d}=cumret_plus1_hist{_d-1};
end;
end;
set transactions;
_d=intnx('month',transaction_date,6,'same');
cumret= cumret_plus1_hist{d}/cumret_plus1_hist{transaction_date} - 1;
run;
This program assumes the data set INDEX is in chronological order. But the transaction file can be in any order.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.