Category Archives: Tools

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 ,

Top 2 Websites for 10-Year Historical Stock Data

There are hundreds of websites around that provide real-time quotes and basic fundamental data for stocks such as P/E ratio, EPS, etc. Usually only current information is available however, as of the current trading day. Value investors by contrast often need up to 10 years of historical fundamental stock data to complete their research. Fortunately there are two excellent websites available that provide free ten year historical data. We’re going to look at both of them today. Continue reading

Tagged , , ,

Excel Watch List Spreadsheet for Value Investors

microsoft-excel-logo

Online watch lists are great for keeping tabs on potential investment opportunities but they’re limited in that it can be difficult to add columns for custom data such as intrinsic value, price targets, etc. I usually use an excel spreadsheet to manage my watchlist. This allows me to add any columns required to include additional information about the stock, and has the added benefit of being able to pull this information directly from my analysis itself (which I also keep in excel format). Today I am going to show you a sample excel watch list spreadsheet, and make it available for you to download and experiment with. Continue reading

Tagged ,

Stock Quotes in Excel with Office 2013

Stock market quotes on cubeMicrosoft Office 2013 is rolling out now, and is finally adding some functionality that will enable stock quotes to be dynamically updated in our excel worksheets. Currently, stock prices must typically be updated manually by hand, or through the use of clunky web queries and/or macros. Office 2013 incorporate the addition of Office Apps, one of which is called Bing Finance and automatically connects to Morningstar to download stock data for the selected tickers. Let’s have a look and see how this works. Continue reading

Tagged , ,

Calculating the PV & FV of Investments with Excel

microsoft-excel-logoMost of the stock valuation methods we use as value investors require some form of calculating the present value of a future investment, or determining what a dollar amount today will be worth X number of years in the future. What we are talking about here are present value and future value calculations. The equations themselves are not that complex, but Microsoft Excel has both functions built-in, so why not take advantage of this. Continue reading

Tagged , ,

Calculating CAGR for historical data with Excel

One very common task that we are faced with when analyzing historical data is the calculation of compound annual growth rate (CAGR). While possible to calculate by hand, it’s a somewhat tedious equation so fortunately for us Microsoft Excel has built in functions to do the calculations for us. This quick tutorial will show you how, using historical EPS data for Caterpillar (CAT). Continue reading

Tagged , ,