XIRR Calculation Help!!

[Divesh2]

Hi All, 

I have some cashflows for deals that I want to calculate the XIRR on. For some of the deals, the return is quite negative resulting in an error - i also see the same issue in Excel. Usually in Excel, i can use the IFERROR function along with the XIRR formula to overwrite this error. I tried to do this in PowerBI but it doesn't seem to work.

Currently, I am maintaining a spreadsheet to highlight deals that need to show n/m and building this into the formula, but as we include more deals and more data, this list will be hard to maintain, and currently to identify the deals that are causing an error, I have to manually filter each deal until it breaks by data table. 

The aim: I am hoping for PowerBI to calculate the XIRR calculation, and if there is an error to replace this with Not Measurable ("n/m"). It needs to be dynamic as I want to filter deals by country / sector, so when it calculates the XIRR for the deals that fit the parameter, it needs to identify the deals that will cause the calculation to trip up. I have provided the formula below. 

Any help would be amazing! 🙂 

Gross IRR Projected = 
var Period = VALUES('Projected Deals NA'[Period])
Var List=VALUES('Projected Deals NA'[Deal Name])
Var Res =
VAR IRR_EUR =

       CALCULATE( XIRR (
            'Gross Returns',
            'Gross Returns'[Cash Flow (EUR)],
            'Gross Returns'[Date]
        ),
        'Gross Returns'[SelectedTab]="Gross Projected")

VAR IRR_USD =
   
        CALCULATE( XIRR (
            'Gross Returns',
            'Gross Returns'[Cash Flow (USD)],
            'Gross Returns'[Date]
        ),
        'Gross Returns'[SelectedTab]="Gross Projected")
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Currency Table'[Currency] ) = "EUR", IRR_EUR,
        SELECTEDVALUE ( 'Currency Table'[Currency] ) = "USD", IRR_USD
    )
Return
IF(HASONEVALUE('Gross Returns'[Deal Name]),
IF(SELECTEDVALUE('Gross Returns'[Quarter]) IN Period && SELECTEDVALUE('Gross Returns'[Deal Name]) In List , "n/m",Res),
BLANK()
)

[Fowmy]

@Divesh2 

Please note that you masure is supposed to resutrn a number but in case of an error you need text to be resturned "n/m". 

Apply the IFERROR at the beginning:

Gross IRR Projected =
IFERROR (
    VAR Period =
        VALUES ( 'Projected Deals NA'[Period] )
    VAR List =
        VALUES ( 'Projected Deals NA'[Deal Name] )
    VAR Res =
        VAR IRR_EUR =
            CALCULATE (
                XIRR (
                    'Gross Returns',
                    'Gross Returns'[Cash Flow (EUR)],
                    'Gross Returns'[Date]
                ),
                'Gross Returns'[SelectedTab] = "Gross Projected"
            )
        VAR IRR_USD =
            CALCULATE (
                XIRR (
                    'Gross Returns',
                    'Gross Returns'[Cash Flow (USD)],
                    'Gross Returns'[Date]
                ),
                'Gross Returns'[SelectedTab] = "Gross Projected"
            )
        RETURN
            SWITCH (
                TRUE (),
                SELECTEDVALUE ( 'Currency Table'[Currency] ) = "EUR", IRR_EUR,
                SELECTEDVALUE ( 'Currency Table'[Currency] ) = "USD", IRR_USD
            )
    RETURN
        IF (
            HASONEVALUE ( 'Gross Returns'[Deal Name] ),
            IF (
                SELECTEDVALUE ( 'Gross Returns'[Quarter] )
                    IN Period
                        && SELECTEDVALUE ( 'Gross Returns'[Deal Name] ) IN List,
                "n/m",
                Res
            ),
            BLANK ()
        ),
    "n/m"
)

[Fowmy]

@Divesh2 

Did my solution work for you?
Please let me know if you need further hlep on this or you may accpet this as a solution if it has worked for you.

[Divesh2]

Hi Fowmy - it did not work, but upon research, we were able to find that within the XIRR calcualtion, you can added an alternative value  - XIRR(table,values,dates,[,guess[,alternateResult]]) - using this, we were able to set an alternative value of 999.99%, meaning when the XIRR was failing, it would default to this value. We then added an IF statement to change 999.99% to "n/m".