- Welcome to the Graham Formula Spreadsheet
- The Benjamin Graham Formula Overview
- How the Expected Earnings in the Graham’s Formula was Calculated
- How to Download the Free Graham Formula Excel Spreadsheet
- How To Use The Free Spreadsheet
- Premium Stock Valuation Spreadsheets
- Free Benjamin Graham Formula Spreadsheet Screenshot
- What is Old School Value?

## Welcome to 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.

-Benjamin Graham

## The Benjamin Graham Formula Overview

Ben Graham formula is as follows:

- V is the intrisic 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’s 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

## How to Download the Free Graham Formula Excel 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.

The spreadsheet requires manual inputs for the required data.

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.

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

## Free Benjamin Graham Formula Spreadsheet Screenshot

**Additional links to resources**

To get this kind of information and other exclusive articles before regular readers, get on the VIP Mailing List today.

### What is Old School Value?

Old School Value is a suite of value investing tools designed to fatten your portfolio by identifying what stocks to buy and sell.

It is a stock grader, value screener, and valuation tools for the busy investor designed to help you pick stocks 4x faster.

Check out the live preview of AMZN, MSFT, BAC, AAPL and FB.

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 ([email protected]) 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.

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

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?

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

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

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.

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.

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.

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.

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.

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?

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

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?

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.

May I know the source of this formula? Thanks.

Levelsix

The formula is from “The Intelligent Investor”

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?

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

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

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

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.

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

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.

.

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?

hello jae jun

what do you think about the mean reversion method to value stock?

thanks

can you provide more information on what this is?

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

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

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

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.

Hi, Can we have this work for Indian equities ?

thanks

Vivek

Sorry. No international markets are supported.

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?

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

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

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.

Download link is not available.

You will receive the download links once you sign up to the blog with your email.

will the excel sheet work for indian stock market also? or do i need any extra plugin

have you found any thing that works for indian market .plz let me know. regards

since you enter the data in manually, it should work for all markets as long as the data is available.

if growth is 24.41% ,what should we consider ? whether 24.41 or.2451 in graham number formula

24.41% = 0.2451