Forum Discussion
Calculating Weighted IRR using XIRR properly
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 |
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])
2 Replies
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.
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])
