Forum Discussion
XIRR Calculation Help!!
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. Usua...
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"
)
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.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".
