Forum Discussion

giordani2000's avatar
giordani2000
Helper I
3 years ago
Solved

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

 

Other attributes

 

Output

 

 

  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    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] )
    )
)
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    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's avatar
    AlexisOlson
    3 years ago

    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.

14 Replies