Forum Discussion
Calculate XIRR
Not sure why I get 27.44% but here's my version.
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.
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.