Blog > Investment Tools > Blog article: 5 Year Intrinsic Value Spreadsheet

5 Year Intrinsic Value Spreadsheet

by Jae Jun

The spreadsheet is now placed under a basic and premium version. The basic version will be downloadable for free while the premium version can be purchased here.

Although finding great, stable companies with more than 10 years of history to examine is ideal, not all companies fall into this category. A boat load of companies fall into the category of around 5 years of operational history. This modified spreadsheet calculates the intrinsic value based on the last 5 years and 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.

This version isn’t much different from the other one, except for the 5 year time frame calculation.

Changes

Multiyear performance is calculated based on timeframes over the past 5 years.
Modified the forecasted EPS formula in the Graham formula tab.

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 spreadsheet utilises 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 OSV 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.