top of page

How to value a great stock in minutes



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.


Stock valuation tool to be used in investment research process.
Assumption list of stock valuation tool.


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.


Stock valuation tool to be used in investment research process.
Setup of timeline for 10-year free cash flow projections.

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).


Stock valuation tool to be used in investment research process.
Free cash flow projection year 1.

Stock valuation tool to be used in investment research process.
Free cash flow projection years 2-10.

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).


Stock valuation tool to be used in investment research process.
Discount factor calculation year 1.

Stock valuation tool to be used in investment research process.
Discount factor calculation years 2-10.

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.


Stock valuation tool to be used in investment research process.
Calculation of discounted free cash flow values years 1-10.

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.


Stock valuation tool to be used in investment research process.
Calculation of the free cash flow earning power per share.

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.


Stock valuation tool to be used in investment research process.
Calculation of cash & short-term investments per share.

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).


Stock valuation tool to be used in investment research process.
Calculation of the total debt per share.

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.


Stock valuation tool to be used in investment research process.
Calculation of the value of the business/stock 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.


Google stock valuation based on Excel valuation tool and assumptions.
Google stock valuation

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!


TheFinancialDutchman - Stock Valuation Tool
.xlsx
Download XLSX • 12KB

 

Thank you for reading!


The Financial Dutchman


***

Please see our disclaimers.

Comments


bottom of page