cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!  🙂

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors