Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am trying to calculate XIRR for the below table. It's getting error as starting number is zero. Please suggest on the below formula how to hide zero in the calculation rather than sorting date in asc or desc order. In excel, without starting zero, it's getting 10.358% IRR.
Backend calculation
DateProductCommitmentCallDist
5/2/2023 | A | 10000.00 | 0 | 0 |
5/3/2023 | A | 0.00 | 1000 | 0 |
5/4/2023 | A | 0.00 | 0 | -50 |
5/5/2023 | A | 0.00 | 0 | -50 |
5/6/2024 | A | 0.00 | 0 | 0 |
2/28/2023 | B | 2000.00 | 0 | 0 |
3/1/2023 | B | 0.00 | 2000 | 0 |
3/2/2023 | B | 0.00 | 0 | -100 |
5/6/2024 | B | 0.00 | 0 | -100 |
5/7/2024 | B | 0.00 | 0 | 0 |
Solved! Go to Solution.
Hi @Nagesh20 ,
You can create a measure as a judgment condition. Refer to the following formula:
a = IF([Total]= 0, 0.0001, Table2[Total])
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Nagesh20 ,
You can create a measure as a judgment condition. Refer to the following formula:
a = IF([Total]= 0, 0.0001, Table2[Total])
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have applied the stepps correctly for generating IRR but not sure where i am missing the steps in the calculations. I am strugling a lot for finding the right solution. Provided all the data below with calcations, tables. Thanks in advance
In Excel, it's getting IRR for fund A is 3.11%, B is 46% and total IRR is 3.35%
Backend calculations
1) Running Tota =
CALCULATE(SUM(Table1[Daily NAV]),
FILTER(ALL(Table1[Date]),Table1[Date]<=MAX(Table1[Date])))
2) Ending NAV =
var _maxdate = CALCULATE(MAX(Table1[Date]),ALLSELECTED(Table1[Date]))
RETURN
SWITCH(TRUE(),MAX(Table1[Date])=_maxdate,[Running Tota],MAX(Table1[Date])<_maxdate,0,BLANK())
3) NAV+Cashflow = SUM(Table1[Capital call])-SUM(Table1[Distribution])-[Ending NAV]
4) IRR =
var nonzero = IF([NAV+Cashflow]=0,1,[NAV+Cashflow])
RETURN
XIRR(Table1,nonzero,Table1[Date],,0.00001)
DateFundCommitmentCapital callDistributionDaily NAV
02-05-2024 | B | 10000 | 0 | 0 | 0 |
03-05-2024 | B | 0 | 100 | 0 | 100 |
04-05-2024 | B | 0 | 50 | 0 | 50 |
05-05-2024 | B | 0 | 25 | 0 | 26 |
06-05-2024 | B | 0 | 500 | 0 | 502 |
07-05-2024 | B | 0 | 0 | 100 | -100 |
08-05-2024 | B | 0 | 0 | 0 | 0 |
09-05-2024 | B | 0 | 75 | 0 | 75 |
10-05-2024 | B | 0 | 0 | 250 | -250 |
03-05-2023 | A | 20000 | 0 | 0 | 0 |
04-05-2023 | A | 0 | 1000 | 0 | 1000 |
05-05-2023 | A | 0 | 45 | 0 | 45 |
06-05-2023 | A | 0 | 0 | 25 | -25 |
07-05-2023 | A | 0 | 26 | 0 | 36 |
08-05-2023 | A | 0 | 35 | 45 | -10 |
09-05-2023 | A | 0 | 100 | 0 | 100 |
10-05-2024 | A | 0 | 0 | 0 | 26 |
User | Count |
---|---|
93 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |