Benjamin Graham Formula Valuation Spreadsheet

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.

If you haven’t read The Intelligent Investor, you are missing out on timeless advice. One of which is to buy at a great margin of safety. I won’t be going through the details of the book here. Instead, I’ve applied Graham’s formula to an excel spreadsheet I created to allow an investor to quickly valuate a company (quantitatively). There are sites that already do this such as Invest In Value but I wanted something where I could do it on my own for any company.

If you just want the file, here it is. Old School Version: Graham Intrinsic Formula Spreadsheet.

A quick quote to start things off.

Confronted with a like challenge to distill the secret of sound investment into three words, we venture the following motto, Margin of Safety. - Benjamin Graham

Brief Overview

Graham’s formula is as follows:

Intrinsic Value = “normal” earnings x (8.5 + (2 x expected 5 yr growth)) x (4.4/20yr AA corp bond)

- Normal earnings refers to earnings over a period of years. Not just the last year.
- I’ve tried to find some info on where the 8.5 derives from but I can’t seem to find it. (Edit: Thanks to Chris for letting me know that 8.5 is the PE of a company with no growth.)
- In the spreadsheet, growth rate is user defined. Check out a method to determine growth rate here.
- Back when Graham wrote the book, he was using a 20 yr AAA corp bond rate of 4.4%. To apply the formula today, we need to normalize it to todays rate. I’ve put the 20yr AA corp bond rate as the denominator because the rate is slightly higher than the AAA.

How To Use The Spreadsheet

I’ve tried to make it as user friendly and eye pleasing as possible. In order to get it working though, you MUST install the plugin for excel which is described below. The plugin allows excel to automatically retrieve all financial statements and prices that I use in the spreadsheet.

Some Explanations

A difficulty I had was to figure out how to come up with a reasonable future EPS guide. I know I’ve said I don’t like using EPS as a guide and I still stick to that. However, I wanted to see how the Graham formula worked and what type of valuation it revealed.

Here is how I calculated the future EPS. Note, I am a conservative guy. If you feel, the ranges are incorrect, let me know and try changing some things yourself and let me know.

1. For the 1st future year, I took the constant at which the EPS had linearly increased over 10 years
2. I added the constant to the average increase of EPS throughout the past 10 years
3. I then added an additional “growth sum” to the number I get from step 2
4. For the 2nd future year, I took the constant
5. Added it to the 1st future year
6. Added the “growth sum”
7. And so on

Test It Out

Test it out, let me know, make changes, give me some ideas or feedback.
If you do use this spreadsheet, please don’t delete the disclaimer tab.

Download Section

Old School Version: Graham Intrinsic Formula Spreadsheet
Randy’s SMF Add-in for Excel

How to Install

You have to unzip the file into c:/program files/SMF Add-In
If you put it in any other folder it wont work.

For Excel 2003:

1. On the Tools menu, click Add-Ins
2. Click the Browse button and navigate to the smf add ins folder
3. Select the add in file that you see and then click OK
4. Click Yes to any file copy or overwrite prompts
5. Verify the Stock Market Function add in box is checked
6. Press OK

For Excel 2007:

1. Click the orb and then click on excel options
2. Click Add in
3. Down the bottom there is a drop down list and next to it there is a move button (I think). Click on the button
4. Navigate to the SMF add in folders
5. select the file and then click OK
6. Verify the Stock Market Function add in box is checked
7. Press OK

Open the spreadsheet, enter and ticker and you will see a “calculating xx%” in the bottom right corner. You are good to go.

Note: if you download the file, copy it to a USB and then copy it back to another folder or computer, MAKE SURE that the data in the “Statements” tab is pointing to c:/program files/SMF Add-in and not your USB drive.

The premium version can be purchased here.

You may also like:

• Intrinsic Value Spreadsheet
• Personal Portfolio vs S&P 500 Spreadsheet
• Basic and Premium Spreadsheets
• 10 Year History Chart Spreadsheet
• 5 Year Intrinsic Value Spreadsheet