2 Replies Latest reply: May 1, 2012 12:09 PM by knguyen1

# Expression to calculate YTD return from monthly data

I have monthly returns and I convert each month’s return to a decimal and add 1 to each one to get the factor. So, it looks like this:

 Col-A Col-B Col-C Col-D Date Return% Factor YTD Return 1/1/2012 1.51% 1.0151 =C3 2/1/2012 -1.96% 0.9804 =C3*C4 3/1/2012 1.12% 1.0112 =C3*C4*C5 4/1/2012 4.43% 1.0443 =C3*C4*C5*C6 5/1/2012 3.49% 1.0349 =C3*C4*C5*C6*C7 6/1/2012 -1.66% 0.9834 =C3*C4*C5*C6*C7*C8 7/1/2012 -3.10% 0.969 =C3*C4*C5*C6*C7*C8*C9 8/1/2012 1.50% 1.015 =C3*C4*C5*C6*C7*C8*C9*C10 9/1/2012 3.74% 1.0374 =C3*C4*C5*C6*C7*C8*C9*C10*C11

I am trying to get the year-to-date returns, by the product for the entire set of factors like this:

1.0151 × 0.9804 × 1.0112 × 1.0443 x 1.0349 x 0.9834 x 0.9690 x 1.0150 x 1.0374 = 1.0913, so YTD for September is 9.13%

You can't use sum or total because that adds the factors together, so what function should I use to get the product of the data set?

• ###### Re: Expression to calculate YTD return from monthly data

hi attach is an example hope it helps you

you should note that this example only works if the date is the only dimension in  the table

so other all additional data should be expressions

• ###### Re: Expression to calculate YTD return from monthly data

Hi Iiron,

Thanks for your help.  I only have the personal version of QV and can't view your attached file.  Can you please write out your expression for YTD return?