topic Re: xIRR calculation discrepancy in DAX Commands and Tips

xIRR calculation discrepancy

giordani2000 — Thu, 22 Dec 2022 18:57:33 GMT

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

Re: xIRR calculation discrepancy

v-jialluo-msft — Fri, 23 Dec 2022 08:25:41 GMT

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

Re: xIRR calculation discrepancy

giordani2000 — Fri, 23 Dec 2022 16:58:42 GMT

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?

Re: xIRR calculation discrepancy

giordani2000 — Mon, 02 Jan 2023 02:23:00 GMT

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

Re: xIRR calculation discrepancy

giordani2000 — Wed, 04 Jan 2023 21:48:50 GMT

tamerj1

Greg_Deckler

FreemanZ

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.

Re: xIRR calculation discrepancy

giordani2000 — Wed, 11 Jan 2023 17:10:52 GMT

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

Re: xIRR calculation discrepancy

AlexisOlson — Wed, 11 Jan 2023 19:33:07 GMT

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] ) ) )

Re: xIRR calculation discrepancy

giordani2000 — Thu, 12 Jan 2023 16:29:24 GMT

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.

Re: xIRR calculation discrepancy

AlexisOlson — Thu, 12 Jan 2023 21:37:18 GMT

The file looks the same to me.

Re: xIRR calculation discrepancy

giordani2000 — Thu, 12 Jan 2023 23:03:26 GMT

how about now?

https://1drv.ms/x/s!AhK8zgM_1Ab0e0yc4mq687RV-Nc?e=tHvmzO

Re: xIRR calculation discrepancy

AlexisOlson — Fri, 13 Jan 2023 20:09:29 GMT

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] ) ) )

Re: xIRR calculation discrepancy

giordani2000 — Fri, 13 Jan 2023 21:47:43 GMT

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.

Re: xIRR calculation discrepancy

AlexisOlson — Fri, 13 Jan 2023 23:46:27 GMT

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.

Re: xIRR calculation discrepancy

giordani2000 — Mon, 20 Mar 2023 18:09:35 GMT

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

Re: xIRR calculation discrepancy

giordani2000 — Wed, 22 Mar 2023 20:32:56 GMT

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