Calculating IRR using slicer and projected data

[Anonymous]

Hello

I am trying to calculate the projected IRR for a particular company, i.e. the IRR on a particular date in the future. I have two relevent data tables that I am using, one that provides the various cashflows and one that contains the various fields used to calculate the valuation. 

Date	Value	Type	Company
1/1/2022	1000	Realisation	Company A
2/1/2022	-2000	Investment	Company A
3/1/2022	3000	Fees	Company A
4/1/2022	10000	Enterprise Value (EV)	Company A
5/1/2022	4000	Investment	Company B

Date	Company	EBITDA	Multiple	Enterprise Value (EV)
4/1/2022	Company A	5000	2	10000

I am using a series of slicers to create different adjustments to the EV calculation, i.e. increase multiple by 5%. This then creates a projection of what the EV will be in the future, based on the various adjustments to the variables:

Projected_EV=
SUMX(Company_A_valuation, Company_A_valuation[EBITDA] * (1 + [%_EBITDA_slicer]) * (Company_A_valaution [multiple] + [%_multiple_slicer]))

I am trying to work out how i can use the projected EV and a chosen date in future to feed into the cashflow table and therefore be included in the IRR calculation. I have tried various custom columns to duplicate the cashflow, but also bring in the projected EV at the selected date, but I cannot get it to work. 

Any thoughts or suggestions welcome! Thank you in advance

NB to select the date of when the EV is to be taken from, I have been playing around with a DAX similar to this:

'Cashflow'[Projected_IRR] = 
VAR currentdate = MAX('Calendar'[Date]) 
RETURN

IFERROR(IF(CALCULATE(SUM(CashFlow[end amt]),FILTER('Calendar','Calendar'[Date]=currentdate)) = BLANK(),BLANK(),
		XIRR(	FILTER( 
				SUMMARIZE(
					FILTER(
					ALL('Calendar'),
					Calendar[Date]<=currentdate
					),
				Calendar[Date],
				"TotalCashFlowIRR", IF('Calendar'[Date]=currentdate,SUM(CashFlow[end amt]),BLANK()) + CALCULATE(SUM(CashFlow[flow]))
				),
			[TotalCashflowIRR] <>0
			),
		

[johnt75 · Accepted solution]

You could add in the future date and value by creating a variable as the UNION of the base data and ROW for the future projections, something along the lines of

Rate of return =
VAR tmpTable =
    UNION (
        SELECTCOLUMNS (
            FILTER ( 'Base Table', 'Base Table'[Some column] = "filter value" ),
            "_Date", 'Base Table'[Date],
            "_Value", 'Base Table'[Value]
        ),
        ROW (
            "_Date", SELECTEDVALUE ( 'Future Dates'[Date] ),
            "_Value", [Projected future value measure]
        )
    )
RETURN
    XIRR ( tmpTable, [_Value], [_Date] )

[Anonymous]

Thanks for the suggestion. The principle of this works well and the table created works with the XIRR function, so I am half way there. 

However, for the additional ROW I am struggling to work out a way for the user to be able to select a particular date in the future (currently trying to use a slicer) and then associate a value with that date, in order to use this projected valuation as part of IRR calculation. 

I have tried, for example, to use this measure:

Selected_year = 
        CALCULATE(
        MAX( Calendar[Date] ),
        ALLSELECTED( Calendar[Date] )

to link a slicer to the DAX you suggest, but the _date in the new ROW is unresponsive and just uses the last date. 

Are you able to suggest a measure to use for _date and _value for the new ROW that would allow the user to select a time period and the respective value would be used?

[johnt75]

Create a separate date table which is not connected to anything, just to use for the slicer. That's what I intended the 'Future Dates' table to be in my initial code.

For the value to use, you could use a what if parameter. That automatically creates a measure which returns the selected value, so you can just use that