The Graham Formula Spreadsheet
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, but an explanation of the Graham Formula and how to use it is explained in the article titled Graham Formula Stock Valuation tutorial.
Instead, I’ve applied Benjamin Graham’s formula to a free Graham Formula spreadsheet that will allow you to quickly calculate the intrinsic value of a company the Benjamin Graham way.
There are a couple of sites that already do this online, but I wanted something where I have control and be able to make adjustments.
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.
The Benjamin Graham Formula Overview
Ben Graham formula is as follows:
- V is the intrinsic value
- EPS refers to earnings over a period of years and not just the previous or current year. Use a normalized version.
- 8.5 is the PE of a company with no growth.
- g is growth rate of the expected earnings. In the premium stock value spreadsheet, growth rate is user-defined. Check out a method to determine growth rate.
- 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 today’s rate. I like to use the 20yr AA corp bond rate as the denominator since the AA rate is slightly higher than the AAA and will give a slightly conservative number.
However, I use a very slight modification to this formula for today’s market context which I detail in an article I wrote titled “How to Value a Stock with the Ben Graham Formula.”
How the Expected Earnings in the Graham Formula was Calculated
A difficulty I had was to figure out how to come up with a reasonable future EPS guide.
Before we get into the calculations, just click on the image below to get exclusive content and valuable resources that we don’t publish anywhere else.
Here is how I calculated the future EPS. Note, I am a conservative guy. If you feel the ranges are incorrect, try changing some things yourself.
- For the 1st future year, I took the constant at which the EPS had linearly increased over 10 years
- I added the constant to the average increase of EPS throughout the past 10 years
- I then added an additional “growth sum” to the number I get from step 2
- For the 2nd future year, I took the constant
- Added it to the 1st future year
- Added the “growth sum”
- And so on
New Graham Formula Spreadsheet
We made some updates and moved the spreadsheet into Google Sheets to make it easier for you to access and use.
Saving a Google Sheets Graham Formula Spreadsheet
- DO NOT request to share the spreadsheet. Just make a copy yourself. See below.
- Yellow cells are where you manually enter data.
- Aqua colored cells are formulas so do not overwrite.
To save a copy into your own account, do the following:
- Sign into your Google Docs account
- Click this link to open Graham Formula Spreadsheet
- Go to File > Make a Copy
- Rename and press OK to save to your account
While you’re at it, you can also get more spreadsheets and checklists by registering your email with Old School Value.
How To Use The Free Spreadsheet
I’ve tried to make it as user-friendly simple to understand.
The spreadsheet no longer requires manual inputs for the required data and attempts to automatically pull the information in from Google Finance and FRED.
Follow the instructions in the spreadsheet to use it properly.
Premium Stock Valuation Spreadsheets
Feel free to check out this free version and then when ready, go to the stock valuation software page and review what you will get with the premium version.
The premium version includes the Graham Formula and several other valuation models, plus fundamental analysis data, historical data, charts and competitor comparison features. Just by entering one ticker, you can immediately get all that information on your favorite stock which will save you hours in your analysis.
Free Benjamin Graham Formula Spreadsheet Screenshot
Additional links to resources