Product of Running Total (rate of return calculation)

[adubrovs]

Hi I need to calculate an cummulative rate of return calculation when I only have monthly returns.

For example, the table includes the following returns
Jan- -0.45%
Feb- 1.35%
March- 0.25%
etc..

The formula is basically [(1+January Return)*(1+Feb Return)*(1+March)]-1.

I'm having trouble generating a product of running total, I know you need to do it through variable, but for some reason it's not working for me.

Any help would be greatly appreciated.
Thanks.

 

[lbass]

Please explain what fields you are working with. Are the months based on a date group on change of month? Or is there a month field? Is the value we see in your sample a detail level field? Or a formula calculation? If the latter, what is the content of the formula (and of any nested formulas)? Also not sure why you are adding one to each value.

-LB

 

[adubrovs]

I'm working with two column, one column has a date the other has a monthly return. I need to get the product of the entire monthly return column.

So if my table is set up as follow
Month Monthly Return
Jan 1.10
Feb 1.05
March 1.20

The product is 1.10*1.05*1.20 = 1.386

The reason you need a one is that's the formula for a cummulative return from periodic returns.

 

[lbass]

Shouldn't your example have been the following based on your original post?

Month Monthly Return
Jan .10
Feb .05
March .20

The product is (1.10*1.05*1.20)-1 = 0.386

???

If so, try the following:

If you are checking monthly returns within a group, add a reset formula to the group header:

//{@reset}:
whileprintingrecords;
numbervar array x := 0;
numbervar i := 0;
numbervar z := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar array x;
numbervar i := i + 1;
redim preserve x;
x := {table.monthly_return};

//{@displaycumrateofreturn} for the group footer:
whileprintingrecords;
numbervar array x;
numbervar k;
numbervar z := 1;
for k := 1 to ubound(x) do(
z := z * (1+ x[k])
);
z-1

-LB

 

[adubrovs]

Yes it should have. I was too focused on getting the return column to multiply that forgot about the -1.

I will give your suggestion a try over the next few days and let you know.

Thank you again for your help.

 

[adubrovs]

It worked great. Thanks again.