# Benjamin Graham Formula Free Stock Valuation Spreadsheet

Pick the best value stocks with our Stock Ranks, screening and valuation tool. Try the live demo today.

Written by

Jae Jun

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

********

#### 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 = (EPS x (8.5+2g) x 4.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.

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.

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
7. And so on

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.

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

Click to Enlarge

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.

### 54 responses to “Benjamin Graham Formula Free Stock Valuation Spreadsheet”

1. Luis says:

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?

2. Jae Jun says:

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

3. Jose Castellon says:

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

4. Jae Jun says:

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.

5. Jae Jun says:

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

6. Jay says:

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?

7. Jae Jun says:

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 🙂

8. mxh says:

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?

9. Jae Jun says:

@ 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

10. mxh says:

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

11. mxh says:

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

12. mxh says:

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

13. Jae Jun says:

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.

14. Larry says:

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

15. Jae Jun says:

@ Larry
What ideas are you referring to?
Buying good companies at a cheap price?

The spreadsheets on this site actually deal with all three and more.

16. Jae Jun says:

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

17. Bruce Kelvington says:

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.

18. Ash says:

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.

19. Jae Jun says:

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.

20. Colton says:

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.

21. Jae Jun says:

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.

22. pat says:

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?

23. Jae Jun says:

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

24. pat says:

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?

25. Jae Jun says:

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.

26. Levelsix says:

May I know the source of this formula? Thanks.

27. Jae Jun says:

Levelsix

The formula is from “The Intelligent Investor”

28. Newbee PS says:

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?

29. Jae Jun says:

30. Dean says:

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.

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

31. Jae Jun says:

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

32. Anand says:

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.

33. Jae Jun says:

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

34. james moylan says:

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

35. Jae Jun says:

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?

36. tierno bane says:

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

37. Jae Jun says:

38. Pat Connell says:

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

39. Jae Jun says:

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

40. Sam says:

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

41. Jae Jun says:

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.

42. Vivek says:

Hi, Can we have this work for Indian equities ?

thanks
Vivek

43. Jae Jun says:

Sorry. No international markets are supported.

44. Dharmender says:

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?

45. Jae Jun says:

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

46. tony muscat says:

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

47. Nitin says:

48. BenZ says:

50. Bipan Guleria says:

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

51. Bipan Guleria says:

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

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

53. durairaj says:

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

54. 24.41% = 0.2451