# XIRR and terminal value

#### nickmarsden76

##### New Member
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.

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.

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?

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.

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?

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.

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.

Replies
2
Views
1K
Replies
2
Views
4K
Replies
1
Views
448
Replies
6
Views
891
Replies
8
Views
7K

1,219,094
Messages
6,146,268
Members
450,682
Latest member
augusto89

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back