IRR formula in xls vs. DCF calculated IRR.

JenniferReed's picture
Rank: Chimp | 1

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?