cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## What If Investment Cost Subsidy and IRR

Hello,

I am looking to create a dynamic tool that allows the user to decide how much subsidy to provide and generate a revised IRR. I'm not sure how to use a measure to create this calculation so it's dynamic. I need Subsidy to be changable by the user.

Here is an example:

 Subsidy 500 Project Year CF CF + Subsidy Project IRR A 1/1/2021 -1000 -500 A 60% A 1/1/2022 350 350 B 32% A 1/1/2023 250 250 A 1/1/2024 450 450 A 1/1/2025 500 500 B 1/1/2021 -5000 -4500 B 1/1/2022 1000 1000 B 1/1/2023 2000 2000 B 1/1/2024 3000 3000 B 1/1/2025 4000 4000

Thanks!

1 ACCEPTED SOLUTION
Community Champion

Hi @Anonymous

I'll attach a pbix file with the whole solution.  Note that the only way to get user changeable values is using a What If parameter.  Here's a screen snip.

First column is the IRR for unsubsidized cash flows.  The second is what you're looking for...subsidized cash flows.  The third is a check based on the CF + Subsidy column in the data above.

Here's the code for the second measure

``````IRR Subsidy =
VAR CashFlows =
SUMMARIZE(
Data,
Data[Project],
Data[Year ],
Data[CF]
)
VAR EarliestPeriod =
MINX(
CashFlows,
Data[Year ]
)
VAR ModifiedCashFlow =
CashFlows,
"CashFlow + Subsidy",
IF(
Data[Year ] = EarliestPeriod,
Data[CF] + Subsidy[Subsidy Value],
Data[CF]
)
)
RETURN

XIRR(
ModifiedCashFlow,
[CashFlow + Subsidy],
Data[Year ]
)``````

First variable is just summarizing your cash flows into a table variable.  Second variable determines the earliest cash flow period.  Third variable adds a column based on if the year is the earliest period then add the subsidy otherwise don't.  The XIRR function calculates the IRR based on the third table variable.

Hope this helps!  🙂

Community Champion

Hi @Anonymous

I'll attach a pbix file with the whole solution.  Note that the only way to get user changeable values is using a What If parameter.  Here's a screen snip.

First column is the IRR for unsubsidized cash flows.  The second is what you're looking for...subsidized cash flows.  The third is a check based on the CF + Subsidy column in the data above.

Here's the code for the second measure

``````IRR Subsidy =
VAR CashFlows =
SUMMARIZE(
Data,
Data[Project],
Data[Year ],
Data[CF]
)
VAR EarliestPeriod =
MINX(
CashFlows,
Data[Year ]
)
VAR ModifiedCashFlow =
CashFlows,
"CashFlow + Subsidy",
IF(
Data[Year ] = EarliestPeriod,
Data[CF] + Subsidy[Subsidy Value],
Data[CF]
)
)
RETURN

XIRR(
ModifiedCashFlow,
[CashFlow + Subsidy],
Data[Year ]
)``````

First variable is just summarizing your cash flows into a table variable.  Second variable determines the earliest cash flow period.  Third variable adds a column based on if the year is the earliest period then add the subsidy otherwise don't.  The XIRR function calculates the IRR based on the third table variable.

Hope this helps!  🙂