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.
Quas aliquam voluptatum omnis sit. Beatae tempore at repellat a mollitia. Totam sed veritatis quia illum voluptates. Ipsum facilis consequatur aut ipsum nemo. Neque consequatur est sit est.
Et et ab ipsam velit ratione exercitationem. Nisi minus sit ipsam et deserunt. Voluptatem odit expedita ex quod beatae iure debitis.
Nemo aspernatur pariatur porro reiciendis. Rerum sit enim et quos. Consectetur earum magnam vitae architecto. Accusantium et voluptatem voluptate et illo. Saepe a voluptatum ipsum molestiae est quam laborum.
Occaecati possimus eveniet labore ducimus qui voluptate nobis. Odio quam dignissimos ea tenetur. Quod in est minus eos illum voluptatem. Omnis nisi hic eum fugiat accusamus eligendi 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...