Forum Discussion

mjfigg's avatar
mjfigg
Frequent Visitor
9 years ago
Solved

Rolling XIRR Calculation

Hello there,

 

I am trying to build a rolling IRR calculation for an investment return report. No mater what I try, I cannot seem to get PBI to return a value for my formula. Can anyone help.

 

ROLLING IRR = CALCULATE(
XIRR('INVESTMENT RETURNS','INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD],'INVESTMENT RETURNS'[DATE])
,FILTER('INVESTMENT RETURNS',EARLIER('INVESTMENT RETURNS'[Index])>='INVESTMENT RETURNS'[Index]))

There are six rows in my test table as follows:

 

IndexFREE CASHFLOW FOR PERIODDATE
42- 7,531,293.0030/06/2013
43     859,425.0030/06/2014
44     901,305.0030/06/2015
45     951,189.0030/06/2016
46  7,742,641.0030/06/2017
47  1,498,289.0030/06/2018

 

Any help would be greatly appreciated.

  • mjfigg's avatar
    mjfigg
    9 years ago

    Hi v-ljerr-msft,

     

    Thank you very much for your help! 

     

    The formula works great, although I have made a few tweaks to include:

     - a terminal value (from the [equity repayment/(investment) on sale] column) to be calculated at the end of each period and calculated in the XIRR calc (i.e. if I sold the investment now, what would my XIRR be), and

     - a check to ensure that there is at least one positive cashflow (another requirement of the XIRR formula along with the first periof being negative).

     

    see my code as follows fyi.... 

    ROLLING XIRR = 
VAR countpos =
    CALCULATE(
        COUNT('INVESTMENT RETURNS'[totalcashflow]),
            FILTER(ALL('INVESTMENT RETURNS'), 'INVESTMENT RETURNS'[totalcashflow]>0 && EARLIER ('INVESTMENT RETURNS'[Index] ) >= 'INVESTMENT RETURNS'[Index] && 'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
              )
           )

VAR minIndex =
    CALCULATE (
        MIN ( 'INVESTMENT RETURNS'[Index] ),
        FILTER (
            ALL ( 'INVESTMENT RETURNS' ),
            'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
        )
    )
VAR firstValue =
    CALCULATE (
        MIN ( 'INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD (excl. sale)] ),
        FILTER (
            ALL ( 'INVESTMENT RETURNS' ),
            'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
                && 'INVESTMENT RETURNS'[Index] = minIndex
        )
    )
RETURN
    IF (
        'INVESTMENT RETURNS'[Index] > minIndex
            && firstValue < 0
            && countpos >= 1 ,
            XIRR (
                UNION( 
                    SUMMARIZE( 
                        FILTER(ALL ( 'INVESTMENT RETURNS' ), EARLIER ('INVESTMENT RETURNS'[Index] ) >= 'INVESTMENT RETURNS'[Index] && 'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] ))
                        ,'INVESTMENT RETURNS'[Index],
                        "DATE1",MAX('INVESTMENT RETURNS'[DATE]),
                        "CASHFLOW",MAX('INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD (excl. sale)])
                            ),
                    SUMMARIZE(
                        FILTER(ALL ( 'INVESTMENT RETURNS' ), EARLIER ('INVESTMENT RETURNS'[Index] ) = 'INVESTMENT RETURNS'[Index] ),
                        'INVESTMENT RETURNS'[Index],
                        "DATE1",MAX('INVESTMENT RETURNS'[DATE]),
                        "CASHFLOW",MAX('INVESTMENT RETURNS'[EQUITY REPAYMENT/(INVESTMENT) ON SALE])
                            )
                ),
                [CASHFLOW],
                [DATE1]
            )
    )

    Thanks again for all your help. Much appreciated.

     

    MJ

     

     

16 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi mjfigg,

     

    Based on my test, you should be able to use the formula below to build a rolling IRR in your scenario. :smileyhappy:

    ROLLING IRR = 
VAR minIndex =
    CALCULATE ( MIN ( 'INVESTMENT RETURNS'[Index] ), ALL ( 'INVESTMENT RETURNS' ) )
RETURN
    IF (
        'INVESTMENT RETURNS'[Index] > minIndex,
        CALCULATE (
            XIRR (
                'INVESTMENT RETURNS',
                'INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD],
                'INVESTMENT RETURNS'[DATE]
            ),
            FILTER (
                'INVESTMENT RETURNS',
                EARLIER ( 'INVESTMENT RETURNS'[Index] ) >= 'INVESTMENT RETURNS'[Index]
            )
        )
    )

     

    Regards

    • mjfigg's avatar
      mjfigg
      Frequent Visitor
      9 years ago

      Hey there!

       

      Thanks, the code worked great. However, I just realised that I will actually need another filter on the table for company name, as there will be multiple companies that an XIRR will need to be calculated for. See updated data below. Could you still help me?

       

      NameIndexFREE CASHFLOW FOR PERIODDATE
      Company A42-7,531,293.0030/06/2013
      Company A43     859,425.0030/06/2014
      Company A44     901,305.0030/06/2015
      Company A45     951,189.0030/06/2016
      Company A46  7,742,641.0030/06/2017
      Company A47  1,498,289.0030/06/2018
      Company B48-840191830/06/2014
      Company B4994974730/06/2015
      Company B5094238830/06/2016
      Company B5194375630/06/2017
      Company B52104908930/06/2018
      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi mjfigg,

         

        Sure! The formula below should work in this new scenario. :smileyhappy:

        ROLLING IRR = 
VAR minIndex =
    CALCULATE (
        MIN ( 'INVESTMENT RETURNS'[Index] ),
        FILTER (
            ALL ( 'INVESTMENT RETURNS' ),
            'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
        )
    )
RETURN
    IF (
        'INVESTMENT RETURNS'[Index] > minIndex,
        CALCULATE (
            XIRR (
                'INVESTMENT RETURNS',
                'INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD],
                'INVESTMENT RETURNS'[DATE]
            ),
            FILTER (
                'INVESTMENT RETURNS',
                EARLIER ( 'INVESTMENT RETURNS'[Index] ) >= 'INVESTMENT RETURNS'[Index]
                    && 'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
            )
        )
    )

         

        Regards