Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ashish_Mathur
Super User
Super User

Calculate XIRR

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.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
1 ACCEPTED SOLUTION

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

 

 

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

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

Thank you for your time.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Any idea why I got a different number?

Hi,

The date and month were interchanged.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

D'oh!  Sorry about that.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.