Create Discount Cash Flow Model Using Excel

Objective

To help new investors create a DCF model on excel for valuation of individual stock.

Scope

• To understand how to create the DCF model on an Microsoft excel sheet
• Where to look for the inputs.
• Understand risks & rewards
• Probalility Study

• Comparison of intrinsic values

Introduction

DCF model is NOT a precise method of valuing a stock, but don't worry, there isn't any that is precise. But it is one of the best tools to that allow you to compare investment opportunity by comparing their intrinsic value. I will use $MSFT as an example

Basically you need 4 input for the DCF model

• Free Cash Flow per share
• FCF growth rate
• Discount rate

• Terminal multiple

Step 1: Find FCF per share

You may use any data provider but i prefer yahoo finance. Under Cash Flow, you can find free cash flow and under Balance Sheet you can find number of shares.

STEP 2: FCF growth rate

To be conservative, growth rate= 18%

STEP 3 Discount Rate

There are endless discussion about discount rate, some uses interest rate of 10 years US treasury (risk-free) and some say WACC (weighted average capital cost). To keep thing simple, always use the same discount rate, so you will not constantly change your intrinsic value due to the fluctuation of the interest rate. I would set at 10% long term average returns of the S&P500.

STEP 4 Terminal Multiple

When analysing a stable company the free cash flow are close to its earning. Net income (earnings) is an accounting term and can be easily manupulated, for Free cash flow is much difficult. For $MSFT the earning is $61B and the FCF is $56B. Using FCF, is more conservative. Given stock price = P/E * Earnings. Thus the Terminal multiple is your Estimated P/E ratio at the end of the investing period.

Intrinsic Value Table on Excel

First Row: FCF at 0 period = $7.46 and projected 18% growth from 1 to 5 years. Because a company is not probable be continue to grow at the same rate, a growth rate of 12% is estimated from 6 to 10 years. The terminal multiple, (estimate 30) is multiply by the FCF at 9th year. This bring the value to the beginning of the 10th year.

Second Row: Apply the present value formula from year 1 to 10. Terminal value = terminal multiple X PV9. Summing all the PV = intrinsic Value: $413

Where:

PV = Present Value

n = 1, 2, 3, 4.... 10

g = growth (18% 1 to 5 years, 12%, 6 - 10 years, for MSFT)

r = Discounted rate or expected returns (10%)

M = Terminal multiple (Estimate 30)

Risks & Rewards

As mentioned there is no precise method in the world to value a company. In addition, market condition are always dynamic, it is difficult to foresee how it could affect a company. Thus is important to know the Best Case, Normal Case and Worst Case. So you will a ranges rather than a single number, so you are mentally prepared.

Probability Study

You may want take a step further, to estimate how probable will the cases to turn out. Usually i would put 10% , 30% and 60% for the best, normal and worst case respectively, to be conservative. Summing up the 3 value, i would get a weighted intrinsic value.

Comparison of Intrinsic Values

Usually in your portfolio there are other stocks, when DCA we can make a decision based on which stocks provide the most value during the month by comparing the intrinsic value to the stock price. Ratio = valuation/stock price.

Example:

if i buy MSFT at $289. My expected returns is 11% p.a. (worst case 10% p.a., Best case 14% p.a.) over 10 years

However, if i buy GOOGL at $2731, my expected returns is 13.5% p.a (worst case 9.7% p.a , best case 29.5% p.a)

Thus Googl provide a better value if i buy at this current price.

Conclusions

The intrinsic value for everyone may be different as it is based on individual assumptions. When making such assumptions it is always better to be conservative. The intrinsic value is also largely affected by your discount rate or your expected returns. To be more precise, the DCF model does not really calculate the value of the company, but rather means if your expected returns is 10% p.a. you must buy at $319 (For our MSFT example).