Calculating IRR of cash flows with terminal value
I have a project with an initial outlay (Year 0) and 10 years of forecasted positive FCFFs and FCFEs. After 10 years, I use a Gordon/Perpetuity Growth Model to determine Terminal Value.
I built DCF model and discounted FCFFs with WACC (to arrive at Enterprise Value) and FCFEs with Cost of Equity (to arrive at equity value).
While calculating the terminal value for both cases (FCFF and FCFE), I use perpetuity growth formula:
CF10 * (1+g) / r-g where:
CF10 = FCFF/FCFE of the Year 10;
g = growth rate;
r = discount rate (WACC for FCFF, Cost of Equity for FCFE).
At the moment, I am trying to determine IRRs (Project IRR from Enterprise Value and Equity IRR from Equity Value). However, I have the following Issue:
- While calculating IRRs with excel IRR formula, I supposedly do not get the correct rate, as far as I use WACC/Cost of Equity ("r" in the above given formula) to calculate Terminal Value. I get two possible IRRs:
1) IRR calculated using Goal Seek;
2) IRR calculated using excel built-in IRR formula (apparently, rate is higher in the 2nd case).
When I check results by setting WACC/Coe to IRR Values, Goal-Seek calculated IRRs return Zero NPV. However, I am not sure this is a correct method to use. Moreover, I need my model to be dynamic (therefore, I can not use Goal Seek).
However, if you use Fixed WACC/CoE in Terminal Value calculations, excel built-in formula calculated IRRs return Zero NPV as well. I am not sure about this method as well and as I mentioned above, my model has to be dynamic.
In order to better understand the issue, please find attached the sample xls file with relevant calculations.
Any suggestion would be greatly appreciated.
Attachment | Size |
---|---|
example.xls 49 KB | 49 KB |
Any Suggestions?
I don't know how the hell to use your model but WACC should change as the capital structure changes (not necessarily fixed at X.X%). I'm guessing your capital structure changes somewhere and that's probably your problem. Just use your target IRR as your valuation metric (go or no) on the project easy enough.
Voluptatem earum voluptas ab. Laboriosam repellat ut aliquam sapiente itaque quia qui. In deserunt incidunt est sint praesentium ut. Eum eos aut aliquam rem illo necessitatibus. Eos dolorem labore harum quibusdam in pariatur.
Expedita saepe sequi nemo iusto labore. Aspernatur quis nam quis illum animi. Nobis voluptas numquam consequatur sunt cumque et. Et fuga optio itaque ad aut. Libero autem molestias a illo et nam.
Facere ut incidunt eveniet maiores quod non. Dolor rerum commodi autem nulla laborum. Dolor enim inventore placeat omnis. Hic recusandae consequatur ratione et officiis tempora. Veritatis nam exercitationem aperiam maiores. Eos in quibusdam praesentium eum aut et.
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...