Calculating IRR of terminal value cash flow
How do you appropriately calculate the IRR for the Terminal Value Cash Flow in excel?
Example:
3/30/16 - Year 1 CF: -50
3/30/17 - Year 2 CF: 120
3/30/18 - Year 3 CF: 150
Terminal Year CF: 275
XIRR(Values, Dates) is standard formula, but what "date" do we realize for terminal Year? If we use 3/30/18 (year 3) date, doesn't that skew the IRR way too favorably?
IRR with Terminal Value
We compute cash flows
for a reasonable period, and then compute a terminal value for this
project, which is the present value of all cash flows that occur after the
estimation period ends..
Why do realize the terminal value at the final year of the project?
From @trader_timmy"
Think about the two primary methods of calculating a terminal value. For the sake of this, let's just assumed year 5 is your terminal year. EMM implies an exit at the end of Year 5. So you'd receive $X.X cash at the end of the year from selling company, and as such would only discount back from Year 5 to present day.
You have it right. Typically, the terminal cash flow would arrive in your final (terminal) projection year, so it would have the same date as your last projected cash flow. Since you're receiving that terminal cash flow in your final projection year - either from selling the business or the PV of CFs as a going concern - it would be incorrect to not include it in your final projection year.
The concept I'm struggling to grasp is that since the date range is an important factor in the IRR calc, why would we assign such a favorable date (last projected year) to the TV CF? Shouldn't there be a method where the date is discounted so that it represents the time range of "perpetuity", just like how there's a mechanism to do that for calculating TV CF? Or is that already done inherently when we use the TV CF in the IRR Calc?
Thanks!
I get what you're thinking, but it's all about the timing of those cash flows. That date is not purposely favorable, that's just the date your terminal cash flow would arrive. The method(s) of calculating terminal value provide a number that is already PV'd to your terminal year, so your IRR calculation only needs to discount from terminal year to present.
Think about the two primary methods of calculating a terminal value. For the sake of this, let's just assumed year 5 is your terminal year. EMM implies an exit at the end of Year 5. So you'd receive $X.X cash at the end of the year from selling company, and as such would only discount back from Year 5 to present day.
For gordon growth, the formula calculates the present value of a growing perpetuity, so when you calculate TV based on your Year 5 FCF number, you're calculating the PV of future cash flows as of Year 5. Both methods are providing you a number stated in Year 5 dollars, so in the context of an IRR calc, you'd be receiving that cash flow in Year 5.
I think the only proper way to calculate an IRR when there is a terminal value is to use the goal seek/TV solver.
I'm not sure the XIRR function can get you there.....
Add terminal year and terminal value cash flows together for terminal year cash flow cell and you shouldn't have a problem
What discount rate would you use for the terminal period?
Ut ut praesentium officiis temporibus. Expedita rerum autem similique rerum vero. Quae error exercitationem rem magnam.
Dolorum est labore aliquid sit. Doloribus nobis nobis quaerat ratione ea modi. Exercitationem sit vel iusto vel. Tenetur ad iste doloremque qui velit laudantium. Odio voluptates autem tempora sit consequatur enim. Velit quia doloremque ut aut laboriosam distinctio. Explicabo ratione rerum nostrum et quo in qui labore.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...