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

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

Reply
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:

 

Subsidy500     
ProjectYear CFCF + Subsidy ProjectIRR
A1/1/2021-1000-500 A60%
A1/1/2022350350 B32%
A1/1/2023250250   
A1/1/2024450450   
A1/1/2025500500   
B1/1/2021-5000-4500   
B1/1/202210001000   
B1/1/202320002000   
B1/1/202430003000   
B1/1/202540004000   

 

Thanks!

1 ACCEPTED SOLUTION
littlemojopuppy
Community Champion
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.

littlemojopuppy_0-1627070168698.png

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

View solution in original post

1 REPLY 1
littlemojopuppy
Community Champion
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.

littlemojopuppy_0-1627070168698.png

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors