Posts Tagged ‘DCF’

Learn how to invest, read stock analysis, and find stock picks

Discounted Cash Flow & Stock Valuation

Jae Jun

The purpose of the Discounted Cash Flow (DCF) valuation is to find the sum of the future cash flow of the business and discount it back to a present value. I use the F Wall Street method of valuing a business along with some tweaks here and there to suit my tastes in the free and best valuation spreadsheets you can find on this site.

The advantage of this method is that it requires the investor to think about the stock as a business and analyze its cash flow rather than earnings.

The first and foremost reason a business exists is to make money where money = cash, not earnings. Since cash is what a business needs in order to maintain and grow its operations, it’s only right to consider the possibility of its future cash growth rather than earnings growth.

The disadvantage is that DCF is not suitable for start ups, growth companies or capital intensive companies where the cash flow cannot be accurately determined. The error of prediction and assumptions must also be dealt with in the DCF, which we cover with margin of safety.

I’ll go through the many assumptions to consider with a DCF. (If you are considering using the spreadsheets found on this site, please don’t just follow it blindly.)

Free Cash Flow

FCF = Cash from Operations – Capital Expenditure

The number we want to use is the cash generated from ongoing business operations. This is the cash that is recurring and will allow the business to grow. Cash from one time sales of property or a subsidiary should therefore be taken out as it is of low importance compared to the recurring cash.

With the DCF spreadsheet, a reader pointed out that the current FCF formula includes other non cash items and deferred taxes. Since we are looking at cash over different timeframes to normalize the data, I don’t believe it to be a cause of concern. However, as I kept thinking about this, excluding these items would provide a better indication of how the cash has been growing before these additional additions. This would not produce a more conservative number but a better indication of the actual FCF growth.

If we use FactSet Research Systems (FDS) as an example, the median FCF growth over 10 years is 29.8% with the above formula whereas the FCF value minus taxes and other produces a median FCF growth of 34.1%.

A discussion on capital expenditure is a post in itself so I’ll just state that to truly get a better accuracy in your DCF, the amount of maintenence capex and capex used for growth has to be distinguished.

Expected Growth

This is where we get to the artsy side of the DCF and where we have to come up with a number for the indefinite future.

I’ve previously written a qualitative post on growth rates but the growth number I generally use is the median FCF growth over 10 or 5 years depending on the company. I also compare it to the PE since that is what the market expects from the company. The exception is when the FCF growth rate or PE is ridiculously high, which is going to be unsustainable. My cap for the highest growth is limited to 15% to be conservative.

The goal of choosing a growth rate is to find a number which is conservative yet not low balling, and close to reality in order to capture potential future gains without eliminating too many investment candidates.

Discount Rate

Click for the full post on discount rates. As I mentioned in the linked post, I lean very strongly towards present dollars rather than future dollars. In other words, I use a high discount rate because I prefer the certainty of the present cash rather than the uncertainty of the future.

People in the finance world pour out their hearts to obtain the most accurate discount rate by analysing risk free rates, beta, risk premium and WACC. I say rubbish to all this. What’s the point in learning every method of hammering a nail when all you have to do is hit it on the head. Personally, I just believe that people over complicate this aspect.

The beauty of old school Graham and Buffett is that their investments are based on common sense, not volatility and other mumbo jumbo.

Terminal Value

Since it isn’t practical to forecast cash flows for an infinite number of years, it’s usual to end the DCF with a terminal value. On the spreadsheet, the terminal value is 3% (although the text says 5%).

The terminal value can also be found with the stable growth model(pdf), but once again, I personally don’t see the necessity of having to choose between my fixed 3% and 3.4859474% the formula may give.

Discounted Cash Flow

DCF receives a bad rep with the crowd and growth players because they call it driving with the rearview mirror. But in the private business world where estimates and PE’s are absolutely irrelvant, cash is what is used to judge the value of a business.

However, as investors, we all need to have plenty of tools and know which one to apply at the right time. I hope to write about the different valuation methods in the future.

For an idea of the accuracy of a DCF analysis, check out my intrinsic values vs Morningstar’s.

Free 5YR DCF Fair Value Investment Spreadsheet

Although finding great, stable companies with more than 10 years of historical data to examine is ideal, not all companies fall into this category. A vast majority of companies fall into the category of around 5 years of operational history. This free investment spreadsheet which is a modification of the free 10 year investment spreadsheet calculates the fair value of a company based on the past 5 years of available data from Morningstar and other sources. I’ll also take you through the steps to modify the spreadsheet yourself so that you can apply it to other databases other than Morningstar.

Changes to the Investing Spreadsheet

  • Multi yearperformance is calculated based on timeframes over the past 5 years.
  • Modified the forecasted EPS formula in the Benjamin Graham formula.

How To Fix The #DIV/0! Error

For companies such as DLB with 4 years of data instead of the required 5, you will get #DIV/0! errors because Excel is trying to calculate the median with invalid numbers.

DIV error

To overcome this, you simply have to restate the cell ranges by double clicking and then changing the range from =MEDIAN(B41:J41) to where there are actual values. In the case of DLB, this would be =MEDIAN(F41,I41;J41).

Median Edit

The screenshot shows the edit for the third row, but you would want to do it for the first row and then just grab the corner and drag it down to apply it to all cells.

Median edit 2

You can actually apply this method for the 10yr intrinsic value spreadsheet instead of downloading this one.

How To Customize The Spreadsheet

The stock investment spreadsheet utilizes an excel add-in called SMF (Stock Market Function) and in order to really understand how it works, you would have to follow the code and the comments within, but I’ll go over the main function.

(If you want further spreadsheets and templates with this add-in, visit the Yahoo page and navigate to the files section)

In the spreadsheets, the data in the statements tab is retrieved from Morningstar with the =RCHGetTableCell() function. Simply, this grabs the value from the cell of the table.

Currently the formula in the spreadsheet is: =RCHGetTableCell(“http://quicktake.morningstar.com/Stock/ Income10.asp?Symbol=”&Ticker, 2, “Fiscal Year-End:”, “>Revenue”)

To grab data from another site such as Yahoo Finance Australia for Telstra, I could go to the balance sheet page and then edit the function to display the cash by entering the following

=RCHGetTableCell(“http://au.finance.yahoo.com/q/abs?s=”&Ticker, 1, “PERIOD ENDING”, “>Cash”) where the ticker entered into the spreadsheet would be TLS.AX.

This command does the following:

  • points to the page http://au.finance.yahoo.com/q/abs?s=TLS.AX
  • selects the table that contains the exact phrase “PERIOD ENDING”
  • selects the very first cell right after the cell containing the exact phrase “CASH” which is characterised by the > sign.

To retrieve the next value from the table, you would simply change the 1 to 2 to get the second cell.

Do this method for all rows and cells. I just found out it was rharmelink from the SMF Group that created the 10 year statement template for Morningstar. (Just giving credit to the proper person)

To retrieve other information such as stock prices, historical prices, charts etc, you would use the other functions mentioned in the documentation.

I could go over the other useful functions but I think it’s pretty easy to figure out and will leave it up to you unless a number of readers request it specifically.

How to Install

A step by step guide is provided in the full spreadsheet installation guide.

IMPORTANT

Please read the guide and FAQ section. To date, I’ve been spending hours helping people with simple excel issues on a free product rather than anything spreadsheet related.

So for all excel and install problems, place all questions in the comment sections below. That way I won’t have to answer the same question again and again.

Download The Free 5Yr Investment Spreadsheet

5 Year Fair Value Stock Investment Spreadsheet

Get a cheap yet the best DCF Valuation spreadsheet which includes more calculations, graphs and tools.

——————————————————————————–

If you found this article helpful and would like to receive more information on free investing tools and spreadsheets, as well as business valuations, stock analysis and updates on merger arbitrage, subscribe to the Old School Value RSS or via email.