topic Re: How do I calculate cumulative returns for multiple different time periods? in SAS Programming

How do I calculate cumulative returns for multiple different time periods?

Mistletoad — Tue, 25 May 2021 15:21:58 GMT

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!

Re: How do I calculate cumulative returns for multiple different time periods?

Reeza — Tue, 25 May 2021 15:31:06 GMT

Do you have SAS/ETS licensed? If so, look into PROC EXPAND.

Can you expand on your calculation, are you just using the value from 6 months later to get the return?
Is it rolling? Not sure what exactly you mean by cumulative here...

Re: How do I calculate cumulative returns for multiple different time periods?

Mistletoad — Tue, 25 May 2021 15:33:34 GMT

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.

Re: How do I calculate cumulative returns for multiple different time periods?

Reeza — Tue, 25 May 2021 15:59:27 GMT

Is that taking the first date from the 6 month interval and the last or are you accounting for every day? What about holidays? What about weekends? How are those factored in?

Re: How do I calculate cumulative returns for multiple different time periods?

mkeintz — Tue, 25 May 2021 19:01:55 GMT

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.