Calculate XIRR

[Ashish_Mathur]

Hi,

My objective is to calculated the XIRR as of 31 January 2023 using a measure.  These are my measures

Purchase date of shares = MIN(Shares[Date of Purchase])

Cost of shares (Rs. cr) = -divide(SUM(Shares[Total cost of shares in Rs]),10^7)

Market value of shares (Rs. cr) = divide([Number of shares]*[MV per share],10^7)

 In the Table visual when i drag Investee Company, these measures reuturn the following results

Purchase date of shares = 11 January 1999

Cost of shares = -1.41

Market value of shares = 397.12

In a slicer, i select the end date as 31 January 2023.

In MS Excel, the XIRR as of 31 January 2023, would be 26.41%.

How can i do this calculation in DAX via a measure.

Thank you for your help.

[Ashish_Mathur]

Hi @Greg_Deckler, @amitchandak, @Sahir_Maharaj , @lbendlin .  Could you kinldy help me.

Thank you for your time.

[lbendlin · Accepted solution]

Not sure why I get 27.44% but here's my version.

[Ashish_Mathur]

Hi,

Thank you very much for your reply.  That solution works.

[lbendlin]

Any idea why I got a different number?

[Ashish_Mathur]

Hi,

The date and month were interchanged.

[lbendlin]

D'oh!  Sorry about that.

[Ashish_Mathur]

Hi @lbendlin ,

I am stuck at calculating the XIRR at the aggregate level.  I have given a detailed description in the attached file.  Could you kinldy modify my XIRR formula to get the correct figure at the aggregate level.

Thank you.

[lbendlin]

I don't understand why "Sale realisation of mutual funds (Rs. cr)" is blank for purchase_sale group 3.  I tried to apply a SUMMARIZE filter to remove that row but that impacts the entire table contents.  CALCULATETABLE will result in the same impasse of not being able to filter a table by a measure.

XIRR of mutual funds sold = 
var a = CALCULATETABLE('Mutual funds',[Sale realisation of mutual funds (Rs. cr)]<>BLANK())
return
CALCULATE(XIRR(a,[Amt to be considered],[Transaction date of MF],,BLANK()),DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),[Selected date]),USERELATIONSHIP('Mutual funds'[Transaction Date],'Calendar'[Date]))

Maybe this could be extended with the date ranges  (at the moment it doesn't work):

XIRR of mutual funds sold = 
var a = SUMMARIZE('Mutual funds',[Entity],[Scheme Name],[Purchase_sale group],"amt",[Amt to be considered],"td",[Transaction date of MF],"sd",[Sale realisation of mutual funds (Rs. cr)])
var b = filter(a,[sd]<>BLANK())
return
CALCULATE(XIRR(b,[amt],[td],,BLANK()),DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),[Selected date]),USERELATIONSHIP('Mutual funds'[Transaction Date],'Calendar'[Date]))

I am guessing here, no idea what any of these financial terms mean.

[Ashish_Mathur]

Thank you for replying and giving it a go.  I'll continue trying with the SUMMARIZE function.

[Ashish_Mathur]

Hi,

I have made some progress.  I wrote the following 2 DAX formulas:

Calculated table formula

Table 2 = DATATABLE("Period",DATETIME,"CF",CURRENCY,{{"01/11/1999",-1.41},{"31/01/2023",397.12}})

Measure

XIRR of shares = XIRR('Table2',[CF],[Period])

I get the correct XIRR result i.e. 26.41%

The modification that i wish to bring here is that i would like to replace hard coded entries in the DATATABLE function with measures that i have written.  I'd like

1. 01/11/1999 to be replaced with the measure [Purchase date of shares]

2. -1.41 to be replaced with the measure [Cost of shares (Rs. cr)]

3. 31/01/2023 to be replaced with the measure [Selected period]

4. 397.12 to be replaced with the measure [Market value of shares (Rs. cr)]

How can i do this?

Thank you for your help.