Skip to main content
No. of Recommendations: 1
Hi Dave,

Sorry for the delay in getting back to you. Earnings season is a busy time and we've got 22 companies (at least) in this week's SA update.

Here's the spreadsheet I used to get the 0.6% / 0.3% / 0% growth at 15% discount and $62.90 price for Transocean:

Transocean Ltd. RIG
Latest stock price $62.90 11/1/2010

Predicted price $62.90 1 2 3 4 5 6 7 8 9 10 Term
Earnings $2,963 $2,980 $2,997 $3,014 $3,031 $3,040 $3,048 $3,057 $3,066 $3,075 $20,497
Discount rate 15.0% Discounted $2,576 $2,253 $1,970 $1,723 $1,507 $1,314 $1,146 $999 $872 $760 $5,067
TTM Earnings $2,946
Growth 1-5 0.6%
Growth 5-10 0.3%
Growth term 0.0%
# shares 321.0

The TTM earnings is free cash flow for the year ended 6/30/10, the latest available at the time. CFFO - capex = 5022 - 2076 = 2946.

Earnings in year 1 is 2946 * (1 + 0.6%) = 2963. Year 2 is Year 1 * (1 + 0.6%), and so on, through Year 5. Then Years 6 - 10 uses 0.3% instead.

Terminal = (Year 10 * (1 + Terminal rate)) / (Discount rate - terminal rate)

The discounted numbers are the earnings number discounted back to the present, and are in the form: (Earnings Yr N) / (1 + Discount rate)^N

Then, add up all the discounted values (all 11) and divide by the # of shares to get the predicted price.

The inputs are the last stock price (the target for Excel Solver), the number of shares (on the end of the TTM period), the TTM earnings, the Discount Rate, and the Terminal growth rate. Growth Rate Yrs 5-10 is half of Growth Rate yrs 1-5 by definition. I then run Excel Solver to vary Growth Rate Yrs 1-5 so that the predicted price matches the latest stock price.

Hmm, just discovered a minor mistake. The number of shares should have been 318.9 M instead of 321.0 M. The latter was actually on 3/31/10, not 6/30/10. That changes the growth rates to 0.4% / 0.2% / 0.0%.

A very simple DCF model and, with Excel Solver installed (check the installed plug-ins to make sure or to add it), very easy to use.

Hopefully with that, you can see where your model and the above differ.

Cheers,
Jim
Print the post  

Announcements

When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Contact Us
Contact Customer Service and other Fool departments here.
What was Your Dumbest Investment?
Share it with us -- and learn from others' stories of flubs.
Work for Fools?
Winner of the Washingtonian great places to work, and Glassdoor #1 Company to Work For 2015! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.