Forum Discussion

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago
Solved

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) = ...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

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

     

     

lbendlin's avatar
lbendlin
Super User
3 years ago

D'oh!  Sorry about that.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

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's avatar
    lbendlin
    Super User
    3 years ago

    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's avatar
    Ashish_Mathur
    Super User
    3 years ago

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