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.
Rem quis assumenda quidem aut. Consequatur qui deleniti molestiae culpa. Consequatur porro aut perspiciatis ab amet. Et qui consequatur maiores qui id aut.
Et quia esse magni explicabo corporis omnis. Sit quia nihil et dolorum expedita. Consequuntur quas sit quia et itaque aut velit. Eos ipsa sit nobis maiores accusantium tenetur.
Delectus et esse ad. Aut molestiae eum sapiente ipsum placeat cumque est rerum. Laborum ut vel vitae non eius. Mollitia sed deserunt similique. Est voluptate alias ipsam eligendi at sapiente.
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...