Forum Discussion

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. R...

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.

v-ljerr-msft

Microsoft Employee

9 years ago

Hi mjfigg,

Remarks

The value is calculated as the rate that satisfies the following function:

The series of cash flow values must contain at least one positive number and one negative number.

After I a few test, I find the issue is with your data of Company C(there are only positive numbers, but no nagative number).

So you may need to correct your data, or use the the formula below to make it work. :smileyhappy:

ROLLING IRR =
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] ),
        FILTER (
            ALL ( 'INVESTMENT RETURNS' ),
            'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
                && 'INVESTMENT RETURNS'[Index] = minIndex
        )
    )
RETURN
    IF (
        'INVESTMENT RETURNS'[Index] > minIndex
            && firstValue < 0,
        CALCULATE (
            XIRR (
                'INVESTMENT RETURNS',
                'INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD],
                'INVESTMENT RETURNS'[DATE]
            ),
            FILTER (
                ALL ( 'INVESTMENT RETURNS' ),
                EARLIER ( 'INVESTMENT RETURNS'[Index] ) >= 'INVESTMENT RETURNS'[Index]
                    && 'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
            )
        )
    )

Regards

mjfigg

Frequent Visitor

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 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.

fatherof4
Frequent Visitor
8 years ago
Is there a way to modify the code so that it works on Power Pivot as well?
- fatherof4
  Frequent Visitor
  8 years ago
  We figured it out. The code needs to be entered as a calculated column not a measure on both Power BI and Power Pivot
DVA496
Frequent Visitor
2 years ago
Hi,

I am trying to get the IRR (rolling or final) depending on a cutoff date which I included as a filter (different table containing all dates of my table). The cutoff date is a measure from a calcualted table which include calendar dates will 2050
S = SELECTEDVALUE(ValuationDate[Dates])

the table looks like this: there could be various assets and various value (entities)

Hope you can help,

Best,

Dyana

Forum Discussion

Rolling XIRR Calculation

Related content

Calculate XIRR

XIRR calculation using DAX

XIRR Calculation Error

Aggregate XIRR Calculation Help

xIRR calculation discrepancy