4 Replies Latest reply on Jul 3, 2017 11:28 AM by Matthew Riley

    Calculating MTD, QTD, and YTD cumulative returns???

    luciana.suran.1

      Hi,

       

      I am trying to calculate cumulative returns for a stock on a MTD, QTD, and YTD basis. I know how to calculate the cumulative returns on an entire series by using the following formula:

       

      IF INDEX() = 1
        THEN 1
      ELSE
        (1 + AVG([Daily Change])) * PREVIOUS_VALUE(1)

      END

       

      However, in this case I want to calculate MTD, QTD, and YYD cumulative returns all in the same view. I've date using DATETRUNC but I inevitably end up running into problems with PREVIOUS_VALUE(1).

       

      Would be immensely appreciative if someone can help me figure this out.

       

       

      Thanks! Sample workbook is attached.

        • 1. Re: Calculating MTD, QTD, and YTD cumulative returns???
          Benjamin Greene

          You can do this with advanced table calculation specification (and without creating any more fields!). Here's what to do:

           

          1. In a new sheet, create a Cumulative Return calculated field using the syntax you provided above.

          2. Drag Measure Names to Filters and select Return and Cumulative Return.

          3. Drag Measure Names to Columns.

          4. Right-click drag Date to Rows, then select Date (Discrete).

          5. Drag YEAR(Date) to Detail, then click the plus sign to drill down to QUARTER(Date) and MONTH(Date).

          6. Drag Measure Values to Text.

          7. Right-click the Cumulative Return pill and select Compute Using>Date.

          8. Drag another instance of Cumulative Return to the Measure Values shelf.

          9. Right-click this new Cumulative Return pill and select Edit Table Calculation.

          10. Compute using Date at the deepest level restarting every Year of Date and click OK.

          11. Drag another instance of Cumulative Return to the Measure Values shelf.

          12. Right-click this new Cumulative Return pill and select Edit Table Calculation.

          13. Compute using Date at the deepest level restarting every Quarter of Date and click OK.

          14. Drag another instance of Cumulative Return to the Measure Values shelf.

          15. Right-click this new Cumulative Return pill and select Edit Table Calculation.

          16. Compute using Date at the deepest level restarting every Month of Date and click OK.

          17. Edit the aliases of the columns to differentiate the Total Cumulative Return from YTD, QTD and MTD.

           

          And there you have it!

          • 2. Re: Calculating MTD, QTD, and YTD cumulative returns???
            Shinichiro Murakami

            Because you are handling three different time range, you need to create three different work sheets and combine them on the dashboard.

             

             

            \

             

            Each worksheet has filter to show only latest Year, Qtr, and Month by LOD. plus only how the last date of the latest date by index "last()".

             

             

             

            Thanks,

            Shin

            • 4. Re: Calculating MTD, QTD, and YTD cumulative returns???
              Matthew Riley

              The attached workbook s is very nearly correct but there are a couple of mistakes.

               

              1. The total, YTD, QTD and MTD total returns need to start at the previous date.  So for the first row, date = 24 Sep 2002, the first return is -1.869%, so the first cumulative return should be 0.98131 = (1-1.869%)

               

              2. The cumulative return is actually the NAV or price, or 1+the cumulative return.  So the cumulative return of returns 1% then 2% is actually 3.02%, = (1+1%)*(1+2%)-1.  The calculated return should subtract 1.  I think the revised calculation should be, cumulative return=:

              IF INDEX() = 0

                THEN 0

              ELSE

                (1 + AVG([Return])) * (1+ PREVIOUS_VALUE(0))-1

              END

               

              I've attached a revised workbook.  The original is renamed "Original", with my revised version in the worksheet "Revised".  The revised cumulative return calculation is called "New cumulative return".

               

              Hope this helps - please let me know if you see any mistakes.