Solution to XIRR with Terminal Values

[austinsense]

I want to share this with the community because it should be helpful to lots of finance people working with DAX.  Power BI and Excel 2016 now have an XIRR function which is great but it's not designed to gracefully handle terminal values which are very common in IRR calculations.  Let's say you own a rental property you can think of the cashflow as the rent people are paying you every month and you can think of a terminal value as "What if I sold this property today, how much would i get for it?".  In calculating IRR people frequently create an estimated terminal value for every period.

So typically in cashflow data you have a series of cashflows in one column of a dataset and in another column a terminal value at the end of every period.  When calculating IRR you want to merge these two columns together capturing all of the cashflows and only the most recent terminal value.  In other words for your final report, on a specific date you want to calculate IRR including all of the cashflows prior to and including the report date plus the terminal value for the date in your report - you want to ignore all of the other terminal values.

The struggle here is that the XIRR function only allows you to have one column of cashflows - so how do we get these two columns into one column and choose the right terminal value? Thank you to johnniet and kelland for helping me think through this.

Austin

Here's the code. If you haven't already, download a copy of DAX studio - it makes working with complex measures much, much easier http://daxstudio.codeplex.com/

DEFINE MEASURE
'Cashflow'[NEWIRR] = VAR currentdate = MAX('Calendar'[Date]) // Use a variable to identify the most current date in the filter context
RETURN

// Total Formula Error Handling
IFERROR(
	
	// Filtering out dates that don't have terminal values
	IF(CALCULATE(SUM(CashFlow[end amt]),FILTER('Calendar','Calendar'[Date]=currentdate)) = BLANK(),BLANK(),
		XIRR(
			
		// This is the first argument of the XIRR function
			// 1. Returns a table for the given filter context that includes all the dates ("ITD", inception to date),
			// 2. Adds the monthly cash flow to every period ITD,
			// 3. Adds the reminal value only if the period is the final period
				
			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
			),
		
		// These are the final three arguments to the XIRR Function
		[TotalCashFlowIRR],
		'Calendar'[Date], .05
		)
	),
BLANK())


// I'm using this query to test the new measure - this is not part of the measure
EVALUATE(
FILTER(
	SUMMARIZE(
		Calendar,
		Calendar[YYYY-MM],
		"IRRMeasure", FORMAT([NEWIRR],"Percent")
	),
	[IRRMeasure] <> BLANK()
))

[DVA496]

Hi, thanks a lot for posting this. I have a similar challenge yet my terminal value is a measure and the CF I need to consider is from inception till cutoffdate (which is settled as a filter) I have describe it in this post XIRR with calculated terminal value (face value) - Microsoft Fabric Community could you help me or guide me towards another way of approaching it? 

Best regards,

Dyana V.

[mjfigg]

Hi @austinsense,

Thanks for your post. I found it somewhat helpful, although I have made my own code to deal with a similar problem. I wanted to find what my rolling IRR is for every date instead of a specific date. Also in the dataset, I have a number of different assets (Name), that I need to seperately calculate rolling IRRs for, adding an additional level of complexity. 

Fianlly, I have included a couple of checks to ensure that the formula will not error if data is queried that cannot be used in the IRR calculation. These checks include:

 - checking to ensure that the first value in the column is a negative, and

 - ensuring there is at least one positive and one negative value in the dataset.

A big thanks to @v-ljerr-msft for their help!!

Cheers,

MJ

ROLLING XIRR = 
VAR countpos =
    CALCULATE(
        COUNT('INVESTMENT RETURNS'[totalcashflow]),
            FILTER(ALL('INVESTMENT RETURNS'), 'INVESTMENT RETURNS'[totalcashflow]>0 && EARLIER ('INVESTMENT RETURNS'[Index] ) >= 'INVESTMENT RETURNS'[Index] && 'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
              )
           )

VAR minIndex =
    CALCULATE (
        MIN ( 'INVESTMENT RETURNS'[Index] ),
        FILTER (
            ALL ( 'INVESTMENT RETURNS' ),
            'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
        )
    )
VAR firstValue =
    CALCULATE (
        MIN ( 'INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD (excl. sale)] ),
        FILTER (
            ALL ( 'INVESTMENT RETURNS' ),
            'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] )
                && 'INVESTMENT RETURNS'[Index] = minIndex
        )
    )
RETURN
    IF (
        'INVESTMENT RETURNS'[Index] > minIndex
            && firstValue < 0
            && countpos >= 1 ,
            XIRR (
                UNION( 
                    SUMMARIZE( 
                        FILTER(ALL ( 'INVESTMENT RETURNS' ), EARLIER ('INVESTMENT RETURNS'[Index] ) >= 'INVESTMENT RETURNS'[Index] && 'INVESTMENT RETURNS'[Name] = EARLIER ( 'INVESTMENT RETURNS'[Name] ))
                        ,'INVESTMENT RETURNS'[Index],
                        "DATE1",MAX('INVESTMENT RETURNS'[DATE]),
                        "CASHFLOW",MAX('INVESTMENT RETURNS'[FREE CASHFLOW FOR PERIOD (excl. sale)])
                            ),
                    SUMMARIZE(
                        FILTER(ALL ( 'INVESTMENT RETURNS' ), EARLIER ('INVESTMENT RETURNS'[Index] ) = 'INVESTMENT RETURNS'[Index] ),
                        'INVESTMENT RETURNS'[Index],
                        "DATE1",MAX('INVESTMENT RETURNS'[DATE]),
                        "CASHFLOW",MAX('INVESTMENT RETURNS'[EQUITY REPAYMENT/(INVESTMENT) ON SALE])
                            )
                ),
                [CASHFLOW],
                [DATE1]
            )
    )

[dpombal]

How can I tune a basic measure using XIRR, i think it is causing slow performance on a table (limit 1gb per chart in powerbi service)

something like indexing dates, i am using XIRR basically

[jasonjs]

Hi @mjfigg,

I am looking for exacly what you have described. I've copied your formulas bet get a number of errors including "Earlier/Earliest refers to an earlier row context which doesn't exist". Any chance you could proivide a copy/screenshot of your Source table and the Power BI File?

Thanks,

JS

[austinsense]

This formula needs some re-work. I've been meaning to update it. Let me give it some work.

[sebast]

Hi Autinsense, 

Thank you very much for sharing the solution. I am facing exactly the same problem as you describe, however I can't understand the code and how to implement it within excel. 

Could you please highlight the the key information to be put in excel. I would be really grateful.

Thank you very much,

Sebastien  

[sebast]

Hi Autinsense, 

Thank you very much for sharing the solution. I am facing exactly the same problem as you describe, however I can't understand the code and how to implement it within excel. 

Could you please highlight the the key information to be put in excel. I would be really grateful.

Thank you very much,

Sebastien  

[v-sihou-msft]

Thanks for sharing your solution.