Forum Discussion
Helper IXIRR error as starting number is zero
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 |
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Nagesh20Hi Anonymous
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