Let’s talk about valuing a publicly listed stock today. This is one of the most important concepts you must master to succeed as an investor. A rough valuation will help you stay grounded in a world of ever-moving stock prices.
Let's assume you have identified a fundamentally strong company as outlined in the investment research process. The only step left now is to find out what the stock is roughly worth. We already know that the true value of a company is derived from discounting its future earnings power back to today. I typically use free cash flow as a proxy for a company's earning power (you can also use other proxies at your own discretion).
I will keep it simple today and won't waste your time showing theoretical formulae and overly complex models. Instead, we will build our own valuation tool in Excel in minutes! The valuation tool will be based on a discounted cash flow model. I will add a few twists to account for cash & short-term investments and debt.
Let’s get building!
Building a stock valuation tool in Excel
Now let’s build a simple valuation model in Excel based on a 10-year free cash flow projection (you can adjust this to a timeframe you would like).
To build the valuation from scratch you will have to take the following steps:
Step 1 – Add your input and assumptions Create an input list on the top left-hand side (I used Columns A and B until row 9). In the below example, you'll see this input column as well as some sample values I've added for a valuation of Google for illustration purposes. We will come back to these later.
Step 2 – Create a timeline Create columns for your 10-year free cash flow projections (I used cells D2 to N2), which will make up your timeline.
Step 3 – Create a cashflow projection Create a projection based on the assumed growth rate and drag this formula to year 10. I used cells E3 to N3 in Excel. Note that the formula in cell E3 and cells F3 to N3 are slightly different, because of the way I have designed this model. The formula in cell E3 simply multiplies the free cash flow number listed in your assumptions with your own assumed growth rate (see the formula in cell E3). For each following year, you multiply the previous year's free cash flow projection with the growth rate (see the formula in cell F3 and then drag this formula all the way to cell N3).
Step 4 – Calculate the discount factor Now let's calculate the discount factor for each projected year in the future. A discount factor is a way to convert cash flow received in a future period to the present. The discount factor is calculated as 1 divided by 1 plus the discount rate to the power of the number of years you are projecting forward (or 1 / (1 + discount rate)^n ). Note that the formulas in cells E4 and F4 are different because of the way I have designed this model as mentioned earlier. The formula in cell E4 is a simple division of 1 by 1 plus the discount rate listed in your assumptions (see the formula in cell E4). For each following year, you divide the previous year's discount factor by the discount rate (see the formula in cell F4 and then drag this formula all the way to cell N4).
Step 5 – Retrieve the discounted value Multiply the projected free cash flow with the discount factor, to get the discounted value of the free cash flow in cell E5. Next, drag the formula from E5 to N5.
Step 6 – Calculate the value of the free cash flow earning power per share
Divide the sum of the discounted values of the projected free cash flows 10 years out (cells E5 to N5) by the number of outstanding shares in your input & assumptions list.
Step 7 – Calculate the cash & short-term investment per share
Divide the cash & short-term investments in the latest quarter by the number of outstanding shares in your input & assumptions list.
Step 8 – Calculate the total debt per share
Divide the total debt in the latest quarter by the number of outstanding shares in your input & assumptions list (make sure you put a minus before the division).
Step 9 – Calculate the value of the business per share
Sum the value of the free cash flow presently per share, the cash & short-term investments per share and the total debt per share.
Done! You can now compare the valuation with the current stock price. However, keep in mind that the valuation depends on a handful of assumptions and by changing inputs even slightly, the value will change.
Now that you've learned the basics of building a stock valuation tool, you're ready to start evaluating!
Example of a stock valuation
Let’s put your new tool to the test with a real-life example: Google. To get the assumptions, I'd recommend using sources such as Yahoo Finance or Tikr. The number of outstanding shares, the total debt and the cash & short-term investment number can all be found on the balance sheet and income statement. For each of these values, I refer to the ones from the latest quarter. The free cash flow number is based on the last twelve months (LTM) number and is equivalent to the cash flow from operations minus the capital expenditures.
The valuation above assumes a growth rate of 15% per year over 10 years and a required return for the investor of 10% (this is the discount rate).
I hope today’s blog post has enabled you to get comfortable with calculating your estimates of what companies are worth.
For reference and in case you want to make absolutely sure you've set up your valuation tool correctly, I've created an out-of-the-box valuation tool for you to download. Enjoy!
Thank you for reading!
The Financial Dutchman
***
Please see our disclaimers.
Comments