I am in a debate overformula in .xls and a calculated IRR.
For my DCF lets say my
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.