Magic Formula Screener Excel: 2026 ROC and Earnings Yield Dashboard

This free Excel template runs Joel Greenblatt's Magic Formula on a 30-stock universe: it ranks each stock on Return on Capital (quality) and Earnings Yield (value), adds the two ranks, and lists the 10 lowest combined ranks as a watchlist. MarketXLS formulas fill both factors, using =ReturnOnInvestedCapitalOneYear as the Return on Capital proxy and 1/EnterpriseValueToEBITDA as the Earnings Yield proxy, so the ranks update when the data refreshes. Four blend modes (Pure Magic, Quality Tilt, Value Tilt, Defensive) let you test how sensitive the top 10 is to the factor weights.

See it in actionMarketXLS video

The full MarketXLS demo

Watch full demoBook a demo

Try it in your own spreadsheet

Get the free MarketXLS sample workbook (.xlsx).

Email address

Download free workbook

No account needed. MarketXLS live data requires a paid plan. Preview workbook

Explore more videos +

IntroductionHistorical DataOptions DataUse-cases

MarketXLS access: Downloading a workbook does not include live data access. Refreshing MarketXLS formulas requires a paid MarketXLS subscription.

The workbook has eleven sheets, including a dashboard with KPI tiles and charts, an inputs sheet, a scatter map, and a methodology sheet. A static sample (with formula references shown as cell comments) and the live MarketXLS formula version are linked at the end of this post.

Magic Formula at a glance - the two-factor scorecard

The Magic Formula scores each stock on two factors, ranks the universe on each, sums the two ranks, and treats the lowest combined rank as the best:

The whole screen turns on those four numbers per stock. Everything else in the dashboard (KPI tiles, scatter map, sector exposure, scenario blends, top-10 watchlist, portfolio sleeve) is a different way of looking at the same scorecard.

What the Magic Formula is, and why a dashboard

Joel Greenblatt published the Magic Formula in 2005 in The Little Book That Beats the Market. The idea was deliberately spartan: two factors, ranked, summed, held in a diversified basket. No discounted cash flow models. No qualitative scoring. No market timing. The factors are these:

1. Return on Capital. Greenblatt defines this as EBIT divided by (Net Working Capital + Net Fixed Assets). It is a profitability measure on the tangible operating capital a business actually puts to work. A high ROC means each dollar of working capital and fixed assets produces a lot of pre-tax operating income, which Greenblatt treats as a sign of a good business.
2. Earnings Yield. Greenblatt defines this as EBIT divided by Enterprise Value. It is the inverse of the EV/EBIT multiple. A high earnings yield means the business is throwing off a lot of pre-tax operating income relative to the all-in price of buying the whole enterprise (debt plus equity, net of cash). It is value, expressed as a yield, on the operating earnings rather than reported earnings.

The two factors point in opposite directions on most stocks. High ROC names tend to trade at premium multiples (which depresses Earnings Yield), and high Earnings Yield names tend to be cheap because their profitability is mediocre (which depresses ROC). The names that score well on both axes are the rare intersection: businesses earning high returns on tangible capital that the market is also pricing cheaply.

Three features make the Magic Formula practical for individual investors:

1. It is a transparent, rules-based screen. There is no judgment, no narrative, no qualitative tilt. You can audit every input and every rank.
2. It is sector agnostic by design. Concentrated lists are a feature, not a bug. When a sector simultaneously trades cheap and earns high ROC, the screen surfaces it.
3. It runs on broadly available data. ROIC and EV/EBITDA are reported on every large-cap stock by every major data provider. You do not need a private dataset to build the screen.

The screen is tedious to build by hand: a sortable universe, two factor columns, two ranks, a combined rank, filters, scoring, and a readable layout. In this template you set the universe, blend mode, and filter thresholds on the Inputs sheet, and the workbook recomputes ROIC, EV/EBITDA, ranks, scores, the top-10 watchlist, sector exposure, and scenario comparisons when the data refreshes.

Why run a Magic Formula refresh at mid-year

When index returns are driven by a handful of richly valued mega-caps, a two-factor screen does two useful things at once. It surfaces the high-quality compounders that have not yet been bid to premium multiples (the upper-right of the scatter map on the dashboard), and it forces a disciplined exit from names that are merely cheap without the underlying ROC to justify a position. The screen does not predict the next 12 months. It sorts the universe on two factors that have been rewarded over long historical periods, though not in every year.

Mid-year is a natural point to review factor exposures. If your portfolio core is a broad index fund, a small Magic Formula sleeve is one way to add a quality-value tilt while keeping the low-cost core. The Portfolio Construction sheet sizes that sleeve.

How the Magic Formula screener excel template works

The workbook ranks a 30-stock universe across two factors, computes a blended score, applies a set of filters, and surfaces a top 10 watchlist. The blend mode and filters are user-controlled. Everything else updates automatically.

Step 1 - the universe

The default universe is 30 US large-cap names spanning every GICS sector except Utilities and Real Estate (Greenblatt's original work explicitly excluded financials, utilities, and real estate because of book-value distortions; the dashboard keeps financials in but excludes the latter two for clarity). The universe is editable. Drop new tickers into the yellow ticker column on the Inputs sheet, and the rest of the workbook re-ranks live.

The 30-name choice is a deliberate trade-off. Greenblatt's original screen pulled from a 1,000-plus stock universe, but a 1,000-name dashboard is unreadable. With 30 names you can see every row at a glance, audit every input, and still get a meaningful rank distribution. Add to 50 or 100 if you want a deeper bench.

Step 2 - the two factors via MarketXLS

For each ticker the dashboard pulls two live values:

=ReturnOnInvestedCapitalOneYear("AAPL")   -> ROIC over trailing twelve months
=EnterpriseValueToEBITDA("AAPL")          -> EV / EBITDA multiple

Formula documentation: ReturnOnInvestedCapitalOneYear, EnterpriseValueToEBITDA

ROIC is the MarketXLS proxy for Greenblatt's Return on Capital. The two metrics are not identical. ROIC includes intangibles and goodwill in the denominator, where Greenblatt's formula excludes them. For most large-cap businesses the two ranks are highly correlated. For roll-up acquirers or businesses with heavy goodwill, the ranks can diverge by several positions. The Methodology sheet calls this out explicitly.

Earnings Yield is computed as 1 / EnterpriseValueToEBITDA. Greenblatt's original formula uses EBIT instead of EBITDA. The two differ only by depreciation and amortization. For asset-light businesses (software, payments, content) the difference is small. For capital-intensive businesses (telecom, utilities, heavy industrials) the EBITDA-based Earnings Yield is slightly flattering. The Methodology sheet documents the proxy choice and its limitations.

Step 3 - ranks and combined rank

Each name is ranked from 1 (best) to 30 (worst) on ROIC. Each name is ranked separately from 1 (best) to 30 (worst) on Earnings Yield. The combined rank is the sum of the two. The lowest combined rank wins.

A name that ranks 1 on ROIC and 30 on Earnings Yield (expensive compounder) gets a combined rank of 31. A name that ranks 30 on ROIC and 1 on Earnings Yield (deep value with weak profitability) also gets a combined rank of 31. The two are equivalent on the screen, even though they are very different stocks. The score column converts combined rank to a 0-100 scale where 100 is the best in the universe, for easier dashboard reading.

Step 4 - filters

Three filters run before the ranking:

Each filter is editable on the Inputs sheet. Names that fail any filter show a red Fail flag on the Dashboard but remain visible (so you can see what the screen is rejecting and why).

Step 5 - blend modes

The dashboard offers four blend modes via the Blend Mode dropdown on the Inputs sheet:

The Scenario Analysis sheet runs all four blends side by side so you can see how the top names shift. Names that appear in three of four blends carry higher conviction; names that appear only in a single blend are factor-sensitive.

MarketXLS implementation - the formulas

The workbook uses the MarketXLS functions below. On the Microsoft 365 add-in (Excel for Mac and the web), add the mxls. prefix:

=Name("AAPL")                              -> Company name
=QM_Last("AAPL")                           -> Latest price (on demand)
=MarketCapitalization("AAPL")              -> Market cap (USD)
=PERatio("AAPL")                           -> Trailing PE
=ReturnOnInvestedCapitalOneYear("AAPL")    -> Trailing twelve-month ROIC
=EnterpriseValueToEBITDA("AAPL")           -> EV/EBITDA, used inversely as EY
=TotalDebtToEquity("AAPL")                 -> Debt/Equity ratio
=OperatingMargin("AAPL")                   -> Operating margin
=DividendYield("AAPL")                     -> Trailing twelve month dividend yield
=Beta("AAPL")                              -> Beta vs market
=Sector("AAPL")                            -> GICS sector
=FiftyTwoWeekHigh("AAPL")                  -> 52-week high
=FiftyTwoWeekLow("AAPL")                   -> 52-week low

Formula documentation: Name, QM_Last, MarketCapitalization, PERatio, ReturnOnInvestedCapitalOneYear, EnterpriseValueToEBITDA, TotalDebtToEquity, OperatingMargin, DividendYield, Beta, Sector, FiftyTwoWeekHigh, FiftyTwoWeekLow

The Dashboard, Top 10 Watchlist, Portfolio Construction, Sector Exposure, and Scatter sheets all reference these primitives. Change a ticker on the Inputs sheet and every formula fetches the new value when Excel recalculates. Prices are 15-minute delayed on the Standard plan. To see the full library of MarketXLS data functions, browse the MarketXLS formulas page or the MarketXLS features overview.

What is inside the template

Eleven sheets, ordered as a product would be ordered. Open the workbook and the Dashboard sheet greets you first. Everything else supports it.

1. Cover - Branded title page with version, edition, data-as-of date, table of contents, MarketXLS attribution. Hidden gridlines, navy background with gold accents. Pure presentation, no data.
2. How To Use - Six-step tutorial. Step 1 sets up the universe. Step 2 sets blend mode and thresholds. Step 3 reads the Dashboard. Step 4 opens the Top 10 Watchlist. Step 5 stress tests with scenario blends. Step 6 builds the portfolio sleeve. Each step explains the inputs, the cells to edit, and what to expect on the downstream sheets.
3. Dashboard - The headline sheet. KPI tile row across the top (Universe Size, Median ROC, Median Earnings Yield, Top Decile Score, Pct Passing Filters, Yield vs 10Y), two embedded charts (ROC distribution bar, Earnings Yield distribution bar), and a fully ranked screener at the bottom with conditional formatting (color scales on ROC and EY, reverse color scales on rank columns, data bars on Score, traffic-light icons on the Pass/Fail column). Hidden gridlines, frozen panes, landscape print area.
4. Inputs and Universe - The control sheet. Yellow input cells with gold borders for Blend Mode, Minimum Market Cap, Maximum P/E, Maximum Debt/Equity, Portfolio Total, Names to Hold, Cash Buffer, Account Type. Below them, the editable 30-ticker universe with auto-populated company name and sector via Name and Sector formulas. Three dropdown form controls (Blend Mode, Account Type, etc) for click-not-type editing.
5. Scenario Analysis - Four-column comparison of Pure Magic, Quality Tilt, Value Tilt, and Defensive blends. Each column shows the top five names by combined rank plus the average top-10 PE, ROC, EY, Beta, and Dividend Yield for that blend. Conditional formatting on the numeric rows. A short interpretation block below tells you how to read the divergence.
6. Top 10 Watchlist - The short list. Ten names with the lowest combined rank, with quality and risk add-ons: price, ROC, Earnings Yield, P/E, Operating Margin, Dividend Yield, Beta, Market Cap, and 52-week range. Conditional formatting on every metric column. This is the page you would print and bring to a research review.
7. Portfolio Construction - Sizes each top-10 name at equal weight against the portfolio total set on the Inputs sheet. Cash buffer rounds out the allocation. Whole-share rounding by default; switch to fractional via the Trade Lot Rounding dropdown on Inputs. Three short notes explain Greenblatt's quarterly staggered entry rule.
8. Sector Exposure - Sector concentration check. Universe vs Top 10 by sector, with universe weight, top-10 weight, and a pie chart. Data bars on the top-10 weight column. A short interpretation block flags when a single sector exceeds 35 percent of the top 10 and what to do about it.
9. ROC vs EY Scatter - Two-axis visualization of the entire universe. Upper-right quadrant is the Magic Formula sweet spot. Upper-left is high-quality, expensive. Lower-right is cheap, lower quality. Lower-left is neither cheap nor profitable. The scatter chart is built natively in Excel from the on-sheet data; no image.
10. Methodology - Transparent walk-through of the screen: origin, factor definitions, MarketXLS proxy choices and their limitations, ranking math, blend mode formulas, filter logic, data sources, and known limitations (sector concentration, point-in-time bias, no transaction cost or tax model). Use this to audit, modify, or argue with the model.
11. Glossary and Disclaimer - Plain-English definitions of every term used in the workbook (ROC, ROIC, EY, EV, EV/EBITDA, EBITDA, Combined Rank, Score, Blend Mode, Beta, Debt/Equity, Drift Tolerance, Defensive Filter, Top 10 Watchlist), plus the educational disclaimer.

Every sheet has frozen panes, a "MarketXLS Functions Used in This Sheet" footer that lists the exact formulas with one-line descriptions, and a powered-by MarketXLS branded footer at the bottom. Cover and Dashboard sheets hide gridlines for a presentation finish.

How to use the screen in practice

The template supports four workflows, each driven by a handful of input cells:

Workflow 1 - Quarterly Magic Formula refresh. Open the workbook, confirm the universe matches your target sector mix, leave Blend Mode on Pure Magic, accept the default filters, and read the Top 10 Watchlist. Compare to last quarter's list. Names that have fallen off graduated. Names that have entered are the new buys. Greenblatt's original work staggers entries quarterly across a 12-month rolling cycle; the workbook supports the same cadence by letting you save snapshots.

Workflow 2 - Stress test before committing. Run the same universe through all four blend modes on the Scenario Analysis sheet. If the top three names appear in three of four blends, conviction is high. If the top names diverge sharply by blend, the result is factor-sensitive and you may want to wait for more confirmation or split the position.

Workflow 3 - Concentration audit. Open the Sector Exposure sheet. If a single sector exceeds 35 percent of the top 10, decide whether to apply a sector cap overlay or accept the concentration. Concentration can mean the screen sees one sector as cheap and high-quality at the same time, but it also concentrates risk.

Workflow 4 - Quadrant analysis. Open the ROC vs EY Scatter sheet. The upper-right quadrant (high ROC, high EY) is the sweet spot. Names that sit just inside that quadrant on each refresh are the closest thing the screen has to a long-term core. Names that bounce in and out of the quadrant by quarter are tradeable but require more vigilance.

Strengths and limitations - the honest read

The Magic Formula is a published, rules-based framework that has been tested by others since 2005. Greenblatt's book reports backtested annual returns above 20 percent for 1988-2004. Later independent tests have generally found smaller excess returns, with long stretches of underperformance. Backtests do not predict future results.

Three honest limitations:

1. Sector concentration. The screen is sector agnostic. Concentrated lists can result. The Sector Exposure sheet surfaces this so you can decide whether to overlay a cap.
2. Point-in-time data. ROIC and EV/EBITDA use trailing twelve month data. One-time events (asset sales, restructuring, accounting changes) can distort a single year's factor reading. The Methodology sheet flags this.
3. No backtest or transaction cost model. The template is educational. Greenblatt's original work assumed quarterly staggered entries, 12-month holds, and equal weight. Real-world frictions (commissions, bid-ask spreads, tax) reduce realized returns, especially for small accounts and short hold periods.

The template surfaces the factors. How you use them is your decision and should be reviewed against your own risk tolerance, tax situation, and time horizon. If you are managing money for clients, fold the screen into your investment process documentation and treat the top 10 as a research starting list, not a buy list.

Frequently Asked Questions

What is the Magic Formula? The Magic Formula is a two-factor quantitative stock screen published by Joel Greenblatt in 2005. It ranks stocks on Return on Capital (a profitability proxy) and Earnings Yield (a value proxy), then sums the ranks to produce a combined rank. Greenblatt's backtests showed a diversified basket of high-scoring names, held for multi-year periods, beating broad benchmarks in his test period; results since publication have been more mixed.

Why does the Magic Formula screener excel use ROIC instead of Greenblatt's Return on Capital? Greenblatt's exact ROC formula (EBIT divided by Net Working Capital + Net Fixed Assets) is not exposed as a standalone MarketXLS function. ROIC is the closest available proxy and uses the =ReturnOnInvestedCapitalOneYear("TICKER") function. The two metrics are highly correlated for the broad equity universe but differ for businesses with heavy intangibles or goodwill. The Methodology sheet inside the template documents the proxy choice and its impact.

Why does the workbook use EV/EBITDA instead of EV/EBIT for Earnings Yield? The workbook inverts the current =EnterpriseValueToEBITDA("TICKER") value to compute Earnings Yield. EBITDA differs from EBIT by depreciation and amortization, so the proxy slightly flatters capital-intensive businesses. If you want Greenblatt's EBIT-based definition, MarketXLS also has a historical EV/EBIT function, =hf_Enterprise_Value_over_EBIT("TICKER", "ly"), which you can invert instead.

Can I change the universe? Yes. The Inputs and Universe sheet has 30 editable yellow ticker cells. Add, remove, or replace tickers and every downstream sheet re-ranks live. For a deeper bench, add rows beneath the table and the formulas extend automatically when the screening universe is updated.

Which blend mode should I use? Pure Magic is the Greenblatt baseline and the right default. Use Quality Tilt when you want to bias toward consistent compounders at the cost of accepting higher multiples. Use Value Tilt when you want to bias toward cheap names at the cost of accepting lower-quality businesses. Use Defensive when you want to layer in a beta and leverage filter on top of the 50/50 ranking. The Scenario Analysis sheet shows the four side by side.

How many names should I hold? Greenblatt's original work used 20 to 30 names held in equal weight for 12 months with quarterly staggered entries. The default in the workbook is 10 names, which is a tighter, more concentrated sleeve suitable as an overlay on a broader portfolio. Increase Names to Hold on the Inputs sheet if you want a wider basket.

Does the workbook account for taxes and transaction costs? No. The template is educational and does not model commissions, bid-ask spreads, dividend taxes, or short-term capital gains taxes. Real-world returns will be lower than gross factor returns, especially for taxable accounts and short hold periods. For tax-aware routing, run the screen and pair it with separate account-by-account tax-lot tracking.

Is the Magic Formula still working? Independent tests since 2005 have generally found smaller excess returns than Greenblatt's original backtest, with periods of significant underperformance. No quantitative factor strategy works every year. The dashboard surfaces the factors transparently so you can audit them; whether the framework matches your investment horizon and risk tolerance is your decision.

The Bottom Line

This Magic Formula template turns a two-factor idea into a working Excel screen. The dashboard pulls ROIC and EV/EBITDA live for every ticker in a 30-stock universe, ranks them, sums the ranks, applies size, P/E, and leverage filters, and surfaces a top 10 watchlist with quality and risk overlays. Four blend modes (Pure Magic, Quality Tilt, Value Tilt, Defensive) let you stress test the screen against your own factor preferences. A scatter map visualizes the entire universe across the two factor axes. A sector exposure sheet flags concentration creep. A methodology sheet documents every proxy choice and limitation.

The Magic Formula is not a market timing tool. It is a disciplined, rules-based way to rotate a portfolio toward businesses that score well on quality and value simultaneously. Greenblatt's original framing assumed multi-year holding periods, quarterly staggered entries, and diversification across at least 20 names. The dashboard preserves that discipline while making the screen sortable, auditable, and presentable in a single workbook.

Download the templates:

• - Pre-filled with the 2026-05-30 snapshot. Every data cell carries a comment with the MarketXLS formula behind it.
• - Live MarketXLS formulas across every sheet. Edit the yellow ticker cells and the whole workbook re-ranks.

To run the live version you need the MarketXLS add-in in Excel (see pricing), or you can book a demo. For a deeper read on the original framework, see our prior post on Joel Greenblatt's Magic Formula investing screener and the related Piotroski F-Score template for a complementary balance-sheet quality screen.

This template is for educational purposes only and does not constitute investment advice. Past performance does not guarantee future results. Always perform your own independent due diligence and review the limitations laid out on the Methodology sheet.