Forum Discussion
Helper IxIRR 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
| 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
14 Replies
v-jialluo-msftCommunity 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- giordani2000
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?
- giordani2000
v-jialluo-msftand Forum, moving this question to the top. Still looking for advise. Thanks.
- giordani2000
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.