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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.