Stock Drawdown Screener Excel: Max Drawdown and Recovery Dashboard (2026)

Stock drawdown screener Excel searches usually return a generic article telling you a drawdown is "a peak-to-trough decline" and a screenshot of someone else's spreadsheet. This guide ships a different answer: a premium June 2026 Excel template that turns drawdown into a living dashboard with KPI tiles, a sector drawdown heatmap, a 30-stock screener with conditional formatting, scenario analysis, a recovery strategy table, and a 25-year history of S&P 500 drawdowns with recovery times. Every cell in the live version is a verified MarketXLS formula. The sample with formula references and the live formula version are both linked below.

Drawdown is the most useful risk metric most retail investors ignore. Volatility and beta tell you how a stock wobbles. Drawdown tells you how far it falls and how long it stays down. The math of recovery is unforgiving: a 20% drawdown requires a 25% rally to get back to even, a 40% drawdown needs 67%, and a 50% drawdown requires the stock to double just to climb back to its prior peak. This template was built to make those numbers visible at a glance for every name in your watchlist and to let you stress-test a portfolio across seven drawdown scenarios.

Quick Look: Drawdown by the Numbers (June 2026)

Three things jump off that table. First, the headline average drawdown of -11.4% looks healthy, but the median tells a different story: most large caps are within 6% of their highs. The average is dragged down by a handful of deeply impaired names where the prior peak is a multi-year-old record. Second, drawdowns cluster by sector. Consumer staples names trade like a tight band 0 to 5% off their highs; consumer discretionary names sprawl across a 65 percentage point range. Third, the recovery math is doing real work in the background. Six stocks need a rally of 50% or more just to reclaim their prior peaks. The screener surfaces every one of these facts in three clicks.

Why Stock Drawdown Matters More Than Volatility

Volatility is symmetric. A stock with 30% annualized volatility is as likely to print a one-day rally as a one-day decline. Drawdown is asymmetric in two ways. First, it captures the path of returns, not just the dispersion. Two stocks with identical annualized volatility can have very different drawdown experiences if one trends and the other oscillates. Second, drawdown is the only risk number that maps directly to investor behavior. People do not panic-sell because volatility is elevated. People panic-sell because their position is down 30% from where they bought it.

The recovery math compounds this. A 10% drawdown needs an 11.1% rally to recover. A 25% drawdown needs 33.3%. A 50% drawdown needs 100%, and a 75% drawdown needs 300%. The relationship is hyperbolic, which is why preserving capital in deep drawdowns matters more than capturing every upside basis point. The template surfaces the recovery number directly: for every stock in the screener, you see both the current drawdown and the rally needed to get back to even.

There is a second reason drawdown matters in mid-2026: the gap between index drawdown and individual stock drawdown has rarely been wider. The S&P 500 itself is within 3% of its all-time high. The individual stocks inside the index are scattered across a 65 percentage point range. Two indexes that look identical on a price chart can mask very different underlying conditions. The drawdown screener exposes the dispersion the index hides.

How the Template Calculates Drawdown

Drawdown in this template is measured two ways for every stock. The headline number is drawdown from all-time high, calculated as (Current Price - All-Time High) / All-Time High. The all-time high is the highest closing price the security has recorded across its history, regardless of how long ago. The second number is drawdown from 52-week high, calculated the same way but using the highest closing price over the trailing 252 trading days.

The two measures often disagree, and the disagreement is informative. A stock can be at a 52-week high while still 50% below its all-time high. That tells you the security is mid-recovery: it has stopped making lower lows and is grinding back toward the previous peak. Other stocks are within 10% of their all-time high and within 2% of their 52-week high. That tells you the security is in a normal pullback inside an ongoing uptrend. A third group is far below both. That tells you a structural problem.

The recovery percentage column closes the loop. For every drawdown number, the template calculates the rally required to fully recover. The formula is -DD / (1 + DD), which is the exact algebraic identity that maps a percentage decline to its inverse percentage rally. A 20% drawdown maps to a 25% recovery; a 33% drawdown maps to a 50% recovery; a 50% drawdown maps to a 100% recovery. The hyperbolic shape of the recovery curve is the single most important fact about drawdowns, and it sits in column J of the Dashboard.

What the Template Does

The June 2026 stock drawdown screener Excel template ships ten working sheets plus a glossary. It exposes every assumption behind the headline numbers, lets you swap inputs in yellow-highlighted cells, and stress-tests a portfolio across seven drawdown scenarios. The template version pulls live MarketXLS data the moment you open the file; the sample version preserves the same design with comments showing exactly which formula generated each value.

The headline Dashboard sheet renders six KPI tiles across the top, a sector drawdown comparison table with conditional-formatted color scales, a bar chart of sector average drawdown, and a 30-stock screener sorted by drawdown depth with red-to-green heatmaps on every metric. Conditional formatting reads at a glance: deep drawdowns flag red, recovered names flag green, beta cells flag red when above 1.0. A second chart compares scenario-implied portfolio drawdowns. The Strategy sheet maps drawdown buckets to entry zones, target recovery levels, and historical time-to-recovery so you have a framework before you commit capital.

Quick Look: What is Inside the Template

Here is the full sheet-by-sheet walkthrough. The template was built so that anyone can open it, look at the Dashboard, and understand the drawdown picture inside two minutes.

• Cover - Branded cover page with title, version, "Data as of" stamp, table of contents, and a marketxls.com link. Gridlines are hidden so it reads like a product cover, not a worksheet.
• How To Use - Step-by-step tutorial covering eight onboarding steps from opening the file to drilling into individual names. Includes the full list of MarketXLS functions referenced anywhere in the workbook.
• Dashboard - The headline sheet. Six KPI tiles across the top show average drawdown from ATH, average drawdown from 52-week high, deepest drawdown in the universe, count of stocks in bear, count near highs, and total universe size. A sector drawdown comparison table sits below with three-color heatmaps. A bar chart visualizes sector average drawdown. A 30-stock screener at the bottom sorts every name from deepest drawdown to highest, with data bars on the recovery column and color scales on drawdown, beta, and 52-week distance.
• Inputs and Controls - Yellow-highlighted input cells for portfolio size, cash reserve percentage, max position size, recovery target filter, and a max drawdown threshold filter. A scenario dropdown selects between seven forward paths. A ten-slot watchlist lets you pin custom tickers. A sleeve allocation block lets you tune the weight of each drawdown bucket.
• Scenario Analysis - Seven forward paths from "Mild Pullback" to "Deep Bear" to "Recovery (V-Shape)". Each row carries the implied S&P 500 drawdown, the implied Mag 7 drawdown, the number of sectors in correction, and a one-line narrative. Conditional formatting paints the drawdowns red-to-green; an icon set flags the breadth of the decline. A bar chart compares the seven scenarios.
• Strategy - A recovery strategy table for each drawdown bucket. For each bucket you see the entry trigger, suggested position size, target recovery level, stop-loss anchor, average historical recovery time in months, and a note on how to approach that bucket. The five buckets map directly to the screener status column.
• Portfolio Allocation - Six sleeves sized to a default 100k portfolio: cash reserve, at-high momentum, near-high builds, correction sweet spot, bear bucket, and deep bear catalyst trades. A doughnut chart visualizes the mix. Each row references the Inputs sheet for portfolio size so you can change one cell and the whole sleeve resizes.
• Sector Comparison - Side-by-side sector table with eleven sectors and seven columns: name count, average drawdown, max drawdown, year-to-date return, average beta, and average dividend yield. Color scales make outliers obvious. A grouped bar chart layers average drawdown against year-to-date return for every sector.
• Historical Drawdowns - A 25-year history of S&P 500 drawdowns from the dot-com bust through 2025, with peak date, trough date, peak-to-trough percentage, recovery time in months, and the catalyst. Summary statistics at the bottom show the average and median drawdown and recovery time across all nine events.
• Methodology - One-page explainer covering universe selection, drawdown formulas, recovery percentage math, status classification thresholds, sector aggregation, beta definition, scenario design, and limitations. Designed so an audit trail is visible.
• Glossary and Disclaimer - Twelve term definitions plus a clearly flagged educational-use disclaimer.

Tab colors set per sheet so the workbook reads as a navigation tree: navy cover, gray instructional sheets, blue dashboard and comparison sheets, yellow input sheet, amber scenario sheet, green strategy and portfolio sheets. Gridlines are hidden on the Cover and Dashboard. Frozen panes are set everywhere. The Dashboard has a print area defined for clean landscape output.

Reading the Sector Drawdown Map

The most surprising sheet for most users is the Sector Comparison map. The intuition that "the market is at a high" breaks down once you slice by sector. Consumer staples names are tightly clustered within 5% of their highs, with an average drawdown of -3.8%. Consumer discretionary names span a 65 percentage point range, with an average drawdown of -18.3%. That dispersion has nothing to do with the index level and everything to do with what is happening inside specific industries.

Energy is interesting in mid-2026. Crude prices have been range-bound for two quarters, and the integrated majors have given back some of their 2024 gains. Average drawdown for energy is -19.8%, with the worst names down 25% from their 2025 peaks. Whether that represents opportunity or a bearish signal depends on your view of forward energy demand and on the credibility of capital discipline among the majors.

Healthcare carries the second-deepest average drawdown thanks to insurer and pharma weakness. UnitedHealth alone is down 29% from its January 2026 high after several quarters of cost pressure. Eli Lilly remains 22% below its 2025 GLP-1 peak as competitive entrants have eroded the previous monopoly narrative. These are the kinds of names where the recovery percentage column tells you something the headline drawdown cannot: a stock that is 30% below the ATH needs a 43% rally to climb back, and the time horizon matters as much as the catalyst.

Technology, the index leader, hides real dispersion under the surface. NVDA, MSFT, and AVGO are within 7% of their all-time highs. AMD is 37% below its March 2025 peak. ADBE is 35% off its 2021 peak and still has not recovered. The cap-weighted Technology bucket looks healthy; the equal-weighted view shows a sector running on a narrow group of leaders. The screener exposes this by listing every name with its individual drawdown rather than rolling up to a sector average that hides the bottom decile.

Building Your Own Drawdown Screener With MarketXLS

The template uses about a dozen MarketXLS functions to pull every value in the workbook. Here are the formulas that do the heavy lifting and what each one returns.

=QM_Last("AAPL")                          → Current price
=Name("AAPL")                             → Company name
=Sector("AAPL")                           → GICS sector
=Industry("AAPL")                         → GICS industry
=FiftyTwoWeekHigh("AAPL")                 → 52-week high price
=FiftyTwoWeekLow("AAPL")                  → 52-week low price
=PercentBelowFiftyTwoWeekHigh("AAPL")     → Distance below 52-week high
=PercentAboveFiftyTwoWeekLow("AAPL")      → Distance above 52-week low
=MarketCapitalization("AAPL")             → Market cap in USD
=PERatio("AAPL")                          → Trailing P/E
=DividendYield("AAPL")                    → Dividend yield (decimal)
=Beta("AAPL")                             → Beta vs S&P 500
=StockReturnOneYear("AAPL","NoDividends") → Trailing 1-year price return
=StockReturnThreeMonths("AAPL","NoDividends") → Trailing 3-month price return
=RSI("AAPL")                              → 14-day RSI
=SimpleMovingAverage("AAPL",50)           → 50-day SMA
=SimpleMovingAverage("AAPL",200)          → 200-day SMA

The drawdown calculation itself is plain Excel. Once you have the current price and the 52-week high in two cells, drawdown from the 52-week high is just =(Price - 52WH) / 52WH. The recovery percentage is =-DD / (1 + DD). Drawdown from the all-time high requires tracking the historical peak. MarketXLS does not natively return an all-time high; the cleanest approach is to use QM_GetHistory to pull the full price history and then take MAX on the resulting array. The template stores ATH as a static value with a comment explaining how to refresh it.

The MarketXLS formulas auto-refresh as soon as the workbook opens with the add-in active. Combined with Excel's built-in conditional formatting engine, this means a single workbook becomes a live drawdown radar that updates in real time. If you only run one MarketXLS formula in your portfolio routine, =PercentBelowFiftyTwoWeekHigh() is a good candidate because it returns the most useful single number for a drawdown view.

Drawdown Buckets and the Strategy Table

The Strategy sheet does something the screener alone cannot: it gives you a framework for what to actually do with the drawdown information. The five buckets map directly to the status column on the Dashboard, and each carries its own playbook based on historical drawdown data.

For names at or near the all-time high, the template suggests small starter positions in case of breakout, with stops 8% below entry. This is the smallest sleeve because the asymmetry is wrong: the upside from a recent high is capped by the cycle, but the downside is wide open if the recent high turns out to be the cycle peak. Limit allocation to 1% per name and keep total sleeve weight under 5%.

The correction bucket (10% to 20% off the high) is the sweet spot. Historical data 2000 through 2024 shows that the median time to recovery from a 15% drawdown on a quality large-cap is about four months. The strategy table recommends 3% position size, target recovery to the prior peak, stop at -25%. Build in over two to three weeks. This is where the screener earns its keep: pulling up six or eight names in this bucket gives you a real opportunity set.

The bear bucket (20% to 30% off the high) requires patience. Historical recovery is six to twelve months. The strategy table recommends 4% position size but with the caveat that you should leg in over four to six weeks rather than buying the whole position at once. Stop is at -40%. This is the bucket where averaging down is acceptable; in the deep bear bucket, averaging down has been historically dangerous.

The deep bear bucket (more than 30% off the high) is the catalyst-only zone. Historical time to recovery is 12 to 24 months, and a meaningful subset of names never recover. The strategy table caps position size at 2.5% and requires a specific recovery thesis: management change, balance sheet repair, divestiture, catalyst-driven re-rating. Without a thesis, deep drawdowns are noise.

Scenario Analysis: What Happens If Drawdown Gets Worse

The scenario analysis sheet provides seven forward paths over the next twelve months. The base case assumes the index stays close to current levels with individual stocks scattered across a healthy range. The four bearish scenarios scale up from a mild 5% pullback to a 42% deep bear. The two bullish scenarios cover a V-shaped recovery and a continuation of the current grind.

The most useful comparison in the scenario table is the S&P 500 drawdown versus the Mag 7 drawdown. Across all seven scenarios, the Mag 7 drawdown is 1.4 to 1.6 times the index drawdown. This is the concentration math working in reverse: when the index moves down 10%, the seven largest names move down 15% on average because they carry both higher beta and concentrated single-name risk. In a deep bear scenario the multiplier widens because mega-cap multiple compression typically lags the broader market.

A self-directed investor benchmarked to SPY should care because the scenario analysis quantifies how much of their portfolio's downside path is driven by the top seven names. In the recession-bear scenario, the headline -28% S&P 500 drawdown breaks down as roughly -42% on the Mag 7 portion (38% of the index) and -19% on the rest (62% of the index). Knowing the decomposition lets you decide whether to hedge the concentration directly or simply trim it.

How This Maps to Portfolio Allocation

The Portfolio Allocation sheet takes the scenario-aware sleeve weights and applies them to a 100k portfolio (configurable on the Inputs sheet). The default mix holds 20% cash, with the remaining 80% spread across five drawdown buckets weighted toward the correction and bear sleeves. The 20% cash position is doing double duty: it cushions the portfolio against a deeper drawdown and provides dry powder for averaging down on quality names that fall through the bear threshold.

Why bias toward the middle buckets? Historical data shows that the best risk-adjusted returns from buying drawdowns come from the 15% to 25% off-ATH range. Stocks bought at the very top tend to keep falling; stocks bought after a 50% drawdown are often value traps with broken business models. The middle buckets capture quality at a discount without the catalyst dependence of the deep bear bucket.

The dollar sizing flows from a single cell on the Inputs sheet. Change portfolio size from 100k to 250k and every dollar amount on the Portfolio Allocation sheet updates automatically. The doughnut chart re-renders to reflect the new weights. This is the kind of design choice that lifts a template from "useful" to "presentation-ready" - one input, the entire workbook updates.

Frequently Asked Questions

What is a stock drawdown screener Excel template?

A stock drawdown screener Excel template is a spreadsheet that ranks stocks by their distance from peak prices and surfaces the math required for full recovery. The premium June 2026 version downloadable from this page tracks 30 large-cap U.S. stocks across eleven sectors, calculates drawdown from both the all-time high and the 52-week high, computes the recovery percentage needed, and color-codes every cell with conditional formatting so deep drawdowns and recovered names jump off the screen.

How is stock drawdown calculated in Excel?

Stock drawdown is calculated as (Current Price minus Peak Price) divided by Peak Price. In Excel, if the current price is in cell B2 and the all-time high is in C2, the formula is =(B2-C2)/C2 and the result is always negative or zero. The recovery percentage required to reach the prior peak is calculated as =-DD/(1+DD), which translates a percentage decline into the inverse percentage rally needed. The template handles both calculations automatically for every stock in the screener.

Where does MarketXLS get the 52-week high data?

The =FiftyTwoWeekHigh("TICKER") function pulls the highest closing price for the security over the trailing 252 trading days from MarketXLS's licensed data feed. For the all-time high, MarketXLS does not provide a single dedicated function. The cleanest approach is to use =QM_GetHistory("TICKER") to retrieve the full historical price series and apply MAX to the closing-price column. The template stores ATH as a manually tracked value with a comment showing how to refresh it.

Which stocks are deepest in drawdown in June 2026?

Across the 30-stock universe in this template, the deepest drawdowns in mid-June 2026 are concentrated in consumer discretionary and legacy industrial names. Tesla remains the deepest at -65% from its 2021 peak. Nike is -65% from its 2021 high. Boeing is -58% from its 2019 high. UnitedHealth is -29% from its January 2026 peak. The exact rankings update live in the template version as prices move; the screener is sorted from deepest drawdown to highest by default.

Can I add my own tickers to the drawdown screener?

Yes. The Inputs and Controls sheet has a ten-slot watchlist where you can pin custom tickers. The main screener on the Dashboard is populated from a fixed 30-stock universe defined in the workbook, but you can copy a row and replace the ticker reference with any U.S. or international symbol MarketXLS supports. The conditional formatting rules extend automatically. The template version uses live formulas, so any new ticker pulls real-time data the moment you open the file.

How long does a stock typically take to recover from a drawdown?

Historical S&P 500 data 2000 through 2024 shows median recovery times of about three months for a 10% drawdown, six months for a 20% drawdown, twelve months for a 30% drawdown, and eighteen to twenty-four months for a 40%-plus drawdown. The Historical Drawdowns sheet in the template documents nine specific events including the dot-com bust, the global financial crisis, the COVID-19 crash, and the 2022 bear market, with peak-to-trough percentages and recovery times. Individual stocks vary widely around these medians and a meaningful subset never fully recover.

Why does the template show different numbers for drawdown from ATH and 52-week high?

A stock can be at a 52-week high while still 40% below its all-time high if the ATH was set in a prior bull cycle. Tesla, Nike, Disney, and Adobe are all in this category in mid-2026. Drawdown from the 52-week high tells you about recent price action; drawdown from the ATH tells you about the cycle. The two measures together give a fuller picture: a stock that is at a 52-week high but 30% below ATH is in a mid-cycle recovery, whereas a stock 30% below both is in active distress.

What is the recovery percentage column on the screener?

The recovery percentage column shows the upside move required for the stock to climb back to its all-time high from the current price. The formula is =-DD/(1+DD), which is the algebraic identity that converts a percentage decline into the inverse percentage rally. A 20% drawdown maps to a 25% recovery; a 50% drawdown maps to a 100% recovery; a 75% drawdown maps to a 300% recovery. The hyperbolic shape of the recovery curve is the single most important fact about drawdowns and the reason capital preservation matters more than capturing every upside basis point.

Download the templates

• - Pre-filled with mid-June 2026 data and formula comments on every cell so you can see exactly which MarketXLS function powers each value.
• - Live-updating MarketXLS formulas. Open in Excel with the MarketXLS add-in active, click Data then Refresh All, and every price, sector, beta, and 52-week distance recalculates.

Both templates ship with the same ten-sheet structure: branded cover, How To Use, Dashboard with KPI tiles and a 30-stock screener, Inputs and Controls with yellow input cells and scenario dropdown, Scenario Analysis with seven forward paths, Strategy with a five-bucket playbook, Portfolio Allocation with sleeve sizing and a doughnut chart, Sector Comparison with an embedded bar chart, Historical Drawdowns with summary statistics, Methodology, and Glossary with disclaimer.

The Bottom Line

Drawdown is the most important risk metric in equity investing and the one most retail screeners ignore. A stock down 30% from its peak is not the same risk profile as a stock at a fresh high, even if both carry the same beta or volatility. The path of returns matters, the recovery math is unforgiving, and the dispersion across stocks inside the index is usually much wider than the index level suggests.

The premium June 2026 stock drawdown screener Excel template ships a working dashboard for tracking 30 large-cap names across every sector, with both the all-time high and 52-week high views, a recovery percentage column, a seven-scenario stress test, a drawdown-bucket allocation framework, and a 25-year history of S&P 500 drawdowns with recovery times. It is designed to be presentation-ready when you open it, configurable through one Inputs sheet, and refreshable in one click via MarketXLS live formulas.

For self-directed investors and advisors who want a structured view of drawdown risk inside their watchlist, the template removes the spreadsheet plumbing from the workflow. Pull live prices, see distance from peak, get the recovery math, and run scenarios all in one place. Sign up for MarketXLS to use the live formula version, or book a demo to see how the same approach scales across hundreds of names.