One of the most popular aspects of this site has been the investment spreadsheet both the free version and the very affordable premium version.
Over the past couple of years as I continually added, fixed and built features and functions into the spreadsheet, what started out as a simple F Wall Street template became a full blown project yet the usability remains very simple and essentially the same. Only 1 input is required to get a fair value calculation.
But to improve the accuracy, a little fiddling is required. It doesn’t take long, nor is it difficult, so I’ll provide a simple guide of how I tweak the numbers to get the numbers as I do when performing valuation calculations such as the Fortune and Forbes list I cover.
Investment Spreadsheet Tutorial Overview
I’ll be using the 5yr version of the valuation spreadsheet to go through each of the examples below.
I’ll be looking at the following scenarios:
- stable cash generators – JNJ
- a high growth company – AAPL
- companies in a down cycle – AEO
Note: I’ll be assuming you know the basics of using the spreadsheet so I wont be going into the basic terms and usage. There may also be some minor tweaks in the spreadsheet that is not yet available.
Stable Cash Generator: Johnson & Johnson (JNJ)
Discounted Cash Flow Spreadsheet
- Step 1: Enter the ticker
- Step 2: Choose a discount rate for the investment
Remember that the discount rate is the rate of return that you expect to receive and are willing to pay now. The higher the discount rate, the more emphasis you are putting on today’s dollar. Whereas a 9% rate I use for JNJ signifies that the future cash flow for JNJ is just as good as today’s cash.
For large, stable, proven companies that generates cash, a rate of 9% is perfectly fine.
- Step 3: Choose a growth rate
With stable companies, the automatically calculated growth rate is going to be the rate of FCF growth over a multiple timeframe period in order to smooth things out. This way it will ignore the single good or bad years.
In JNJ’s case, the calculated growth rate is 9.9%. Compare this number with the PE of the company.
I read somewhere, maybe it was Peter Lynch but I can’t remember, that the PE is going to be the market consensus of the company growth. Whether you agree or not, this quick method has actually proved to be quite accurate.
JNJ’s PE is currently at 12. Considering that JNJ usually trades at a premium to its peers, the industry PE of 10 is bang on target with the auto growth rate of 9.9% you see above.
If you think otherwise, enter the desired growth rate into the user defined box labeled “User Growth” and update the spreadsheet.
- Step 4: Margin of safety
The lower the discount rate, the bigger the margin of safety required because you are placing more emphasis on the future cash. Anything can happen in 1 year let alone 10, so for a 9% discount rate, I always make sure the margin of safety is at least 50%.
- Step 5: Check with the historical price and value graph
As you can see in the graph above, the red line represents the intrinsic value over the past 5 years and JNJ’s historical stock price displayed by the blue line. From the graph the discount rate of 9% and growth rate of 9.9% produces an intrinsic value graph close in step with the historical stock price.
I can now assume that the rates I have used for JNJ is fairly accurate. The market isn’t always efficient but price does follow value over the long term which we can use to our advantage.
These large cash cows are the easiest to value. Try it for yourself by using KO, PG, JNJ etc.
Ben Graham Formula Spreadsheet
Next we move to the fair value calculation using Ben Graham’s formula.
I’ve updated the Benjamin Graham Formula valuation so that the growth is no longer the same as the DCF growth rate. Previously whatever growth rate was used for the DCF was identically applied to Graham’s equation. Instead it now makes use of normalized earnings growth just as Graham intended.
The earnings growth is retrieved from the DCF tab also normalized over multiple timeframes.
JNJ’s earnings growth over 5 years normalized was 10.9% which looks pretty good.
However, be careful with the Graham growth as earnings projections into the future can cause big jumps even if your growth rate increases by 1%.
Growth Company Valuation: Apple (AAPL)
A complete turnaround company within the past 5 years and still going strong. Apple is going to be slight harder than JNJ. The difficulty with growth companies is that their growth curve is exponential and there is no real way to graph or value a curve like that.
Discounted Cash Flow Spreadsheet
Enter AAPL in the spreadsheet and I’m given a FCF growth rate of 98.2% and intrinsic value of $6980 for the shares. Unrealistic right?
The growth rate remains at 9% because although AAPL is a growth company, it’s proven again and again it can create cash quite easily. The price vs value graph also suggests something is wrong and Ben Graham must be smoking something to produce a figure $1505 as a fair value.
So what is a good growth rate? AAPL’s PE is 24 at the moment but I never use a growth rate above 15%. Simply because I know even AAPL won’t be able to maintain a growth of 24% for the next 5 years. It’s achievable for the next year but becomes more uncertain as we project to the future.
The analysts believe AAPL can achieve 18% each year for the next 5 years. I rarely agree with them so I’ll stick to a rate of 15%. It’s my rule of thumb which doesn’t mean you have to limit yourself to 15%.
This results in the following images.
We now get something that makes sense. The argument that remains is whether AAPL shares are worth between $126 and $166. Looking at the price and value graph it’s true that the stock of AAPL got ahead of itself during the peaks. Looks to be trading in the range of fair value to me. This also similar to what I got when I valued AAPL previously in Jan of 2008.
Company in a Downturn: American Eagle Outfitters (AEO)
For companies that were previously producing healthy positive free cash flow but now losing money, it’s important to normalize the free cash flow figure. If you use the latest years FCF number as the starting point of the DCF, it will product incorrect and overly negative numbers.
Looking at AEO, a retailer is going to be hit hard in any recession which the stock price already reflects.
Enter AEO into the investment spreadsheet and you get a normalized FCF growth rate of -2.9%. With unstability, I require a higher discount rate because I don’t want to rely on the future cash. A 15% discount rate works great for me and assuming that AEO will be in business for the next 5 years, I’ll assume a growth rate of 12% for both the DCF value and Graham value.
As you can see in the image, the intrinsic value of the company comes out to $8.27 but what we need to fix is the normalized FCF as the company is currently at its down cycle and it’s unlikely that the current numbers will remain the same when people start spending again.
So an easy way is to highlight the 10 years of Free Cash Flow data in excel and then enter the median value. In this case I get a value of $54.7m which I will enter into the FCF override box.
The fair value now comes out to $9.84. It’s not a big change from $8.27 but the difference is large enough to make a difference.
Do the same thing with the earnings for the Graham formula and voila, not much fiddling around but just enough tweaking to get some realistic figures.
Do it for Yourself
If you are new here, still using the free versions or on the fence, why not grab the spreadsheet now and start calculating the value of companies for yourself? In the month of July, I’m actually offering a money back guarantee if you are not pleased with what you get for your money.
A risk free $10 spreadsheet could make you $1000’s
July 22nd, 2009 at 6:30 am
Why do you adding last year ‘Tangible Equity’ to the DCF Equation (in ‘Company Valuation’, row ‘Total Value’ (cell B66)) ?
(As i understand, if you want to be more accurate you should assume that the company wind’s-up after the last year of the DCF model and hence to add the Present Value of that cell (?) npv=(discount rate, nper, 0, fv))
July 22nd, 2009 at 10:10 am
As you know, DCF is the sum of the future cash flows. It is not including any of the current assets into the equation. But what about all of its buildings (if it owns any) or desks, phones, computers, furniture etc etc. It’s all part of the company value.
When a company is liquidating, the current assets will be sold for cash which is why using tangible shareholders equity is a value that should be added and not plugged into the PV formula.
July 22nd, 2009 at 8:13 pm
I recently purchased the investment spreadsheet. Great work!!! – I’m in the process of reviewing some of the formulas, I don’t understand why we are subtracting the aggregate negative balance of deferred taxes and the ‘other’ amount from cash flow from operations. Essentially you are subtracting this amount twice. I believe what you’re trying to do is remove the affects of working capital changes and the effect of the non-cash impact of income taxes from cash flows from operations, which makes sense, however, your formula for free cash flow removes the positive impact of these amounts (correctly) but if these amounts are negative, they are subtracted from the total, which essentially subtracts it twice. Would it not be correct just to use Net income plus depreciation and amortization as ‘cash flows from operations’ from which you subtract capital expenditures to arrive at free cash flows?
July 23rd, 2009 at 12:45 am
@ Greg,
Thanks for the purchase.
Regarding your question about the FCF formula, I did think of this problem so let me explain how the excel formula actually works.
I overcame this issue by first adding the deferred taxes and other income. Either way, the addition of these numbers will be positive or negative.
I then use the excel IF statement which allows me to use conditions to base my calculation.
So if deferred taxes + other income = negative, then instead of subtracting, I used addition since +- = -.
If deferred taxes + other income = positive, then I just simple subtracted.
Basically the IF statement is:
IF (deferred taxes + other income > 0)
Then (subtract the number)
Otherwise (add the number)
Hope that makes sense.
July 23rd, 2009 at 6:16 pm
But because you are adding a negative number, the negative number reduces the GAAP ‘cash flow from operations’, which already includes the affects of these two amounts. I think the result you are seeking is to add the negative amount back to GAAP ‘cash flows from operations’. Correct me if I am wrong but I believe what you are attempting to do is remove the affects of period to period changes in working capital ‘financing’, i.e. the receipt of receivables and payments of payables (the ‘Other’ amount), along with ‘financing’ of income taxes (i.e changes in deferred taxes) from the true cash that was generated from operations during that particular period. I believe this is the correct approach, so, when you subtract the positive amount you are correct, but when you add a negative amount you are lowering the GAAP ‘cash flow from operations’ by this amount, which has already been reduced by this negative amount. You should be adding the negative amount back to the GAAP ‘cash flow from operations’.
As an example, Titatium Metals (TIE) for the year 2004 had a negative $19.5 million in GAAP ‘cash flows from operations’, and used $23.6 million in cap ex. The combination of Deferred taxes and ‘Other’ was negative at 90.8 million. The FCF computes to a negative $136.8 million, determined by taking GAAP cash flows from operations of a negative $19.5 million plus the negative item of $90.8 million less the capex amount. The problem is that the $19.5 already includes the $90.8. This amount should be added back to get a get a operating cash flow amount of $68.4, which will then result in a FCF of a positive $44.8 million.
July 23rd, 2009 at 10:10 pm
(Light bulb goes off) Ding Ding Ding!
You’re absolutely right. Sorry I misunderstood the first time.
The original equation I used for FCF was simply “cash from ops – capex” but then I wanted to adjust it to my version of owner earnings excluding the effects of taxes and other and it seems like I’ve been using the wrong number all along.
Thanks for clearing that up.
Will fix it right away.
Updates coming to everyone ASAP.
July 26th, 2009 at 5:18 am
I tested the example with AAPL and don’t get the crazy numbers.
10.7% is the calculated growth when I enter 9% Discount rate. Share value is $83.12
nevermind.. now I see that number.
July 29th, 2009 at 5:37 pm
Hi there, can this be used for multiply invest programs?
I want a system who go from beginning to end pr month.
It must be so you can insert each program, and the % you get paid and a total calculation of invest vs profit… something like that.
July 29th, 2009 at 6:16 pm
@ Kent,
Can you clarify what you mean? I dont really understand your needs.
What do you mean by using it for several investment programs?
What do you mean from beginning to end of month?
What do you mean by insert each program?