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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
giordani2000
Helper I
Helper I

xIRR calculation discrepancy

Hi team,

I am trying to calculate XIRR for a given set of investments and cashflows (column Total CF), which comply with all rules of XIRR function, but I still get a discrepancy between calculating XIRR using XIRR formula vs a new measure via DAX (this method allows me to slice and dice the data among diferent attributes and categories).

 

Please see my data and calculation attached.

 

When loaded into excel the XIRR formula the value for SYM returned -69.9%, but the new IRR measure yields -100% (incorrect). For Aqsys, the XIRR formula and IRR measure returned the same value.

 

What am I doing wrong?

 

Thanks in advance for your help.

Note: not sure how to attached the excel file but I am open to it.

 

Measure Formula: 

=CALCULATE(iferror(xirr(TestCF,TestCF[Total CF],TestCF[Date],-0.01),-1),filter( ALL(TestCF),

CONTAINS(

VALUES(TestSum_2),TestSum_2[Portfolio Company],TestCF[Company])

)

)

 

XIRR formula: XIRR(Cashflow!E2:E7,Cashflow!B2:B7,0)

 

CompanyDateCFActivityFMVTotal CF

Sym3/19/2013-2125000 -2125000
Sym10/10/2014-500000 -500000
Sym12/10/2014-500000 -500000
Sym12/2/2015362591 362591
Sym12/8/20156 6
Sym12/2/2019 00
AqSys6/4/2010-2142857 -2142857
AqSys2/17/2011-3085714 -3085714
AqSys4/27/2012-1707314 -1707314
AqSys1/15/2014-2489833 -2489833
AqSys1/15/2014-235001 -235001
AqSys10/27/201525911452 25911452
AqSys12/2/20169559646 9559646
AqSys4/21/20172765590 2765590
AqSys5/22/20172370391 2370391
AqSys11/5/20190 0
AqSys12/2/2019 00

 

Photos:

cashflows

giordani2000_0-1671735058963.png

 

Other attributes

giordani2000_2-1671735121872.png

 

Output

giordani2000_3-1671735303765.png

 

 

3 ACCEPTED SOLUTIONS

XIRR is a poorly implemented function. It's finicky and will occasionally fail if you don't give it a good enough guess to start from. For example, in your file, it will return the -69.9% value if you use -0.5 as a guess but fails with -0.01 as you have.

 

I don't know of a good way around this. I've implemented my own versions of XIRR in SQL before but this isn't feasible in DAX as it cannot handle the recursion required for the algorithm. In my own financial models, I've written measures that give a very rough approximation that I feed into the guess parameter of my IRR measures (currently I use annualized TVPI but I hope to find something better).

 

In a pinch, you may be able to get away with nested IFERROR functions that try XIRR with different guesses but this is ugly and inefficient:

CALCULATE (
    IFERROR (
        XIRR ( TestCF, TestCF[Total CF], TestCF[Date], 0.1 ), /*Default guess*/
        IFERROR (
            XIRR ( TestCF, TestCF[Total CF], TestCF[Date], -0.1 ),
            IFERROR (
                XIRR ( TestCF, TestCF[Total CF], TestCF[Date], -0.5 ),
                BLANK ()
            )
        )
    ),
    FILTER (
        ALL ( TestCF ),
        CONTAINS ( VALUES ( TestSum_2 ), TestSum_2[Portfolio Company], TestCF[Company] )
    )
)

View solution in original post

The file is updated now. The ones it's failing on don't surprise me. IRRs like that are pretty far out of the norm.

 

Unfortunately, I don't have any further advice other than to tinker with the guesses. You could try using [Loss WE Ratio] as a guess and including as many additional guesses as you feel like. I think the following may work for this specific case but there's no guarantee it will work more broadly. I don't have any silver bullets.

CALCULATE (
    IFERROR (
        XIRR ( TestCF, TestCF[Total CF], TestCF[Date], 0.1 ),
        IFERROR (
            XIRR ( TestCF, TestCF[Total CF], TestCF[Date], - [Loss WD Ratio] ),
            IFERROR (
                XIRR ( TestCF, TestCF[Total CF], TestCF[Date], -0.5 ),
                IFERROR (
                    XIRR ( TestCF, TestCF[Total CF], TestCF[Date], -0.999 ),
                    -1
                )
            )
        )
    ),
    FILTER (
        ALL ( TestCF ),
        CONTAINS ( VALUES ( TestSum_2 ), TestSum_2[Bin], TestCF[Bin] )
    )
)

 

View solution in original post

Most of my training has been on the job and answering thousands of questions on here and StackOverflow. SQLBI has great articles about DAX. Radacad has good articles too. My new favorite is Data Goblins.

 

Reading articles and watching videos is nice but to really learn you need to actually solve problems yourself, whether your own or other people's.

View solution in original post

14 REPLIES 14
giordani2000
Helper I
Helper I

Hi Gallen @v-jialluo-msft , Thanks for the quick response. I tried adding ALL following XIRR but did not work.

I uploaded the file to onedrive as suggested. https://1drv.ms/x/s!AhK8zgM_1Ab0ds0FGvdGKm1hobA?e=CN6lYc 

#Forum, any other suggestion?

@v-jialluo-msftand Forum, moving this question to the top. Still looking for advise. Thanks.

@tamerj1 

 

Any ideas how to solve this? For an unknown reason the IRR formula is not picking up company Sym but it picks the rest of my 100+ companies in my live file. It may be a problem with the data but I have review it multiple times. Appreciate your feedback.

@AlexisOlson I noticed you had contributed to a question related to XIRR in the past. Would you be able to contribute to this problem. See prior posting for a path to the file. Thanks.!

XIRR is a poorly implemented function. It's finicky and will occasionally fail if you don't give it a good enough guess to start from. For example, in your file, it will return the -69.9% value if you use -0.5 as a guess but fails with -0.01 as you have.

 

I don't know of a good way around this. I've implemented my own versions of XIRR in SQL before but this isn't feasible in DAX as it cannot handle the recursion required for the algorithm. In my own financial models, I've written measures that give a very rough approximation that I feed into the guess parameter of my IRR measures (currently I use annualized TVPI but I hope to find something better).

 

In a pinch, you may be able to get away with nested IFERROR functions that try XIRR with different guesses but this is ugly and inefficient:

CALCULATE (
    IFERROR (
        XIRR ( TestCF, TestCF[Total CF], TestCF[Date], 0.1 ), /*Default guess*/
        IFERROR (
            XIRR ( TestCF, TestCF[Total CF], TestCF[Date], -0.1 ),
            IFERROR (
                XIRR ( TestCF, TestCF[Total CF], TestCF[Date], -0.5 ),
                BLANK ()
            )
        )
    ),
    FILTER (
        ALL ( TestCF ),
        CONTAINS ( VALUES ( TestSum_2 ), TestSum_2[Portfolio Company], TestCF[Company] )
    )
)

Thanks @AlexisOlson this solution, while ugly and ineffient, works. If I may, let me present to you three other instances where the IRR formula does not yield the expected result. Disclosing these issues to you may disclose that I am not an advance power pivot and power query user. Click the link below for an updated file:

https://1drv.ms/x/s!AhK8zgM_1Ab0ds0FGvdGKm1hobA?e=rEFcVi

Would you be able to contribute to this problem?

Additionally, I am super insterested in learning and becoming an advanced user of Power Query and Power Pivot. Any recomendations where to get more free training.

 

The file looks the same to me.

The file is updated now. The ones it's failing on don't surprise me. IRRs like that are pretty far out of the norm.

 

Unfortunately, I don't have any further advice other than to tinker with the guesses. You could try using [Loss WE Ratio] as a guess and including as many additional guesses as you feel like. I think the following may work for this specific case but there's no guarantee it will work more broadly. I don't have any silver bullets.

CALCULATE (
    IFERROR (
        XIRR ( TestCF, TestCF[Total CF], TestCF[Date], 0.1 ),
        IFERROR (
            XIRR ( TestCF, TestCF[Total CF], TestCF[Date], - [Loss WD Ratio] ),
            IFERROR (
                XIRR ( TestCF, TestCF[Total CF], TestCF[Date], -0.5 ),
                IFERROR (
                    XIRR ( TestCF, TestCF[Total CF], TestCF[Date], -0.999 ),
                    -1
                )
            )
        )
    ),
    FILTER (
        ALL ( TestCF ),
        CONTAINS ( VALUES ( TestSum_2 ), TestSum_2[Bin], TestCF[Bin] )
    )
)

 

@AlexisOlson got it. SUPER HELPFUL to have a sounding board for issues like this.

WRT learning more and becoming an advanced user of Power Query, Power Pivot and DAX. Any recomendations where to get more free training? I've started watching a few videos from SQLBI.com and myonlinetraininghub. Any better option? Thanks.

Most of my training has been on the job and answering thousands of questions on here and StackOverflow. SQLBI has great articles about DAX. Radacad has good articles too. My new favorite is Data Goblins.

 

Reading articles and watching videos is nice but to really learn you need to actually solve problems yourself, whether your own or other people's.

Using the same file, I am trying to find out the following:

Attribute =Lead  /  Category= JB  

Attribute = Year of Inv.  /  Category: select only greater than 2012.

I also want the flexibility to select any attribute and filter by other Attribute.

For this, it is almost like I need Attribute 1 and Attribute 2. The way is designed now, the pivot does not allow me to select Attribute twice. Do I need to create an attribute#2? It will not work bc the pivot table will filter category based on the first attribute.

Ultimately, I want the same number of selections in Attribute 1 and Category 1 in Attribute 2 and Category 2 so I can slice the pivot table any way I want. Thanks.

Test_PivotTable_Inconsistent results_v2.xlsx

 

Forum, let me know if you need more clarification. I guess I can unpivot column Attribute but I am not sure it is the most efficient solution

v-jialluo-msft
Community Support
Community Support

Hi  @giordani2000 ,

 

You can try this:

CALCULATE (
    IFERROR ( XIRR ( ALL(TestCF), TestCF[Total CF], TestCF[Date], -0.01 ), -1 ),
    FILTER (
        ALL ( TestCF ),
        CONTAINS ( VALUES ( TestSum_2 ), TestSum_2[Portfolio Company], TestCF[Company] )
    )
)

Or provide sample files and expected output.

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

 

Best Regards,
Gallen Luo

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors