Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating IRR using slicer and projected data

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. 

 

DateValueTypeCompany
1/1/20221000RealisationCompany A
2/1/2022-2000InvestmentCompany A
3/1/20223000FeesCompany A
4/1/202210000Enterprise Value (EV)Company A
5/1/20224000InvestmentCompany B

 

DateCompanyEBITDAMultipleEnterprise Value (EV)
4/1/2022Company A5000210000

 

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
			),

 

Slicer
XIRR
  • johnt75's avatar
    johnt75
    4 years ago

    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] )

3 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        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