Calculating Weighted IRR using XIRR properly

[Anonymous]

Hi, 
I am trying to calculate the IRR (funtion: XIRR) for the following data set and return a specific calcualtion.

What I`m trying to do
Calculating a Weighted IRR for each asset: (IRR of each asset under each source parameter * Sum of Capex for that asset)
To be done 2 times:
1. For only Baseline values only in column [Baseline/Change]:
Then multiply the IRR of each asset by the sum of "Capex" values in column [Impacted Parameter] for that asset.
XIRR based on [values] & [Impacted Date] with filters: [Impacated Parmater]="Cashflow" & [Baseline/Change]="Baseline"
*
Sum of [Value] with filters: [Impacated Parmater]="Capex" & [Baseline/Change]="Baseline"

2. For all Baseline and change values in column [Baseline/Change]:
Then multiply the IRR of each asset by the sum of "Capex" values in column [Impacted Parameter] for that asset.
XIRR based on [values] & [Impacted Date] with filters: [Impacated Parmater]="Cashflow"&[Direction]="Positive"OR"Null"
*
Sum of [Value] with filters: [Impacted Parameter]="Capex"&[Direction]="Positive"OR"Null"

ii.Compare the two by the following formula 
(Item 2 divided by Sum of Capex of all assets
-
Item 1 divided by Sum of Capex of all assets)
-1

iii. Show results on bar graph with column [Source Parameter] on y-axis subject to filters on [Asset Scenario] and [Source Fiscal Year]

Here is my sample data:

Asset Scenario	Source Parameter	Impacted Parameter	Source Fiscal Year	Impacted Date	Baseline/Change	Direction	Value
Asset 1	Prices	Earnings	null	01/01/2022	Baseline	null	968517.3
Asset 1	Prices	Earnings	null	01/01/2023	Baseline	null	774718.9
Asset 1	Prices	Earnings	2022	01/01/2022	Change	Positive	732735.6
Asset 1	Prices	Earnings	2022	01/01/2023	Change	Positive	936139.7
Asset 1	Prices	Earnings	2023	01/01/2023	Change	Positive	935362
Asset 1	Prices	Cashflow	null	01/01/2020	Baseline	null	-922165
Asset 1	Prices	Cashflow	null	01/01/2021	Baseline	null	214134.3
Asset 1	Prices	Cashflow	null	01/01/2022	Baseline	null	416461.4
Asset 1	Prices	Cashflow	null	01/01/2023	Baseline	null	876725.6
Asset 1	Prices	Cashflow	2022	01/01/2022	Change	Positive	914957.2
Asset 1	Prices	Cashflow	2022	01/01/2023	Change	Positive	369155.2
Asset 1	Prices	Cashflow	2023	01/01/2023	Change	Positive	147612.4
Asset 1	Prices	Capex	null	01/01/2022	Baseline	null	303774.2
Asset 1	Prices	Capex	null	01/01/2023	Baseline	null	159712.9
Asset 1	Prices	Capex	2022	01/01/2022	Change	Positive	747143.3
Asset 1	Prices	Capex	2022	01/01/2023	Change	Positive	623516
Asset 1	Prices	Capex	2023	01/01/2023	Change	Positive	608508.3
Asset 1	Rates	Earnings	null	01/01/2022	Baseline	null	968517.3
Asset 1	Rates	Earnings	null	01/01/2023	Baseline	null	774718.9
Asset 1	Rates	Earnings	2022	01/01/2022	Change	Positive	732735.6
Asset 1	Rates	Earnings	2022	01/01/2023	Change	Positive	936139.7
Asset 1	Rates	Earnings	2023	01/01/2023	Change	Positive	935362
Asset 1	Rates	Cashflow	null	01/01/2020	Baseline	null	-922165
Asset 1	Rates	Cashflow	null	01/01/2021	Baseline	null	214134.3
Asset 1	Rates	Cashflow	null	01/01/2022	Baseline	null	416461.4
Asset 1	Rates	Cashflow	null	01/01/2023	Baseline	null	876725.6
Asset 1	Rates	Cashflow	2022	01/01/2022	Change	Positive	914957.2
Asset 1	Rates	Cashflow	2022	01/01/2023	Change	Positive	369155.2
Asset 1	Rates	Cashflow	2023	01/01/2023	Change	Positive	147612.4
Asset 1	Rates	Capex	null	01/01/2022	Baseline	null	303774.2
Asset 1	Rates	Capex	null	01/01/2023	Baseline	null	159712.9
Asset 1	Rates	Capex	2022	01/01/2022	Change	Positive	747143.3
Asset 1	Rates	Capex	2022	01/01/2023	Change	Positive	623516
Asset 1	Rates	Capex	2023	01/01/2023	Change	Positive	608508.3

[Anonymous · Accepted solution]

@Ashish_Mathur Indeed it`s quite a complex challenge, it is simply a try to calculate weighted IRR (against Capex) of 2 subsets of the data and comparing them while applying multiple filters.

I was able to resolve it and I hope the code itself describes better the solution:

WAIRR Change test =

//calculations for IRR of baseline only:
var tableIRRbase0=SUMMARIZE(CALCULATETABLE(
'result',KEEPFILTERS('result'[Baseline/Change]="Baseline"),ALL('result'[Source Fiscal Year])),
'result'[Asset Scenario ID],'result'[Source Parameter],
"IRR",XIRR(FILTER('result','result'[Impacted Parameter]="Cashflow"&&'result'[Baseline/Change]="Baseline"),[Value],'result'[Impacted Date]),
"Capex",
SUMX(FILTER(('result'),'result'[Impacted Parameter]="Capex"&&'result'[Baseline/Change]="Baseline"),'result'[Value])
)
var tableIRRbase1= ADDCOLUMNS(tableIRRbase0,"WIRR",[Capex]*[IRR])
var tableIRRbase2=ADDCOLUMNS(tableIRRbase1,"C",[WIRR]/CALCULATE(SUMX(tableIRRbase1,[Capex])))

//calculations for IRR of baseline and change:
var tablechange0= SUMMARIZE(
UNION(
CALCULATETABLE('result',KEEPFILTERS('result'[Baseline/Change]="Baseline"),ALL('result'[Source Fiscal Year])),
CALCULATETABLE('result',KEEPFILTERS('result'[Baseline/Change]="Change"),KEEPFILTERS('result'[Stress Direction]="Positive"))),
'result'[Asset Scenario ID],'result'[Source Parameter],
"IRR",XIRR(FILTER('result','result'[Impacted Parameter]="Cashflow"),[Value],'result'[Impacted Date]),
"Capex",
SUMX(FILTER(('result'),'result'[Impacted Parameter]="Capex"),'result'[Value])
)

var tableIRRchange1= ADDCOLUMNS(tablechange0,"WIRR",[Capex]*[IRR])
var tableIRRchange2=ADDCOLUMNS(tableIRRchange1,"C",[WIRR]/CALCULATE(SUMX(tableIRRchange1,[Capex])))

//Returning the required difference calculation
return (SUMX(tableIRRchange2,[C])-SUMX(tableIRRbase2,[C]))/SUMX(tableIRRbase2,[C])

[Ashish_Mathur]

Hi,

It is way to complex to understand so much of textual information.  In an MS Excel workbook, show the formulas that you would have writen had this been an Excel problem.  Give notes/comments there for better understanding.