Tim wanted a little clarification on how I came up with a value for JNJ’s future cash of $201.7 billion using Excel® (See the full valuation here). The following is a semi-intermediate discussion for Excel® users.
All numbers in $ Millions unless otherwise specified
Enter the Free Cash Flow (FCF) for JNJ from 1997 through 2006 into Excel®. Then, have Excel® calculate the growth rate for various timeframes (I use 5- and 7-year periods to get medium and longer-term performance):
- 1997-2004
- 1998-2005
- 1999-2006
- 1997-2002
- 1998-2003
- 1999-2004
- 2000-2005
- 2001-2006
Doing that, you’ll be able to see how JNJ performed in various economic conditions and situations for an entire decade. Then, you simply get the median (=MEDIAN(values)) of those values-the median will give you the typical growth rate taking into account any overly high or low values.
In JNJ’s case, the median growth rate is 16.1%-and that is the basis for the next step.
Moving On
Based on the past ten years of steady, consistent growth, we can reasonably expect JNJ to grow FCF at 16.1% for the next ten. After that, to be conservative, we project that JNJ will grow FCF at 5% from years 11-20.
Tell Excel® to grow the FCF at 16.1%, using $11,582 in 2006 as your starting value. 2007 FCF would be $13,444; 2008 would be $15,605-and so on through 2016 (ten years). Then, tell Excel® to grow it at 5% from 2017-2026. You should now have a list of the projected FCF for the next twenty years.
Almost there!
(Once you set up your spreadsheet, this will all happen automatically.)
You can’t just buy that future cash at face value-you’d end up with a 0% return. You have to buy that cash at a discount. For example: we estimate that JNJ’s FCF in 2015 will be $44,304; so, we have to figure out what price to pay today to earn 15% a year on our money and end up with $44,304 in 2015. In Excel®, there are two ways to do this:
- Use the Present Value formula for each year: =PV (15%, 2015-2006, 0, -$44,304). Then, add up all twenty years of data;
- Use the Net Present Value formula for the entire series of cash flow data-from 2007-2026-and let Excel® do all of the leg work: =NPV (15%, [FCF from 2007-2026]).
The Result
However you choose to do it, you end up with $201,680-the price you could pay today to get 15% on your money assuming the future cash is as we project it. Of course, you aren’t just buying future cash-the company has a net worth of $39,318 in 2006 and you are buying your share of that as well. $201,680 + $39,318 = $240,998. That figure is in millions-translate it to billions and you end up with $241 billion.
What if the 2007 free cash flow isn’t exactly $13,444? No problem-we have to look at businesses over various timeframes and not sweat over the actual year-to-year performance. Think about it: Does it make sense to analyze ten years’ worth of data in 5- and 7-year blocks, project twenty years into the future, and then worry about the daily stock price or one-year performance?
Relax. You’re not gambling in stocks anymore-you are buying businesses. It is a whole different world.
Filed under: Companies Analyzed (Generals)
Related Stocks: JNJ
Dave,
If you assume that Berkshire can keep growing its Free Cash Flow above 20%, then yes, Berkshire is trading at a discount to its intrinsic value.
Here, you run into a question of management. So long as you have the world’s greatest investor at the helm, the future looks bright. Once management changes hands, growth may dry up.
This is definitely worth a post in and of itself. I’ll put it together this week and email you when it is online.
Joe Ponzio
( REPLY )
When you discount cash, you need to find the “net present value” of that cash today. That net present value is the price you can pay today to end up with a dollar amount tomorrow.
Think of it this way: If I offered you $100 next year if you gave me a sum of money today, how much would you be willing to pay for that $100? If you wanted a 10% return on your investment, you could not give me more than $90.91. Growing $90.91 to $100 in one year gives you a 10% return.
You can see the actual formula here on Wikipedia. Look at the first example to see how to discount each year (or term) back to today’s value.
( REPLY )
I use 5% because a) the future is very difficult to predict, and b) it is a conservative growth rate above which my business and I will make lots of money.
If I use, say, 10% for years 11-20, I’ll end up with an extremely high value for my business. It is impractical to think that a business can sustain high rates of growth for very long. If I buy on the assumption that it’ll grow at 5%, and then it grows at 7%, I make more money.
That leads into the second part: 5% is a conservative growth rate for large companies. By increasing prices with (or slightly ahead of) inflation, cutting expenses, and through other “sustaining” tactics, a business should be able to grow its cash at 5% over the long term. It won’t be rapid growth; still, it is fairly easy to obtain.
( REPLY )
Hi Joe,
I’m pretty new at all this and find your ideas clearly expressed, logical, and more or less in line with my perspective on things. Thanks for taking the time to write and respond to us.
Concerning DCF and margin of safety in valuation: if I think the company is going to grow at 10% for 10 yrs and 5% for 10 yrs after that, would I be adding in my margin of safety by using a discount of say 8% for 10, then 3% for 10? Or, what if I use 10% for 5, 5% for 5, and 0 thereafter? I guess what I’m trying to figure out is this: what are the dangers and problems of being overly conservative (especially after yr 10, since that is pure speculation) with your discount numbers?
Appreciate you insight. Thanks.
( REPLY )
Hi Joe,
First of all, thank you for posting such rational and informative thoughts and information on your blog. It’s in the top 3% of finance blogs I’ve read so far. I understand your valuation approach and have started using it myself.
I’m confused on two points in the calculation of the future value of cash flows.
1. You start the 20 year cash flow projections from the last year’s cash flow. For example, in 2006, JNJ had $11,582 (mil) of FCF. Thus, that’s the starting point for years 2007 and beyond. That’s fine if the FCF in the last fiscal year was representative of a company smoothly growing FCF over time (like JNJ) — it’s predictable. What about for companies that have more irregular or cyclical cash flow? Do you use an average of the last X years (3, 5, 7, or whatever seems representative) as a starting point?
2. I also see you use the median, multiyear FCF growth (16.1 for JNJ) in projecting future FCF for the near future (3-10 years or so). I’m wondering if you ever knock a few points off that number for some extra margin of safety? For example, you might look at JNJ and say, yes, they’re growing FCF at a median of 16.1% over multiple rolling periods, but what if they can’t do that for 10 more years. Maybe it will be a tougher business climate or their drug pipeline will stagnate for a while? Do you ever do this? I’m doing this b/c I feel it gives me some more safety in the fact that I don’t know all the details of any given business since I a) am not an insider, and b) don’t have a crystal ball.
Thanks for any help. Please keep up the informative blogging!
DRP
( REPLY )
Hi DRP,
1) The idea behind valuing a business is comfort. Consistent businesses lend themselves to that. Cyclical or inconsistent ones do not. Still, it is okay to value and buy those businesses if you are confident in your reasoning and data. I stay away from cyclical businesses because I never know how wide or devastating the next cycle will be.
As far as other companies – companies that have been inconsistent but may become consistent – it comes down to educated guessing (as does everything else). If you think that the multi-year average can give you a basis for future value, then go ahead with it.
Remember: There are enough consistent, simple businesses out there that you don’t have to take big chances or risk. Take a look at this post – Does Discounted Cash Flow Always Work? – to see how big gains can be made in simple, slow growing companies.
2) I am not fixated on the past. A perfect example: the American Eagle Outfitters analysis. AEO’s growth was through the roof for the past 10 years, but I chose to use half of it’s historical 35.4% growth rate to predict the future. Why? I wasn’t comfortable saying AEO would continue to double in size every three years for ten more years. There is little or no moat in AEO and a ton of competition.
To use the past rate, you should have an extremely durable, wide, deep moat and a business that will be needed in ten or twenty years. People will need Johnson & Johnson’s products and services; a VHS-only movie store, no matter how great, won’t be around in ten years (if any still exist).
Hope that helps!
( REPLY )
I have absolutely no idea how to use excel. what exactly does =NPV (15%, [FCF from 2007-2026]) mean? could you write this whole process out long hand as if i did not go to harvard school of finance? you know show me what each year looks like when discount going backwards of 20 years?when I wrote it out long hand I wrote the sum of the total of future earnings from 2007-2026=966. so i wrote npv(15%[966.9]=npv14,1/145=npv=.0068. so how do you get 201 as your npv or pv?
( REPLY )
Hi Joe,
your blog is a great source of value-investing information, I love it!
I do have one question about the Margin of Safety. In your JNJ excel sheet you use a 15% discount rate and then you want a 25% discount to the calculated value before you buy in. That 25% is your Margin of Safety.
You calculate the 25% Margin of Safety with the formula:
(1 – 0,25) x per share value
In my opinion that’s way more than a 25% discount. I measure the discount this way:
(intrinsic value – the value the company is trading for today) / the value the company is trading for today
So let’s say our estimated intrinsic company value is 100 million, it is currently trading for 75 million. Now if we use your 25% discount calculation:
(1 – 0,25) x 100 million = 75 million
We see that the company is trading at exactly 25% discount.
But if we use my calculation we get:
(100 – 75) / 75 = 33% discount.
Ofcourse the value of the company stays exactly the same and the discount calculation doesn’t matters that much. Still I think my calculation is more fair, because if the company would rise from it’s current value (75 million) to it’s intrinsic value (100 million) we would have a 33% gain, not a 25% gain.
( sorry for the long post, it got a bit out of hand 
)
( REPLY )
All you’re doing is lowering the amount of leeway you look for calculating it like that.
Really, we’re getting a margin of safety/room for error for our calculations.. not for our potential gains.
I don’t see the point in overcomplicating it and lowering your room for error.
( REPLY )
@ Night:
I don’t see why you say I’m overcomplicating things and am lowering my room for error. The room for error stays exactly the same and the calculation of the discount uses the same primary school math as Joe’s method, so no overcomplicating whatsoever!
And I’m not lowering the amount of leeway at all, because I know that 25% discount in Joe’s way is more than 25% in my calculation!
The only point I was trying to make was that with the method I use, you get a more ‘true’ insight on how much your stock is underpriced. At one glance you can see the potential return the % the stock is underpriced.
My long post may have made it look more complicated than it was meant to be.
( REPLY )
hi,nice blog however being a newcomer could u explain a few doubts i have
1. Does FCF= Cash Flow From OP minus cash flow used in investing activity
2. the spread sheet formulas are too difficult (eg.npv(15%,B23:K23,B25:K25) K9
to understand as in if you could use simple terms such as , – , / , * it would be simpler for me to calculate
If you could atleast explain the Total value(B28) formula in easy terms ( ,-,/,*) it would be of great help as the other formulas i have somehow figured out
thanks in advance
Jazzy
( REPLY )
I just wanted to add to Nick’s contribution:
In essence, if your owner earnings projections were on spot, you would enjoy a 33% bonus in your overall return on investment over the 20 year projection period.
Theoretically, if you sold in 20 years at Fair Value, your return should be 16.7% annually.
Correct me if I’m wrong
Amit
( REPLY )
Hi Joe
This is a great idea.
I’m going to work through your valuation and get back to you.
Cheers
J
( REPLY )