Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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 2
Anonymous
Not applicable

@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])

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors