I need to calculate modified deitz return (Which is basically geometric mean of periodic return after adjusting Cash Flow and weighted Cash flow). You calculate monthly return, and for larger periods take geometric mean for it. Say you have monthly return and you can calculate annual return by taking Geometric mean. I am manipulating data in SAS EG so I can use the dataset in SAS VA for a Report. Geometric Mean=(1+r1)*(1+r2)...... -1 r1, r2== are monthly returns. The idea is to select a random period Date_From and Date_To and the pre-calculated Rate of return come up. I have calculated the monthly return with adjusted cash flow, I am strugling to get a Geometric mean/Cumulative Return. I HAVE: Client Financial Product Date Return Return +1 Customer A Product A Jan2016 2% $1.02 Product A Feb2016 3% $1.03 Product A Mar2016 1% $1.01 Product A Apr2016 7% $1.07 Product A May2016 -5% $0.95 Product A Jun2016 3% $1.03 Product A Jul2016 -0.50% $1.00 Product A Aug2016 3% $1.03 Product A Sep2016 2% $1.02 Product A Oct2016 5% $1.05 Product A Nov2016 1% $1.01 Product A Dec2016 -1% $0.99 Customer A Product B .... .... ..... ..... ....... ....... ......... ......... ......... ......... I WANT: Financial Product Date_From Date_To GM_Formulae Cumulative Return Product A Jan2016 Feb2016 (1+r2)-1 2.00% Product A Jan2016 Mar2016 (1+r2)*(1+r3)-1 5.06% Product A Jan2016 Apr2016 .... .... Product A Jan2016 May2016 ..... ..... Product A Jan2016 Jun2016 ....... ....... Product A Jan2016 Jul2016 ......... ......... Product A Jan2016 Aug2016 ......... ......... Product A Jan2016 Sep2016 .... .... Product A Jan2016 Oct2016 ..... ..... Product A Jan2016 Nov2016 ....... ....... Product A Jan2016 Dec2016 (1+r1)*(1+r2)....*(1+r11)-1 23.16% Product A Feb2016 Mar2016 ......... ......... Product A Feb2016 Apr2016 .... .... Product A Feb2016 May2016 ..... ..... Product A Feb2016 Jun2016 ....... ....... Product A Feb2016 Jul2016 ......... ......... Product A Feb2016 Aug2016 ......... ......... Product A Feb2016 Sep2016 Product A Feb2016 Oct2016 Product A Feb2016 Nov2016 Product A Feb2016 Dec2016 For 12 months there will be 66 combinations of months The date combinations are easy to create. But I am having trouble calculating Rate of return for all teh 66 combinations. Since it is teh same variable. There are ways to this in excel. Since, actual table is millions of rows thats not a possibility. Can this be done in SAS.
... View more