The Value Investing Blog

Empowering value investors with stock valuation tools, tutorials and resources

Benjamin Graham Formula Free Stock Valuation Spreadsheet

February 26th, 2008

47 Comments

Written by

Jae Jun

follow me on

Facebook

Twitter

Welcome to the Graham Formula

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 value 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. – Benjamin Graham

The Benjamin Graham Formula Overview

Ben Graham formula is as follows:

Intrinsic Value = \displaystyle\frac{EPS\times(8.5+2g)\times4.4}{20yr Corp Bond}

- 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 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’s Formula was Calculated

A difficulty I had was to figure out how to come up with a reasonable future EPS guide.

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.

  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

How to Download the Free Graham Formula Spreadsheet

To download the free Graham’s Formula spreadsheet, simply enter your email in the form at the bottom of the page. Once you have entered your email, you will automatically receive, not just the Graham formula spreadsheet but eight more spreadsheets for your own use. You will also be able to get articles such as this directly to your inbox.

How To Use The Free Spreadsheet

I’ve tried to make it as user friendly simple to understand.

In order to get it working though, you MUST install the plugin for excel which is described in the spreadsheet. It is a free plugin that allows excel to automatically retrieve financial data from the internet.

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.

The premium version includes several valuation models as well as 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.

Go now and see for yourself why people rave about the spreadsheets.

Free Benjamin Graham Formula Spreadsheet Screenshot

Additional links to resources

About Jae Jun

Jae Jun has written 529 value investing articles.

Jae is the founder of Old School Value and has a mission to empower every investor by providing tools, tutorials and educational material so that they can invest successfully on their own.

Recent posts by Jae Jun

You may also like:

  1. How to Value a Stock with Benjamin Graham’s Formula
  2. Updated Free Graham Net Net Spreadsheet
  3. Graham Cheap Stocks & Free Net Net Investment Spreadsheet
  4. Graham Valuation Screen
  5. Free Discounted Cash Flow Spreadsheet

Value stocks in 30 seconds with the stock valuation spreadsheets

Tags: graham formula, intrinsic value, spreadsheet

  • Luis

    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?

  • http://oldschoolvalue.blogspot.com Jae Jun

    Luis,
    If you have a specific issue, post a comment or send me an email ([email protected]) and I will get back to you.

  • Jose Castellon

    Great spreadsheet but do you know how I can put the add-in on iWork for Apple OSX?

  • http://oldschoolvalue.blogspot.com Jae Jun

    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.

  • http://www.oldschoolvalue.com Jae Jun

    To improve load time, turn off automatic calculations in excel. Then press F9 everytime you want to update.

  • Jay

    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?

  • http://www.oldschoolvalue.com Jae Jun

    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 :)

  • mxh

    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?

  • http://www.oldschoolvalue.com Jae Jun

    @ 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

  • mxh

    I’m trying to do it all manually so I can understand what I’m doing.

  • mxh

    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

  • mxh

    Never mind, I got it (lol). Thank you.

  • http://www.oldschoolvalue.com Jae Jun

    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.

  • Larry

    How can this spreadsheet be easliy converted to Buffett’s ideas on investing?

  • http://www.oldschoolvalue.com Jae Jun

    @ 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.

  • http://www.oldschoolvalue.com Jae Jun

    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

  • Bruce Kelvington

    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.

  • Ash

    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.

  • http://www.oldschoolvalue.com Jae Jun

    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.

  • Colton

    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.

  • http://www.oldschoolvalue.com Jae Jun

    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.

  • pat

    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?

  • http://www.oldschoolvalue.com Jae Jun

    Are you asking specifically for American Smelting? Because that company no longer exists.

  • pat

    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?

  • http://www.oldschoolvalue.com Jae Jun

    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.

  • Levelsix

    May I know the source of this formula? Thanks.

  • http://www.oldschoolvalue.com Jae Jun

    Levelsix

    The formula is from “The Intelligent Investor”

  • Newbee PS

    I tried using the OSV_Graham basic spreadsheet. It seems that the morningstar link to get revenues (“http://quicktake.morningstar.com/Stock/Income10.asp?Symbol=”) is no longer working. Does anyone know what should I replace the link with.

    Alternatively, is there an update to the spreadsheet that has this problem fixed?

  • http://www.oldschoolvalue.com Jae Jun

    Sign up using the form on the right and you will receive an email with all the latest working downloads.

  • http://www.fusioninvesting.com/ Dean

    Hi Jae
    Are you sure you have the brackets in the right place?
    = EPS*(8.5+(2*GROWTH*100)*(4.4/AAA BOND YIELD))

    Here is the formula as I recall it and as most sites say.
    http://upload.wikimedia.org/math/f/1/7/f175ed700a765b0437445ede304e684e.png

    Which would have the brackets like this
    =EPS*(8.5+2*GROWTH*100)*4.4/AAA BOND YIELD
    or simplified to
    =EPS*(8.5+200*GROWTH)*4.4/AAA BOND YIELD

    I look forward to you answer.

    Cheers
    Dean

  • http://www.oldschoolvalue.com Jae Jun

    Yes the article needs to be updated with the proper brackets. Premium spreadsheets all have the proper formulas.

  • Anand

    Wonderful post Jae. I am researching on the relevance of Benjamin Graham formula to non-American stocks – particular Indian stocks. I would like to hear from you about the relevance of it when I use EPS in Indian rupee, as opposed to American dollar. Mathematically, I see no difference since the resulting valuation will be in rupees. But would like to hear your opinion.

  • http://www.oldschoolvalue.com Jae Jun

    It shouldnt matter what the currency is. Earnings is the same everywhere.

  • http://www.manhattan.com james moylan

    I have a web site where I research stocks under five dollars. I have many years of experience with these type of stocks. I find that the best measurement of how undervalued a stock is is the price to sales ratio of a companies stock. the price to sales ratio is the market cap of a companies stock compared to the amount of sales the company does on an annual bases.a good example of a company with a low price to sales ratio is carrols restaurant group the company has a market cap of just 200 million dollars but does over 800 million dollars in annual sales the company is solidly profitable. in other words the price that the market is valuing the company at is 200 million dollars this is only one fourth of what the company does in annual sales 800+ million dollars. the stock currently trades at around 9.25 cents a share under the symbol {TAST} I think the stock could get to 50.00 dollars a share over the next five years. I base this on the current net profit margin of around 1.75% or 14 million dollars on sales of 800 hundred million dollars. if the companies sales were to increase by 50% or 400 million dollars to 1.2 billion dollars over the next five years. and if the companies net profit margin were to expand from 1.75% to4.5% or 54 million dollars over the next five years. than if the companies stock increased in price to where it was trading at a price earnings ratio of 15 this would put the stock at 50 dollars a share. this may seem to be a somewhat optimistic scenario but not really that much. there are many stocks that trade at much higher price earnings ratios when they become popular than 20 times earnings. I find that companies like carrols restaurant group are very rare. I also find that companies that have low price to sales ratios that are profitable or of decent quality tend to become takeover targets or get taken private by private equity firms or the management of the company. or other companies in the same business.
    .

  • http://www.oldschoolvalue.com Jae Jun

    Hey James,

    One thing Im concerned about your valuation of TAST is that there are far too many “if’s”. What value do you come up with if TAST is unable to reach your target? What is the value of TAST now?

  • tierno bane

    hello jae jun
    what do you think about the mean reversion method to value stock?
    thanks

  • http://www.oldschoolvalue.com Jae Jun

    can you provide more information on what this is?

  • Pat Connell

    Jae I was looking at one of your email’s and tried to download the Benjamin Graham formula. I went back to the home page and put my email address in and the response I received was I was already subscribed. I know that but I wasn’t allowed into the site to download the spreadsheet. I already have the plug in from downloading the valuation spreadsheet. That is the one I enjoy the best.
    Another question on finance stocks are there any plans to in the future to program a similiar valuation spreadsheet regarding bank and other financial stocks. I believe that the financial equities will have a strong upside. Not sure when but they will come back. Thanks Pat Connell

  • http://www.oldschoolvalue.com Jae Jun

    @ Pat,
    Seeing as how I don’t understand the business of financial instituitions, I’m going to stick my head out and say that I probably won’t be able to create what you are looking for. But your question related to the graham spreadsheet was solved I believe :)

  • http://BlackbirdBioFinance.com Sam

    Hey

    Do you have a service where we can simply typemin the equity name and we get a graph of valuation?

    Also how do you handle non revenue producing biotechs?

    Sam

  • http://www.oldschoolvalue.com Jae Jun

    Hi Sam,

    The premium spreadsheets graph the historical stock price vs the intrinsic value based on DCF but that’s about it at the moment.
    Biotechs are out of my league so I wont be able to answer that one.

  • Vivek

    Hi, Can we have this work for Indian equities ?

    thanks
    Vivek

  • http://www.oldschoolvalue.com Jae Jun

    Sorry. No international markets are supported.

  • Dharmender

    I tried applying basic Graham formula to few companies. It works for few companies but for few others I get a very high value of growth rate (and of intrinsic value).

    for example – for JDA, EPS TTM in 2003 and 2012 were .31 and 1.67, G computes to (1.67-0.31)/(9*0.31)= 49%
    For AAPL this computers to (27.68 -0.09)/(0.09 * 9) = 3406%..

    What am I doing wrong?

  • http://www.oldschoolvalue.com Jae Jun

    Those companies had huge growth. You need to adjust the growth by entering in a different EPS.

  • http://yahoo tony muscat

    Dear Jae.
    I noticed that downloading the bg equation is far too complicated especially when I am not technically savvy. Can you make it more simpler?
    Thanks tm

  • Nitin

    Hi Jae, this is a great initiative on your part. Keep up the good work. I am already subscribed to your newsletter. How can I download the Ben Graham DCF spreadsheet from your link? Thanks and regards.