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.

Reply
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 ScenarioSource ParameterImpacted ParameterSource Fiscal YearImpacted DateBaseline/ChangeDirectionValue
Asset 1PricesEarningsnull01/01/2022Baselinenull968517.3
Asset 1PricesEarningsnull01/01/2023Baselinenull774718.9
Asset 1PricesEarnings202201/01/2022ChangePositive732735.6
Asset 1PricesEarnings202201/01/2023ChangePositive936139.7
Asset 1PricesEarnings202301/01/2023ChangePositive935362
Asset 1PricesCashflownull01/01/2020Baselinenull-922165
Asset 1PricesCashflownull01/01/2021Baselinenull214134.3
Asset 1PricesCashflownull01/01/2022Baselinenull416461.4
Asset 1PricesCashflownull01/01/2023Baselinenull876725.6
Asset 1PricesCashflow202201/01/2022ChangePositive914957.2
Asset 1PricesCashflow202201/01/2023ChangePositive369155.2
Asset 1PricesCashflow202301/01/2023ChangePositive147612.4
Asset 1PricesCapexnull01/01/2022Baselinenull303774.2
Asset 1PricesCapexnull01/01/2023Baselinenull159712.9
Asset 1PricesCapex202201/01/2022ChangePositive747143.3
Asset 1PricesCapex202201/01/2023ChangePositive623516
Asset 1PricesCapex202301/01/2023ChangePositive608508.3
Asset 1RatesEarningsnull01/01/2022Baselinenull968517.3
Asset 1RatesEarningsnull01/01/2023Baselinenull774718.9
Asset 1RatesEarnings202201/01/2022ChangePositive732735.6
Asset 1RatesEarnings202201/01/2023ChangePositive936139.7
Asset 1RatesEarnings202301/01/2023ChangePositive935362
Asset 1RatesCashflownull01/01/2020Baselinenull-922165
Asset 1RatesCashflownull01/01/2021Baselinenull214134.3
Asset 1RatesCashflownull01/01/2022Baselinenull416461.4
Asset 1RatesCashflownull01/01/2023Baselinenull876725.6
Asset 1RatesCashflow202201/01/2022ChangePositive914957.2
Asset 1RatesCashflow202201/01/2023ChangePositive369155.2
Asset 1RatesCashflow202301/01/2023ChangePositive147612.4
Asset 1RatesCapexnull01/01/2022Baselinenull303774.2
Asset 1RatesCapexnull01/01/2023Baselinenull159712.9
Asset 1RatesCapex202201/01/2022ChangePositive747143.3
Asset 1RatesCapex202201/01/2023ChangePositive623516
Asset 1RatesCapex202301/01/2023ChangePositive608508.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])

 

View solution in original post

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

 

Ashish_Mathur
Super User
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

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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