XIRR and terminal value

[nickmarsden76]

Hi - when using XIRR (or even IRR) for a series of cashflows, should the 'terminal period' cashflow be added to the last forecast period or should it be placed in the next cell. For example, if you have five years of 'forecast' cashflows, should the data be arranged as follows:

Yr 0 - (£50,000) [cash outflow]
Yr 1 - £10,000 [yr1 cashflow]
Yr 2 - £12,000
Yr 3 - £16,000
Yr 4 - £20,000
Yr 5 - £25,000 [yr5 cashflow]
Yr 6 - £100,000 [terminal value]

...or, should the last cashflow be added to the Yr 5 cashflow (so nothing in Yr 6, BUT £125,000 in Yr 5)?

I'm looking at this from a business valuation / corp finance perspective, so the terminal value is not actually cash that will be received, but rather a value placed on the business from yr 10 to infinity.

 

[Oaktree]

Welcome to the board.

I'm looking at this from a business valuation / corp finance perspective, so the terminal value is not actually cash that will be received, but rather a value placed on the business from yr 10 to infinity.

If that's the case, it would just depend on the discount assumption you used to come up with the £100,000 valuation. If you summed the future cash flows starting in year 6, you'd put your £100,000 in year 6, etc.

 

[nickmarsden76]

thanks for the welcome.

I think I may have mislead by calling it the 'terminal value'. The £100,000 actually represents a non-discounted terminal free cashflow. The way it is calculated is by simply taking the yr5 cashflow and multiplying by (1 + terminal growth rate).

Therefore, I suppose it could be seen as a Yr6 cashflow, because it is simply one-year's worth of the terminal growth rate. Do you agree?

If so, then when using the XIRR function and introducing dates, I would think that the terminal free cashflow would need to have a date assigned to it that is one year (to match one year of terminal growth) after the date of the last forecast cashflow.

Does that make sense?

 

[Oaktree]

I'm not convinced that your "terminal value" is calculated by multiplying yr5's cashflow by (1+terminal growth rate)... seems to me that that would assume your terminal growth rate is 300% since your terminal value is 100 and yr5 is 25. Your 100 seems reasonable, but I think you have the calculation to get there wrong.

I believe the rest of your assertion is correct.

 

[nickmarsden76]

You're right Oaktree - apologies, I'm new at this message board business and I think the excitement has got to me! The terminal value formula (as you prob know) is actually:

Yr5 cashflow x (1 + terminal growth rate)
-------------------------------------------------
WACC - terminal growth rate

So, does my previous argument still stand? Should I be treating this value as if it occurs in Yr6?

 

[Oaktree]

It's been 5 years since I did a problem like that, so you probably want to verify the calculation with an old finance textbook; but, if I had to guess, I'd say it's right to put it in yr6.

 

[nickmarsden76]

Thanks. I haven't found any specific reference to XIRR in the textbooks I've seen, so if anyone else can shed any light on the assumptions implicit in the Excel function that would be great.