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 Benjamin Graham’s formula to an excel investing spreadsheet I created to allow anyone to quickly value the fair value of 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.
, 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 Benjamin Graham’s formula to an excel investing spreadsheet I created to allow anyone to quickly value the fair value of a company (quantitatively). There are sites that already do this such as If you just want the file, here it is.
Old School Version: Ben Graham Fair Value Investment 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
Benjamin Graham Formula Overview
Ben 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 refer to earnings over a period of years. Not just the previous year.
- 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.
- 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 since the AA rate is slightly higher than the AAA and will give a slightly conservative number.
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 or try changing some things yourself and if it works better, let me know.
- 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
Other Points
Since this investment spreadsheet is free, if you do use this spreadsheet, please do not delete the disclaimer tab. Other than that you can do anything you want.
Download Section
- Old School Version: Benjamin Graham Fair Value Investing Spreadsheet
- Randy’s SMF Add-in for Excel
- **Spreadsheet installation guide**
- You can also download the other FREE and best premium investing spreadsheets.
Benjamin Graham Spreadsheet Screenshot
——————————————————————————–
If you found this article helpful and would like to receive more information on free investing tools and spreadsheets, as well as business valuations, stock analysis and updates on merger arbitrage, subscribe to the Old School Value RSS or via email.
This is a great spreadsheet and I would love to use it. I have Microsoft Office 2007 and I tried adding the add-in and it is not working properly with the dirrections you provided. I installed the add-in but it is not working…. the sheet does not update the cells. Can you please help?
Luis,
If you have a specific issue, post a comment or send me an email (jjun0366@gmail.com) and I will get back to you.
Great spreadsheet but do you know how I can put the add-in on iWork for Apple OSX?
Sorry but I have no idea how excel works in Apple. Doesn’t Apple allow you to run windows as well? If that is the case I assume it is the same as just PC. Unzip it to the correct folder and run it.
[...] I put up a Graham intrinsic value spreadsheet. As with any intrinsic value calculating method, there are shortcomings and disadvantages. Even [...]
[...] Installation of the SMF-addin is required and the installation instructions can be found here. [...]
To improve load time, turn off automatic calculations in excel. Then press F9 everytime you want to update.
[...] Portfolio Vs SPY Premium Tools 10YR+20 Qtr Financial Statements Ben Graham Formula Ben Graham Net Net 5YR Intrinsic Value 10YR Intrinsic Value Competitor [...]
[...] Statement Analysis: Statement of Cash Flows AeroGrow (AERO)Discounted Cash Flow & Stock ValuationBenjamin Graham Formula Free Investment SpreadsheetForbes 200 Best Small Companies ProjectFree Fair Value Investing SpreadsheetForbes Best Small [...]
[...] Statement Analysis: Statement of Cash Flows AeroGrow (AERO)Discounted Cash Flow & Stock ValuationBenjamin Graham Formula Free Investment SpreadsheetForbes 200 Best Small Companies ProjectFree 10YR DCF Fair Value Investment SpreadsheetAnalysis Now [...]
[...] Statement Analysis: Statement of Cash Flows AeroGrow (AERO)Discounted Cash Flow & Stock ValuationBenjamin Graham Formula Free Investment SpreadsheetForbes 200 Best Small Companies ProjectFree 10YR DCF Fair Value Investment SpreadsheetAnalysis Now [...]
I love the organization and quality of the worksheet. My only question is, what exactly is user growth and why is it set at 22%? Is it wise to change it?
Hi Jay,
For the Ben Graham Formula spreadsheet, the growth rate is the EPS growth rate normalized over 5 or 10 years depending on which spreadsheet you are using. If this rate is too high, it’s sensible to adjust the rate yourself but dont use the growth rate that only projects for the next year as it will provide skewed and incorrect results.
If you like the quality and organization, you should check out the premium version
I’m new to investing and am having trouble plugging the numbers into the formula. I know it’s something simple but I can’t figure it out. In the above example (AAPL) my formula looks like this:
5.16 x (8.5 + 30) x .0597
5.16 x 38.5 x .0597 = $11.86
Would someone please point out where my mistake is?
@ MXH
(edited my first comment because I misunderstood what you meant)
Gotta be careful with the brackets and which operations should be done first.
=5.16*(8.5+(2*15)*(4.4/5.5)) = $167.7
Work your way from the inside and out
I’m trying to do it all manually so I can understand what I’m doing.
5.16 * 38.5 = 198.66
198.66 * 4.4/5.5
198.66 * .8 = 158.93
I know this is remedial but cant’t figure out
Never mind, I got it (lol). Thank you.
It seems like youre doing it backwards. Do these steps one at a time and make sure you press = after you do the +8.5 otherwise it will do 8.5×5.16 which isnt what we want.
4.4/5.5 x (2 x 15) + 8.5 = 32.5
32.5 x 5.16 = $167.7
viola. Hope that helps.
How can this spreadsheet be easliy converted to Buffett’s ideas on investing?
@ Larry
What ideas are you referring to?
Buying $1 for 50c?
Buying good companies at a cheap price?
Buying with margin of safety?
The spreadsheets on this site actually deal with all three and more.