XIRR error with different methods

[Nagesh20]

Hi All,

I have used the following two methods to calculate XIRR. Both are giving the right values as like as in the excel. But when coming to calculate XIRR, it’s getting error. In excel, it’s showing IRR of around 2.2%. After struggling for many hrs, I am reaching out for the help. Please someone assist on this query. Any either of below method works for IRR calculation below, that would suffice.

Total_Value 1st method =

var Running_ =

CALCULATE(SUM(Fund[Daily NAV]),FILTER(ALL(Fund[Date]),Fund[Date]<=MAX(Fund[Date]))) -- Running total for daily NAV

Var _max=maxx(filter(allselected(Fund), Fund[Fund] = max(Fund[Fund])&&Fund[User]=MAX(Fund[User]) ), Fund[Date])

return

-Switch ( true(),Max(Fund[Date]) = _max ,Running_,Max(Fund[Date]) < _max ,0.1) -- locking Ending NAV from Running total based on Max date

+SUM(Fund[Call])-SUM(Fund[Distribution])

IRR_1st method = XIRR(Fund,Fund[Total_Value 1st method],Fund[Date],,0.0001)

2nd method

Total_Value 2nd method =

var Running_ =

CALCULATE(SUM(Fund[Daily NAV]),FILTER(ALL(Fund[Date]),Fund[Date]<=MAX(Fund[Date]))) -- Running total for daily NAV

Var _max=maxx(filter(allselected(fund), Fund[Fund]= max(Fund[Fund])&&Fund[User]=MAX(Fund[User]) ), Fund[Date])

return

-Switch ( true(),Max(Fund[Date]) = _max ,Running_, Max(Fund[Date]) < _max ,0.1,blank()) -- locking Ending NAV from Running total based on Max date

+SUM(Fund[Call])-SUM(Fund[Distribution])

IRR_2nd method = XIRR(Fund,Fund[Total_Value 2nd method],Fund[Date],,0.0001)

Date	User	Fund	Call	Distribution	Daily NAV
4/5/2022	Second	Fund1	500	0	500
6/5/2022	Second	Fund1	0	25	-25
7/5/2022	Second	Fund1	26	0	36
7/6/2022	Second	Fund1	0	0	0
8/5/2022	Second	Fund1	35	45	-10
9/5/2022	Second	Fund1	100	0	100
4/5/2023	Third	Fund1	1000	0	1000
5/5/2023	Third	Fund1	45	0	45
5/5/2023	Second	Fund1	25	0	25
6/5/2023	Third	Fund1	0	25	-25
7/5/2023	Third	Fund1	26	0	36
8/5/2023	Third	Fund1	35	45	-10
9/5/2023	Third	Fund1	100	0	100
9/5/2023	Second	Fund1	0	0	26
3/5/2024	First	Fund1	100	0	100
4/5/2024	First	Fund1	50	0	50
5/5/2024	First	Fund1	25	0	26
6/5/2024	First	Fund1	500	0	502
7/5/2024	First	Fund1	0	100	-100
9/5/2024	First	Fund1	75	0	75
9/5/2024	Second	Fund1	0	0	0
10/5/2024	First	Fund1	0	250	-250
10/5/2024	Third	Fund1	0	0	26

[amitchandak]

@Nagesh20 , refer, this can help

https://www.youtube.com/watch?v=N1WRSFuaFmE