Return on Equity / IRR

J

jhughes825

New Member
Joined
Apr 3, 2009
Messages
3
I am trying to do a return on equity analysis - could I get some help from someone please? I am a new analyst at a small boutique.

The initial investment is $6,000,000.
The free cash flows are as follows:
2016 = 600,000
2017 = 600,000
2018 = 600,000
2019 = 600,000
2020 = 600,000
2021 = 600,000
2022 = 600,000
2023 = 52,315,912
2024 = 64,588,052

The discount rate is 10%

The terminal value assigned to this project is 6x free cash flow.

The reason why the final two years of the projection period are so much higher is that in 2023, the debt is paid off and all free cash can flow down for equity. (ie no debt service burden)

I know I should do an IRR calculation with -6,000,000 as initial cash flow, but confused about how to integrate the terminal value (6x FCF) and the discount rate of 10%.

Could someone give me some guidance asap in doing this calculation in excel? Thank you so much.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
I'm having a hard time believe that free cash flow changes from six hundred thousand dollars to 52 million dollars, once you've paid off the initial six million dollar investment. Is that really the case?

Also, some cautionary remarks:
http://www.exceluser.com/solutions/irr.htm

I'm a bit reluctant to give answers here given the size of your investment - there's a lot to consider besides what an IRR formula will tell you, and you probably would want to consult with someone with a good knowledge of your business, your market, and financial/strategic planning overall.

Regards,
Alex.
 
Upvote 0
Thank you for your feedback. I will contact my principal with your concerns. However, could you help me with the calculations? How do you include a discount rate and terminal value with the calculation of IRR?

Thanks
 
Upvote 0
This rough (!) explanation tells me I'm out of my league:
http://www.peoi.org/Courses/finanal/ch/ch10e5.html

But I also found some pretty good discussion here:
http://www.finance30.com/forum/topi...87892:Comment:362899&x=1#1987892Comment362899

In short, it seems maybe if you want to include terminal value in your IRR you have to determine the final value of the investment at the end of all the periods - i.e., as if you "sell" it - and include that amount as the final cash input.

Hope that helps - I'm really not too familiar with IRR but I'm willing to give it a go. I think your problem seems strangely lopsided. My IRR (ignoring terminal value) was 125% for your numbers - which is very high. Looking at the 52 million and 64 million free cash flows in the final two years - one thinks that there is more here than meets the eye.

Alex.
 
Upvote 0
Hello,

jhughes825 said:
How do you include a discount rate and terminal value with the calculation of IRR?
Click to expand...
You don't include a discount rate in the IRR calculation. The IRR effectively serves as it's own discount rate, it's independent of your discount rate, as noted here:

http://en.wikipedia.org/wiki/Internal_rate_of_return

Put another way, the internal rate of return for an investment is the discount rate that makes the net present value of the investment's income stream total to zero.
Click to expand...
The IRR is the rate that brings the project to an NPV of $0. So if your discount rate is 10%, and the IRR is 8%, you can be assured the NPV will be negative (fail), if the IRR is 12%, the NPV will be positive (pass).

How is that useful? Well, if you have several positive-NPV projects to chose from, you have a comparative measure.

Set up your investment at time-0, tack-on your terminal value at time-9, and calculate it.

However, your numbers don't make much sense to me - who's trading $6,000,000 for $100,000,000 plus a terminal value? I'd like to discuss some business ideas with this person. Your IRR doesn't need to be calculated, it equals infinity.
 
Last edited:
Upvote 0
hello Alex, speaking of terminal value, the difference in NPV when 'using' Vs 'not using' terminal value is significant. i've seen calculations where TV was used and others where it wasn't. so the question is should TV be included? any general rules here? best practices? also from your personal experiences, do investors usually include it in their IRRs?

thank you
Ahmed
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
XIRR but values are from two different cell ranges
Replies
2
Views
937
EthanP
E
E
  • Question
Having an array based on number of months
Replies
7
Views
393
steve the fish
steve the fish
P
  • Solved
Computing investment return formula
Replies
8
Views
428
polarwoc
P
F
  • Question
Dynamic XIRR Forumla Help
Replies
1
Views
801
frigo191
F
J
  • Question
Would someone please have a look at my simple npr / irr calc and let me know if it seems correct?
Replies
4
Views
635
jc65046
J

Forum statistics

Threads
1,214,661
Messages
6,120,793
Members
448,994
Latest member
rohitsomani

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top