XIRR and terminal value

nickmarsden76

New Member
Joined
Sep 25, 2006
Messages
4
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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

New Member
Joined
Sep 25, 2006
Messages
4
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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

New Member
Joined
Sep 25, 2006
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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

New Member
Joined
Sep 25, 2006
Messages
4
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.
 

Forum statistics

Threads
1,136,584
Messages
5,676,659
Members
419,638
Latest member
GlenMc52

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top