Forum Discussion
Aggregate XIRR Calculation Help
Hi community, I have been busting my head on this and nothing has been working as expected. TLDR: Trying to get an aggregated level XIRR across multiple accounts. Longer story: I have an...
Hi Anonymous,
If these solutions do not resolve the issue, please consider raising a Microsoft support ticket. You can create a Microsoft support ticket using the link below:
https://learn.microsoft.com/en-us/power-bi/support/create-support-ticket
Thank you.
Per Account Level:
Account Level XIRR =
VAR CurrentAct = 'Sheet1'[Account]
VAR CurrentQuarter = 'Sheet1'[Quarter]
VAR PriorQuarterFlows =
SELECTCOLUMNS(
FILTER(
'Sheet1',
'Sheet1'[Quarter] < CurrentQuarter &&
'Sheet1'[Quarter] >= 'Sheet1'[Earliest Contrib Date] &&
'Sheet1'[Account] = CurrentAct
),
"CashFlow", 'Sheet1'[contrib+distrib],
"FlowDate", 'Sheet1'[Quarter]
)
VAR CurrentQuarterFlow =
SELECTCOLUMNS(
FILTER(
'Sheet1',
'Sheet1'[Quarter] = CurrentQuarter &&
'Sheet1'[Account] = CurrentAct
),
"CashFlow", 'Sheet1'[contrib+distrib+nav],
"FlowDate", 'Sheet1'[Quarter]
)
VAR CombinedFlows =
UNION(PriorQuarterFlows, CurrentQuarterFlow)
RETURN
IFERROR(
XIRR(CombinedFlows, [CashFlow], [FlowDate]),
-1
)Attempt for Aggregate XIRR:
CalcCashFlow =
VAR CurrentAct = 'Sheet1'[Account]
VAR CurrentQuarter = 'Sheet1'[Quarter]
VAR PriorQuarterFlows =
SELECTCOLUMNS(
FILTER(
'Sheet1',
'Sheet1'[Quarter] < CurrentQuarter &&
'Sheet1'[Quarter] >= 'Sheet1'[Earliest Contrib Date] &&
'Sheet1'[Account] = CurrentAct
),
"CashFlow", 'Sheet1'[contrib+distrib],
"FlowDate", 'Sheet1'[Quarter]
)
VAR CurrentQuarterFlow =
SELECTCOLUMNS(
FILTER(
'Sheet1',
'Sheet1'[Quarter] = CurrentQuarter &&
'Sheet1'[Account] = CurrentAct
),
"CashFlow", 'Sheet1'[contrib+distrib+nav],
"FlowDate", 'Sheet1'[Quarter]
)
VAR CombinedFlows =
UNION(PriorQuarterFlows, CurrentQuarterFlow)
RETURN
SUMX(CombinedFlows,[CashFlow])
IRR Check =
VAR mainflow =
SELECTCOLUMNS(
'Sheet1',
"QTR", 'Sheet1'[Quarter],
"CalcCashflow", 'Sheet1'[CalcCashFlow]
)
VAR maingroup =
GROUPBY(
mainflow,
[QTR],
"Cashflow", SUMX(CURRENTGROUP(), [CalcCashflow])
)
VAR mainrow =
SELECTCOLUMNS(
maingroup,
"period",[QTR],
"cf",[Cashflow]
)
VAR mainfilter =
FILTER(mainrow,[cf] <> 0)
VAR maincalc =
IFERROR(
XIRR(mainfilter, [cf], [period]),
-1
)
RETURN
maincalc