Not finding the solution for XIRR error though all looks good

[Nagesh20]

Hi, Please someone solve my query. I am not sure where i am missing the steps for generating IRR. I am strugling a lot for finding the right solution. Provided all the data below with calculations, 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 calcualtion

1)  Running total =

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 total],MAX(Table1[Date])<_maxdate,0,BLANK())

3)

NAV+Cashflow = SUM(Table1[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)

DateFundCommitmentCallDistributionDaily NAV

2/5/2024	B	10000	0	0	0
3/5/2024	B	0	100	0	100
4/5/2024	B	0	50	0	50
5/5/2024	B	0	25	0	26
6/5/2024	B	0	500	0	502
7/5/2024	B	0	0	100	-100
8/5/2024	B	0	0	0	0
9/5/2024	B	0	75	0	75
10/5/2024	B	0	0	250	-250
3/5/2023	A	20000	0	0	0
4/5/2023	A	0	1000	0	1000
5/5/2023	A	0	45	0	45
6/5/2023	A	0	0	25	-25
7/5/2023	A	0	26	0	36
8/5/2023	A	0	35	45	-10
9/5/2023	A	0	100	0	100
10/5/2024	A	0	0	0	26

[v-yohua-msft · Accepted solution]

Hi, @Nagesh20 

Maybe you can try to modify the DAX:

Total Cash_NAV =
VAR currentdate = Table1[Date]
VAR Currentfun = Table1[Fund]
VAR filter_table = FILTER(Table1, Table1[Date] <= currentdate && Table1[Fund] = Currentfun)
VAR runtotal = CALCULATE(SUM(Table1[Daily NAV]), filter_table) -- Running Total
VAR _maxdate_per_fund = CALCULATE(MAX(Table1[Date]), FILTER(ALLSELECTED(Table1), Table1[Fund] = Currentfun)) -- Calculate max date per Fund
VAR filter_table2 = FILTER(Table1, Table1[Date] = currentdate && Table1[Fund] = Currentfun) -- Matching date with Fund name
VAR Total = -SWITCH(TRUE(),
    MAX(Table1[Date]) = _maxdate_per_fund, runtotal,
    MAX(Table1[Date]) < _maxdate_per_fund, 0,
    0
) + CALCULATE(SUM(Table1[Call]) - SUM(Table1[Distribution]), filter_table2) -- (-Ending NAV + Calls - Distribution)
RETURN
IF(Total = 0, 0.001, Total)
 

 If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nagesh20]

Hi all,

Finally i found the solution for the below query using calculated column. Only one concern is left i.e. Ending NAV is taking based on max date in the date column rather than max date of the Fund (tried to figure out this mutliple times), resulting IRR error is getting for Fund C. Please someone fix this issue using colculated column than measure. I am almost close to the answer. Thanks in advance

Total Cash_NAV =

var currentdate=Table1[Date]

var Currentfun=Table1[Fund]

var filter_table=FILTER(Table1,Table1[Date]<=currentdate && Table1[Fund]=Currentfun)

var runtotal =CALCULATE(SUM(Table1[Daily NAV]),filter_table) -- Running Total

var _maxdate = CALCULATE(MAX(Table1[Date]),ALLSELECTED(Table1[Date])) -- for locking ending NAV from Running Total based on max date

var filter_table2=FILTER(Table1,Table1[Date]=currentdate && Table1[Fund]=Currentfun) -- matching date with Fund name

var

Total=

-SWITCH(TRUE(),

MAX(Table1[Date])=_maxdate,runtotal,

MAX(Table1[Date])<_maxdate,0,0) +CALCULATE(SUM(Table1[Call])-SUM(Table1[Distribution]),filter_table2) -- (-Ending NAV + Calls - Distribution)

RETURN

IF(Total=0,0.001,Total)

@Greg_Deckler @Ashish_Mathur @amitchandak @ryan_mayu