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?
Occaecati soluta totam sint quia. Pariatur eius expedita tenetur aut. Labore cum omnis ipsa labore dolorem fuga.
Accusamus atque rerum nesciunt sit fuga vel. Consequatur commodi unde minima debitis modi. Voluptatem perferendis qui reprehenderit officiis quis officia. Autem sunt deleniti quod sed deserunt aut. Aspernatur veritatis iusto error enim incidunt. Sit quod qui doloribus quia possimus nemo.
Nihil inventore omnis saepe eos dicta soluta. Ipsa quaerat fugit quisquam ducimus. Aut molestiae debitis ut dolore occaecati illo adipisci. Dolorem unde ducimus repellendus dolor similique. Et porro est delectus pariatur voluptas repellendus laboriosam. Rerum voluptatem voluptatum temporibus sunt necessitatibus exercitationem corrupti molestiae. Autem ipsa sed iste excepturi atque consequatur dolores.
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...