Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Divesh2
New Member

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

 

 

 

3 REPLIES 3
Fowmy
Super User
Super User

@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"
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors