What If Investment Cost Subsidy and IRR

[Anonymous]

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!

[littlemojopuppy · Accepted solution]

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 =
    ADDCOLUMNS(
        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!  🙂