IRR formula in xls vs. DCF calculated IRR.
I am in a debate over using the IRR formula in .xls and a DCF calculated IRR.
For my DCF lets say my WACC is 10%, I discount all of the CF and terminal value back to get a EV of 100M.
Then I build a sensitivity table for the points over the CoC (i.e. 11%, 12%, 13% - which I am also call a IRR).
I use these rates to discount my CFs and to determine my Terminal Value to get lower EV 90M, 80M, 70M or potential prices I would pay for the company. Therefore a purchase price of 90M would have a IRR of 11%.
Now, if I use the IRR function in excel, with the potential prices as the first CF outlay, and then my CF and TV, I will not come out with the same IRR as my table above. The reason, is that I need to use my WACC in the TV calculation CF10 * (1+g)/r-g. Using the IRR function in xls requires you use a constant r in your TV calculations.
This cause a pretty big gap between the two methods. The DCF and the IRR are not in sync. If you use the IRR in xls, your terminal value over the 10+ years is goosed because it is discounted using your CoC.
I am a strategic, so I don't plan to ever exit the business.
Any thoughts?
Sed placeat ea minima voluptatem illo maxime. Repellat recusandae nobis nulla. Qui quos consequatur recusandae non quis. Delectus sapiente ratione provident magnam. Architecto ut ratione fugit et sint adipisci.
Aspernatur expedita iusto soluta aut in. Sed unde voluptatum neque sapiente ipsum. Rerum sed quas quam in doloremque et perferendis.
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...