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 a free stock valuation spreadsheet that allows anyone to quickly value the fair value of a company (quantitatively). There are sites that already do this but I wanted something where I could do it on my own for any company. (download link is down the bottom)
, 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 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 stock valuation calculator is free, if you enjoy this spreadsheet, consider buying the best dcf and stock valuation spreadsheet starting at $10. I guarantee you’ll be making thousands with just a $10 investment.
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.
Download Section
- Old School Version: Benjamin Graham Stock Valuation Spreadsheet
- Randy’s SMF Add-in for Excel
- **Spreadsheet installation guide**
- You can also download the other FREE and best investment valuation spreadsheet.
Benjamin Graham Spreadsheet Screenshot
——————————————————————————–
March 21st, 2008 at 10:20 pm
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?
March 22nd, 2008 at 1:52 am
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.
April 19th, 2008 at 7:56 am
Great spreadsheet but do you know how I can put the add-in on iWork for Apple OSX?
April 19th, 2008 at 8:15 am
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.
March 28th, 2009 at 8:00 pm
To improve load time, turn off automatic calculations in excel. Then press F9 everytime you want to update.
June 1st, 2009 at 2:18 pm
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?
June 1st, 2009 at 2:40 pm
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
June 13th, 2009 at 3:40 pm
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?
June 13th, 2009 at 3:43 pm
@ 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
June 13th, 2009 at 3:45 pm
I’m trying to do it all manually so I can understand what I’m doing.
June 13th, 2009 at 4:04 pm
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
June 13th, 2009 at 4:25 pm
Never mind, I got it (lol). Thank you.
June 13th, 2009 at 4:27 pm
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.
June 18th, 2009 at 6:12 pm
How can this spreadsheet be easliy converted to Buffett’s ideas on investing?
June 19th, 2009 at 1:22 am
@ 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.
July 12th, 2009 at 3:21 pm
go to http://www.traineetrader.com/importing-stock-quotes-to-excel-using-smf-add-in/ for another look at how to install on excel 2007
July 20th, 2009 at 9:34 pm
i just purchased and downloaded. When I it F9 all cells turn to #NAME?. I checked Morningstar and the financials are there.
I reistalled the Ad-in several times. I also checked the macro function in Excel and have it set to ‘disable but notify’. I do not get a notification when I open the spreadsheet.
I am using 2007.
Your assistance is appreciated.
July 26th, 2009 at 5:11 am
First off, thank you for this invaluable resource. Time you put into this is much appriciated.
Bruce: I had a similar problem. One cell in the spreadsheet is linking to the plug-in which you probably stored in your program files. Try changing the location of the plug in to My Documents and changing the link in the cell. That’s how it worked for me.
July 26th, 2009 at 8:37 am
Thanks Ash,
I inform people to install it in a certain directory as they seem to find some difficulty in working with excel add-ins but as long as excel knows where the add-in is, it should work.
October 14th, 2009 at 1:22 pm
Jae,
Thanks for the spreadsheet it is great! I did have a question about one of your formulas. You project ‘normal earnings’ by taking the median of the previous three years EPS and the next three years projection. However to get the projection your calculation uses both the user defined growth rate plus the historical trendline(B11:D11). It seems that using both over inflates the projected earnings. I was just wondering why you think it is best to use both.
October 14th, 2009 at 1:48 pm
Hi Colton,
Since it is taking the normal earnings, if you just use the historical EPS the resulting number is the median value so the EPS would be grossly understated.
So that’s why I projected 3 years to try and come up with a realistic EPS for the next few years.
November 24th, 2009 at 9:02 pm
I’m new to investing and am reading The Intelligent Investor. On page 155 of the book, Graham states in his example: Our earning power value for American Smelting exceeds twice the asset value by $34 per share. How do you figure out the asset value of American Smelting?
November 24th, 2009 at 11:30 pm
Are you asking specifically for American Smelting? Because that company no longer exists.
November 28th, 2009 at 12:08 pm
Yes, I was specifically referring to American Smelting. Although it no longer exits, I was wondering if there is enough data given in the book to figure out how he came about his figure for net asset value. If so, how?
November 29th, 2009 at 12:19 am
Well if the book provides the numbers for the assets and the total liabilities, you just have to enter it into his formula.
See the post on net net asset value for details. If you don’t bother with the 50% and 75% multiplication, it is just a net asset value formula.
December 2nd, 2009 at 7:00 am
May I know the source of this formula? Thanks.
December 2nd, 2009 at 10:48 pm
Levelsix
The formula is from “The Intelligent Investor”