Free 5YR DCF Fair Value Spreadsheet

EDIT: This is now an outdated article. Please see this updated article with a free discounted cash flow spreadsheet.

3 free fair value spreadsheet calculators are available for you to download if you sign up with your email here:

Table of Contents show

The DCF Fair Value 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.

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

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.

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.