Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Sym | 3/19/2013 | -2125000 | -2125000 | |
Sym | 10/10/2014 | -500000 | -500000 | |
Sym | 12/10/2014 | -500000 | -500000 | |
Sym | 12/2/2015 | 362591 | 362591 | |
Sym | 12/8/2015 | 6 | 6 | |
Sym | 12/2/2019 | 0 | 0 | |
AqSys | 6/4/2010 | -2142857 | -2142857 | |
AqSys | 2/17/2011 | -3085714 | -3085714 | |
AqSys | 4/27/2012 | -1707314 | -1707314 | |
AqSys | 1/15/2014 | -2489833 | -2489833 | |
AqSys | 1/15/2014 | -235001 | -235001 | |
AqSys | 10/27/2015 | 25911452 | 25911452 | |
AqSys | 12/2/2016 | 9559646 | 9559646 | |
AqSys | 4/21/2017 | 2765590 | 2765590 | |
AqSys | 5/22/2017 | 2370391 | 2370391 | |
AqSys | 11/5/2019 | 0 | 0 | |
AqSys | 12/2/2019 | 0 | 0 |
Photos:
cashflows
Other attributes
Output
Solved! Go to Solution.
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] )
)
)
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] )
)
)
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.
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.
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
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
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |