Free Discounted Cash Flow (DCF) Spreadsheet

I’ve had several requests to make my discounted cash flow (DCF) spreadsheet publicly available. As a refresher, DCF analysis is a method of forecasting the cash flows that a company generates into the future, and then discounting them back to a present day value. This value can then be used to determine what the current stock price should be (called its intrinsic value), and a comparison can be made against its actual trading price. It might be an indication that a stock is undervalued if it is currently trading at less than its intrinsic value. Many investors would then consider this a buy signal and initiate or add to a position.

Spreadsheet: Stockodo Discounted Cash Flow Spreadsheet

There are many methods to conduct a DCF analysis, some more complex than others. There’s often a tradeoff between complexity and accuracy, and I therefore prefer to take more thorough approach to my analysis. Use the link above to access my spreadsheet on Google docs. Feel free to make a copy for yourself and use it in your own analysis. Standard disclaimers apply as to the use of information arising from this spreadsheet.

 

This spreadsheet looks at a 20 year period consisting of the prior ten years and future ten years from today. The historical information is entered manually using data from websites such as GuruFocus. As historical data is entered, percentages of revenue are calculated. We look at these numbers to identify trends, and then enter our forecast percentages into the future column. This then performs the reverse operation to calculate the future metrics. Any number in blue text is intended to be a user input. For a refresher on DCF analysis in general, I suggest you check out my previous article on the topic. The spreadsheet is prepopulated with an analysis I recently conducted on Chevron (CVX). You may also be interested to see how DCF analysis fits into an overall strategy of evaluating a company.

Once all data is entered, the next step is to choose a minimum acceptable rate of return (MARR), which is also called the discount rate. Typical values of MARR range from 9% – 15%. A long term growth rate must also be entered, usually in the range of 3-4%. This will then calculate the enterprise value of the company. The enterprise value includes all debt and excludes cash. We subtract and add these items in respectively to finally determine the company’s intrinsic value.

I hope you will find this spreadsheet useful. Please post some company valuations you come up with in the comments below!

Tagged ,

5 thoughts on “Free Discounted Cash Flow (DCF) Spreadsheet

  1. NickJT says:

    Hi Dustin, great site. The DCF spreadsheet is really good but I’ve hit a problem. where do you get 10yr tax figures from? I couldn’t find them on GuruFocus. Thanks
    Nick

    • Dustin Small says:

      Hi Nick, Sorry for the late reply. 10-yr tax data is on Gurufocus. Bring up a stock quote and then click on 10-Y Financials. Tax paid for the previous 10 years is under the income statement section.

  2. Firstwanted says:

    Hi, Dustin
    Thank you for share this excel, but it doesn’t match with morningstar or gurufocus.
    For Example, CVX’s free cash flow is 6690 at morningstar in 2003, but it’s 7190 at excel. According to previous articles about DCF in here, They should be match, right? Can you tell me why, please?

    • Dustin Small says:

      Hi,

      No, there are several methods to calculate free cash flow and the definition is very subjective. Sites such as Morningstar must rely on automated systems to calculate free cash flow since they are processing thousands of stocks. Using a spreadsheet you can be much more selective on what gets included, which is why the excel spreadsheet is coming out with a different result.

  3. Steffen says:

    Thank you for a fantastic spreadsheet and also a great tutorial. I seem to have problems with identifying the figure to put in line 35 “other”. In your example of CVX the sum of ‘other’ in 2003 is made up of =(17981+474)+5326. how do you find the first figure 17981. Love your site by the way

Leave a Reply

Your email address will not be published. Required fields are marked *