A Graham Number screener in Excel needs three columns per stock: trailing EPS (=EarningsPerShare("JNJ")), book value per share (=BookValuePerShare("JNJ")), and price (=QM_Last("JNJ")). The Graham Number is =SQRT(22.5*EPS*BVPS), and a stock passes the basic screen when its price is below that number. Add filters for P/E, price to book, current ratio, and debt to equity to approximate Graham's full defensive checklist.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
This guide explains the formula, shows the MarketXLS formulas for each input, and walks through a free 12-sheet template that ranks 30 large-cap US stocks by margin of safety, runs seven preset screens, and stress-tests each name for EPS and book value cuts. Data in the sample file is as of June 12, 2026. This is educational content, not investment advice.
What is the Graham Number?
The Graham Number is the highest price a defensive investor should pay for a stock under Benjamin Graham's criteria: the square root of (22.5 times earnings per share times book value per share). It is derived from Graham's rule in The Intelligent Investor that a stock's P/E multiplied by its price to book should not exceed 22.5. The 22.5 multiplier is the product of Graham's two preferred maximums: a price to earnings ratio no greater than 15, and a price to book ratio no greater than 1.5. Multiplied together, they yield 22.5. When the current price trades below the resulting number, the stock has at least one form of margin of safety against the defensive investor's ceiling.
The formula in pure form:
Graham Number = SQRT(22.5 * EPS * Book Value Per Share)
Two notes. First, the formula requires positive EPS and positive book value per share. Companies losing money, or those with negative tangible equity from share repurchases, are disqualified by the math itself. Second, the Graham Number is a snapshot, not a forward valuation. It uses trailing earnings and the most recent balance sheet, not a discounted cash flow model.
Why a Graham Number screen is useful in June 2026
A Graham Number screen separates stocks that meet a conservative valuation ceiling from those that do not, which is useful when index returns are concentrated in a few high-multiple companies. Large technology stocks with high P/E and price to book ratios cannot pass a Graham defensive screen, while some energy, healthcare, and other mature companies can.
The screen does not make a market call; you add your own judgment on top.
Snapshot - Graham screen results (illustrative, data as of 2026-06-12)
| Sector | Avg PE | Avg P/B | Avg Margin of Safety | Survivors (Default Screen) |
|---|---|---|---|---|
| Energy | 14.1 | 2.12 | 36.1% | 2 |
| Healthcare | 13.0 | 5.31 | 12.4% | 2 |
| Consumer Discretionary | 12.4 | 29.5 | 18.2% | 1 |
| Communication Services | 17.1 | 5.78 | 8.4% | 2 |
| Consumer Staples | 26.0 | 9.13 | -28.5% | 0 |
| Technology | 21.7 | 11.4 | -22.6% | 1 |
| Industrials | 14.1 | 12.1 | -38.4% | 0 |
| Materials | 12.1 | 2.15 | 9.2% | 1 |
| Utilities | 20.2 | 1.83 | -7.4% | 0 |
In this snapshot, Energy and Healthcare had the highest average margins of safety. Consumer Staples and Industrials, despite their reputation as defensive sectors, look expensive on Graham's grid because brand value and intangible assets do not show up in book value.
How the Graham Number is built
The Graham Number uses two inputs, earnings per share and book value per share, both taken from reported financial statements. The 22.5 multiplier is the product of two valuation ceilings Graham set for the defensive investor: a P/E of 15 and a price to book of 1.5.
The first ceiling - a PE ratio of 15 - comes from Graham's observation that long-term earnings yields on stocks should rival the rate on AAA corporate bonds. A PE of 15 implies an earnings yield of 6.67 percent, which historically tracked high-quality corporate yields with a small risk premium. Compare that 6.67 percent earnings yield with current high-grade bond yields to judge whether the ceiling still offers an equity risk premium.
The second ceiling - a price to book ratio of 1.5 - comes from Graham's belief that the defensive investor should not pay more than 50 percent over tangible book value for the average business. He allowed exceptions when book value was depressed by share repurchases or accounting noise, but the 1.5 cap was his default. Multiply 15 by 1.5 and you get 22.5. Multiply 22.5 by EPS and BVPS and take the square root, and you get a single dollar figure: the maximum price a defensive investor should pay.
Margin of safety
Margin of safety in this template is how far the price would have to rise to reach the Graham Number:
Margin of Safety = (Graham Number - Current Price) / Current Price
A positive margin of safety means the stock trades below the ceiling. A 20 percent margin means the price would have to rise 20 percent to reach the Graham Number. Graham's idea of buying at about two-thirds of value (a one-third discount) equals a 50 percent reading on this formula, because the price must rise 50 percent to reach the ceiling. Set your own threshold in the Inputs sheet.
What is inside the Graham Number Screener Excel template
The Graham Number Screener Excel template ships as two files: a Sample workbook with static values for one-time inspection, and a Template workbook with live MarketXLS formulas that refresh on demand. Both files use the same 12-sheet layout:
1. Cover
A branded cover page with the workbook title, edition, data-as-of date, and a navigable table of contents. No data lives here. The Cover sheet exists for presentation polish - it is the first thing a client or colleague sees when you open the file in a meeting.
2. How To Use
Step-by-step tutorial that explains every input cell, every formula by name, and the recommended workflow. New users open this sheet first. It also catalogs every MarketXLS function the workbook calls, with one-line descriptions, so users know exactly which formulas they would need to rebuild any sheet from scratch.
3. Dashboard
The headline sheet. Six KPI tiles span the top: average margin of safety, deepest discount in the universe, average PE, average price to book, the composite value score, and the number of names passing the default screen. Below the tiles, a deep value leaderboard ranks the top 15 names by margin of safety with conditional formatting heatmaps on PE and price to book. Two embedded charts - sector average margin of safety and sorted PE distribution - sit alongside the data tables. Gridlines are hidden, the print area is set to landscape, and the entire sheet is designed to fit on one page when printed.
4. Inputs and Controls
The single source of truth for every downstream calculation. Yellow input cells with bold borders take seventeen parameters: selected scenario, max PE ratio, max P/B ratio, minimum current ratio, max debt to equity, minimum margin of safety, minimum market cap, portfolio size, weighting method, sector cap, EPS shock, book value shock, risk tolerance, three custom tickers for the comparison matrix, and benchmark index. Five data-validation dropdowns enforce valid entries on the scenario picker, weighting method, risk tier, and benchmark. Change any input and every other sheet updates instantly.
5. Graham Screener
The full 30-name screener. Each row carries ticker, company, sector, last price, EPS, book value per share, computed Graham Number, margin of safety, PE ratio, P/B ratio, ROE, current ratio, debt to equity, dividend yield, and a 0-100 composite value score. Color scales highlight cheap PE and P/B in green, low margin of safety in red, healthy current ratios in green, and the value score gets a data bar. In the Template version every metric is a live MarketXLS formula. In the Sample version each data cell carries a comment showing the underlying MarketXLS function that would produce it.
6. Scenario Analysis
A pass/fail matrix that runs seven preset screens against the same universe simultaneously: Strict Defensive (Graham 1973), Margin of Safety (Default), Deep Value Only, Moderate Value, Growth at Reasonable Price, Dividend + Value, and Quality + Value. Each ticker gets a colored PASS or FAIL cell under every scenario. A survivor count and hit rate appear at the bottom, along with a threshold definition table. The matrix shows immediately how strictness changes the universe: how many names survive a 15x PE cap, how many additional ones clear a 20x cap, and which sectors disappear when you require a 25 percent margin of safety.
7. Sector Valuation
The sector heatmap. Each of the nine sectors gets one row showing the count of constituents, average PE, average P/B, average margin of safety, average ROE, average debt to equity, average current ratio, and average value score. Conditional formatting applies a red-to-green gradient on every column according to whether high or low is desirable. An embedded horizontal bar chart visualizes sector average PE so users can see at a glance which corners of the market are cheap.
8. Margin of Safety Stress
The stress test sheet. Each ticker is run through six shock scenarios: EPS down 10 percent, EPS down 20 percent, EPS down 30 percent, book value down 10 percent, book value down 20 percent, and a combined 20 percent shock to both. The Graham Number is recomputed under each scenario and the new margin of safety is shown. Positions whose stressed margin of safety drops below zero are the ones most exposed if earnings disappoint or write-downs hit. Color scales make the breakdowns visible at a glance.
9. Portfolio Allocation
The position sizing sheet. The screener's survivors flow into a portfolio sleeve sized to the portfolio dollar amount entered on Inputs. Three weighting methods are presented side by side: equal weight, score weight (allocates more to higher composite scores), and yield weight (allocates more to higher dividend yields). A totals row at the bottom checks that weights sum to 100 percent. An embedded pie chart visualizes the score-weighted allocation with category labels and percentages.
10. Comparison Matrix
A side-by-side comparison of three user-selected tickers plus the benchmark index. Eighteen rows of metrics cover company name, sector, price, EPS, book value, Graham Number, margin of safety, PE, price to book, ROE, current ratio, debt to equity, dividend yield, beta, YTD return, value score, qualitative value label, and a colored pass/fail flag for Graham's defensive checklist. The pass/fail row paints green or red so the answer is impossible to miss.
11. Methodology
An eight-section explainer covering the Graham Number formula derivation, defensive investor criteria, universe selection, value score weighting, scenario thresholds, stress test design, data sources, and limitations. It documents every assumption so the screen is not a black box.
12. Glossary and Disclaimer
Definitions for every term the workbook uses, followed by a clear educational-only disclaimer. Useful when sharing the file with clients or colleagues unfamiliar with Graham's vocabulary.
Building the screener in Excel with MarketXLS
To build a Graham Number screener from scratch, you need about a dozen MarketXLS formulas:
=QM_Last("JNJ") -> Current price
=EarningsPerShare("JNJ") -> Trailing 12-month EPS
=BookValuePerShare("JNJ") -> Book value per share
=PERatio("JNJ") -> Price to earnings ratio
=PriceToBook("JNJ") -> Price to book ratio
=current_ratio("JNJ") -> Current ratio
=TotalDebtToEquity("JNJ") -> Debt to equity ratio
=ReturnOnEquity("JNJ") -> Return on equity
=DividendYield("JNJ") -> Trailing 12-month dividend yield
=MarketCapitalization("JNJ") -> Market cap in USD
=Sector("JNJ") -> GICS sector classification
=Beta("JNJ") -> Beta vs S&P 500
=ChangePercentYTD("JNJ") -> YTD price change
Formula documentation: QM_Last, EarningsPerShare, BookValuePerShare, PERatio, PriceToBook, current_ratio, TotalDebtToEquity, ReturnOnEquity, DividendYield, MarketCapitalization, Sector, Beta, ChangePercentYTD
The Graham Number itself is a one-liner once you have EPS and BVPS in their own columns:
=IFERROR(SQRT(22.5 * EPS_cell * BVPS_cell), 0)
The margin of safety is another one-liner:
=IFERROR((Graham_Number_cell - Price_cell) / Price_cell, 0)
QM_Last returns 15-minute delayed quotes on the Standard plan, which is fine for a valuation screen. On Mac or Excel for the web, the Microsoft 365 add-in uses the mxls. prefix (for example =mxls.EarningsPerShare("JNJ")); check platform availability for each function on the formulas page.
The IFERROR wrapper protects against negative EPS or BVPS, which would otherwise return a square-root domain error. Disqualified names get a zero in those columns and drop to the bottom of the value score ranking automatically.
For a more complete defensive screen, wrap the qualifying check in an AND formula that mirrors Graham's full checklist:
=AND(EPS>0, BVPS>0, PE<15, PB<1.5, CR>2, DE<1, MoS>0)
You can adjust the constants to match your scenario. The seven scenarios in the workbook each use a different combination of those caps and floors.
What about industries Graham would not have screened?
Banks, insurance companies, and pure REITs need different valuation lenses. For banks, book value matters but the price to book ratio interacts heavily with regulatory capital ratios and loan loss reserves - a price to tangible book ratio with a separate capital ratio overlay is more appropriate. For insurers, embedded value and combined ratio carry more weight than the Graham Number. For REITs, funds from operations replaces earnings per share entirely. The template excludes these names by design so the Graham Number stays meaningful.
Pure growth names with high R&D capitalization and very thin book values (think modern software companies) also struggle on Graham's grid. That does not mean they are overvalued in any absolute sense; it means Graham's formula does not capture their economics. The template includes a few such names in the universe so users can see why they screen as "Disqualified" or "Premium" rather than removing them silently.
Limitations of the Graham Number screen
The Graham Number has real limitations and they are worth naming explicitly so the screen is used as one input rather than the only one:
- It uses trailing data. EPS is trailing 12 months; book value is the most recent balance sheet date. Forward expectations are not part of the formula. A name with deteriorating earnings will look cheaper than it should until the trailing data catches up.
- It does not adjust for capital intensity. A capital-light business and a capital-heavy business with the same EPS and BVPS will show the same Graham Number, even though the capital-light business is worth more.
- It penalizes intangible-heavy business models. Brand value, R&D pipelines, customer relationships, network effects - none of these sit on the balance sheet. Companies whose moats are mostly intangible look expensive on price to book and so will tend to fail Graham's screen even when they are excellent businesses.
- It treats every dollar of earnings the same. Quality of earnings, accruals, working capital changes, and one-time items all get rolled into a single trailing EPS number.
The template addresses these limitations through layered views rather than ignoring them. The Comparison Matrix lets you put a Graham-style cheap name side by side with a quality-priced mega cap so the trade-off is visible. The scenario set includes Growth at Reasonable Price and Quality + Value so users can move beyond strict Graham defensiveness when the situation warrants. The methodology sheet states the limitations directly.
Download the Graham Number Screener Excel template
The Graham Number screener template is free to download. It includes the cover page, KPI dashboard, scenario analysis, stress test, sector heatmap, portfolio sleeve, and methodology sheet.
Download the templates:
- - Pre-filled with current data, every cell carries a comment showing the underlying MarketXLS formula
- - Live formulas that refresh on demand
Open in Microsoft Excel 2016 or later. The Template version requires the MarketXLS add-in to be installed and signed in so the live formulas refresh. The Sample version is fully functional without any add-in.
Frequently asked questions
What is the Graham Number formula?
The Graham Number is calculated as the square root of (22.5 times earnings per share times book value per share). It represents the maximum price a defensive investor should pay for a stock according to Benjamin Graham. The 22.5 multiplier is derived from Graham's preferred maximum PE ratio of 15 multiplied by his preferred maximum price-to-book ratio of 1.5. In Excel notation: =SQRT(22.5 * EPS * BVPS).
Is the Graham Number still relevant today?
The Graham Number remains useful as one input among several. It is most relevant for mature, dividend-paying companies with positive earnings and meaningful tangible book value. It is least relevant for growth companies with significant intangible assets, financial institutions, and REITs. As a screening filter in a value-tilted portfolio it still earns its place; as a sole valuation method it does not.
What is a good margin of safety for the Graham Number?
There is no single correct threshold. Graham's idea of buying at about two-thirds of value means a price one-third below the Graham Number, which is a 50 percent reading on this template's formula ((Graham Number minus price) divided by price). Lower thresholds admit more stocks. Set the threshold yourself in the Inputs sheet.
Why do some companies have no Graham Number?
A company has no valid Graham Number when earnings per share or book value per share is negative. The formula requires positive values for both inputs because the square root of a negative number is not a real number (if both are negative, the product is positive but the result is meaningless). The template shows these names as zero or Disqualified. Companies losing money or carrying negative tangible equity (often from aggressive buybacks or repeated write-downs) get disqualified by the math automatically.
Can I use the Graham Number for tech stocks?
You can run the formula on any stock with positive EPS and positive book value, including tech stocks. However, most modern tech companies will fail Graham's defensive screen because their book values are low relative to market cap. This is a known limitation of book-value-based valuation methods for intangible-heavy businesses. The template includes several tech names in the universe so you can see exactly how the screen treats them.
How is the value score in the template calculated?
The value score is a 0-100 composite that blends five components: margin of safety to Graham Number (40 percent weight), low PE ratio (20 percent), low price-to-book ratio (20 percent), high current ratio (10 percent), and low debt-to-equity (10 percent). Each component is normalized to a 0-100 sub-score using Graham's preferred bands. A score above 75 indicates strong alignment with defensive criteria; a score below 25 indicates a name unlikely to clear Graham's checks.
Does this template work without MarketXLS installed?
The Sample workbook works without MarketXLS - it ships with static values pre-filled and is fully functional for inspection and educational use. The Template workbook requires the MarketXLS add-in because every data cell is a live formula. To use the Template version, install and sign in to the MarketXLS add-in, then recalculate (F9). The formulas populate with the most recent EPS, book value, price, and other inputs.
The bottom line
A Graham Number screener does not predict returns. It checks each stock against a conservative valuation ceiling and shows which names trade below it and which do not. Use the scenario set to see how the universe changes when you tighten or relax the criteria. Use the stress test to find out which positions break first when earnings disappoint. Use the portfolio sleeve to size a Graham-style allocation against your own capital. Use the comparison matrix to put cheap names side by side with the premium-multiple alternatives so the trade-off is visible.
Visit marketxls.com to explore the full library of valuation, screening, options, and portfolio analytics formulas available inside Excel. Book a personalized walkthrough at marketxls.com/book-demo if you would like a guided tour of how MarketXLS supports advisor workflows, family office research desks, and self-directed value investors.
This template, and the analysis surrounding it, is educational. Nothing here is investment advice, a recommendation to buy or sell any security, or a forecast of future performance. Always consult a licensed financial professional before acting on any screening output.