XIRR error as starting number is zero

[Nagesh20]

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

Total = SUM(Table2[Call])+SUM(Table2[Dist])-SUM(Table2[NAV])

IRR = XIRR(Table2,Table2[Total],Table2[Date],,0.0001)

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

[v-kongfanf-msft · Accepted 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.

[Nagesh20]

Hi @v-kongfanf-msft 

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