Forum Discussion

Nagesh20's avatar
Nagesh20
Icon for Helper I rankHelper I
2 years ago
Solved

Not finding the solution for XIRR error though all looks good

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/2024B10000000
3/5/2024B01000100
4/5/2024B050050
5/5/2024B025026
6/5/2024B05000502
7/5/2024B00100-100
8/5/2024B0000
9/5/2024B075075
10/5/2024B00250-250
3/5/2023A20000000
4/5/2023A0100001000
5/5/2023A045045
6/5/2023A0025-25
7/5/2023A026036
8/5/2023A03545-10
9/5/2023A01000100
10/5/2024A00026
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Nagesh20's avatar
    Nagesh20
    Icon for Helper I rankHelper I
    2 years ago

    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)
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.