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

    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-ACol-BCol-CCol-D
      DateReturn%FactorYTD Return
      1/1/20121.51%1.0151=C3
      2/1/2012-1.96%0.9804=C3*C4
      3/1/20121.12%1.0112=C3*C4*C5
      4/1/20124.43%1.0443=C3*C4*C5*C6
      5/1/20123.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/20121.50%1.015=C3*C4*C5*C6*C7*C8*C9*C10
      9/1/20123.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?