Message Font: Serif | Sans-Serif

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

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.