Yield Trap Screener Excel June 2026: Spot Dangerous High-Dividend Stocks

A yield trap screener flags high-dividend stocks whose yield is high mainly because the share price fell and the dividend may be cut. This free Excel template runs six red-flag tests on each stock (payout ratio above 90% of net income, weak cash coverage, free cash flow yield below the dividend yield, high debt to equity, low interest coverage, and a large drawdown from the 52-week high) with MarketXLS formulas and combines them into a Trap Risk Score from 0 to 100. A high score is a warning to research further, not a prediction of a cut. It was built in June 2026 and is educational, not investment advice.

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

Download the templates:

• - Pre-filled with current data as of May 31, 2026
• - Live-updating formulas

The Six Red-Flag Tests at a Glance

The screener runs every name in your watchlist through six binary tests. The more tests a stock fails, the higher its Trap Risk Score (0-100). Higher scores cluster around the historical pattern of dividend cuts.

A name with three or more flags is worth a hard second look. Five or six flags lands in the "Likely Trap" verdict bucket on the dashboard.

Why Yield Trap Screening Matters Right Now

The macro setup in mid-2026 has pushed income investors into a tricky corner. Short-term Treasury yields have come off their peak as the Fed leaves room for cuts later in the year, but credit spreads in high-yield bonds remain wider than they were a cycle ago. That leaves equity dividends as one of the few places to chase incremental income without taking duration risk. The catch is that not all high yields are the same. A 9% yield on a stable consumer staple with rising free cash flow looks identical on a screen to a 9% yield on a leveraged industrial whose top line is shrinking. The first is a windfall. The second is a trap.

The classic trap pattern looks like this. A company's business deteriorates over twelve to eighteen months. Management keeps the dividend flat to defend the share price. The payout ratio creeps from 60% to 110%. Free cash flow no longer covers the dividend. Debt rises to make up the gap. The stock falls, which mechanically pushes the quoted yield higher. Income screens light the stock up as a "value opportunity." New money pours in. Then the dividend gets cut, the yield resets, and the price falls another 25%. The original investors lose both the income they bought and a chunk of capital.

The screener in this template is designed to catch that pattern before it completes. None of the six tests is predictive in isolation, but multiple flags together raise the historical odds of a cut considerably.

How to Use the Yield Trap Screener Template

The workbook ships with six sheets. The two you'll spend the most time on are the Main Dashboard and the Red Flag Matrix.

Main Dashboard

This is the workhorse sheet. Yellow input cells at the top let you set the thresholds for each test:

• Portfolio size
• Target annual income
• Stretched yield trigger (default 7%)
• Max payout ratio (default 90%)
• Min FCF coverage (default 1.0x)
• Max debt to equity (default 2.0)
• Min interest coverage (default 3.0x)
• Max % below 52-week high (default 30%)

Below the inputs, twenty tickers are pre-loaded across Communication Services, Consumer Staples, Healthcare, Energy, Real Estate, Industrials, Financials, and Technology. Replace any of them with your own watchlist. Every metric column is a live MarketXLS formula. The Flag Count column sums the binary tests for that row. The Trap Risk Score weights each component out of 20 points and tops out at 100. The Verdict column buckets each holding into Lower Risk, Watch, High Risk, or Likely Trap.

Red Flag Matrix

The Red Flag Matrix is the "show your work" version of the Trap Risk Score. Each column is one of the six tests. A 1 means the test failed for that ticker. The final two columns show Flag Count and Verdict. This view is useful when a name surprises you. If a stock you owned showed up with a high score, this sheet tells you exactly which tests it failed so you can dig into whether the failure is a temporary one (a one-time write-down depressing the payout ratio) or a structural problem (revenue declining for three straight years).

Scenario Analysis

The Scenario Analysis sheet stress-tests the portfolio. It assumes equal weight across the screener, then models the impact of a 30%, 50%, or 100% dividend cut on each holding. The portfolio-level summary at the bottom answers a single question: if every name with a Trap Risk Score above 70 cuts the dividend in half, how much annual income does the portfolio actually deliver versus the target?

For most income portfolios, the surprising result is how little aggregate income depends on the highest-yield names. A high-yielding name often sits at a smaller weight than expected because the share price has fallen, so the dollar loss from a cut is smaller than the headline number suggests. The opposite happens with steady mid-yielding compounders that have been increasing their position size through capital appreciation.

Portfolio Allocation

Three sizing methods sit side by side:

• Equal Weight is the baseline. Portfolio size divided by number of positions.
• Inverse-Risk sizes positions by 1 / (Trap Score + 1), normalized to the portfolio total. Lower-risk names get more capital, higher-risk names get less.
• Exclusion sets the allocation to zero for any "Likely Trap" verdict and equal-weights the survivors.

Inverse-Risk is the more useful of the three for advisors building an income sleeve. Exclusion is the cleaner choice for clients who want a strict screen with no exceptions.

Sector Comparison

AVERAGEIFS rolls each metric up by sector. Use it to see which corners of the dividend universe carry the most clustered red flags right now. In a typical cycle, telecom and tobacco show the highest payout ratios. Energy yields swing with crude prices and can flip from comfortable to stretched in a single quarter. REITs require careful handling because their TTM payout ratios overstate trap risk - AFFO is the right denominator, not GAAP net income.

The MarketXLS Formulas Behind Each Test

Every red flag in the template is a transparent formula. There is no hidden scoring. Here are the exact MarketXLS functions used in the dashboard.

Test 1: Payout Ratio

=DividendPayoutRatio("T")

Formula documentation: DividendPayoutRatio

This pulls the trailing twelve-month payout ratio as a percentage. Pair it with the yellow input cell for max payout in the dashboard. The screener flags any holding above 90% by default. You can tighten the threshold to 75% for a more conservative screen or loosen it to 110% if you want to capture only the most extreme cases.

Test 2: Cash Flow Coverage

=IFERROR(CashFlowPerShare("T")/DividendPerShare("T"), 0)

Formula documentation: CashFlowPerShare

Operating cash flow per share divided by dividend per share. A value of 1.0 means the company generates exactly enough operating cash to fund the dividend. Below 1.0 is a flag because the dividend is being funded from somewhere other than current operations (asset sales, new debt, balance sheet cash).

Test 3: FCF Yield vs Dividend Yield

=IFERROR(1/PriceToFreeCash("T")*100, 0)

This produces the free cash flow yield. The screener flags any holding where FCF yield is less than the quoted dividend yield. If a stock yields 8% on the dividend but only generates 4% in free cash flow yield, the math doesn't work for long.

Test 4: Debt to Equity

=TotalDebtToEquity("T")

Formula documentation: TotalDebtToEquity

Total debt divided by shareholder equity. The default threshold is 2.0. Heavy leverage doesn't kill a dividend by itself, but it removes the ability to ride out a soft quarter. Pair this with the interest coverage test.

Test 5: Interest Coverage

=InterestCoverage("T")

Formula documentation: InterestCoverage

EBIT divided by interest expense. The default minimum is 3.0x. Below that, a meaningful share of operating profit goes to lenders before shareholders see anything. This is the test that catches over-leveraged dividend payers whose income statement looks fine on the surface.

Test 6: Drawdown from 52-Week High

=PercentBelowFiftyTwoWeekHigh("T")

Formula documentation: PercentBelowFiftyTwoWeekHigh

The percentage decline from the 52-week high. The default flag triggers at 30% below. A deep drawdown doesn't predict a cut, but it usually means the market is pricing in a real concern. Combined with one of the other tests, it sharpens the screen.

Composite Trap Risk Score

The Trap Risk Score sums weighted components. Each test contributes 0 to 20 points. The full formula on the dashboard:

=ROUND(
  MAX(0, MIN(20, (F16-50)/4))         (payout - high is bad)
  + MAX(0, MIN(20, (2-I16)*10))       (cash coverage inverted)
  + MAX(0, MIN(20, (10-K16)*2))       (interest coverage inverted)
  + MAX(0, MIN(20, (L16-0.5)*8))      (debt/equity)
  + MAX(0, MIN(20, M16*0.4))          (drawdown)
, 0)

You can adjust the weights by editing the multipliers. The current weights distribute risk roughly evenly across payout, cash, balance sheet, and price action.

Reading the Sample Data

The static version of the template (sample file) is pre-filled with data as of May 31, 2026 across twenty higher-yield names: AT&T, Verizon, Altria, British American Tobacco, Kraft Heinz, Conagra, Pfizer, Bristol-Myers Squibb, GSK, Intel, IBM, Chevron, ExxonMobil, Enterprise Products, Energy Transfer, Realty Income, Kimco, 3M, Leggett & Platt, and KeyCorp.

A few notes on reading the sample:

• High yield alone does not produce a high Trap Score. Several of the higher-yielding energy MLPs show low Trap Scores because their FCF coverage and balance sheet metrics are healthy at current commodity prices.
• Cyclical industrials with elevated leverage and deep drawdowns from 2023 highs tend to top the Trap Score column. The flag matrix shows which specific tests they fail.
• The tobacco names show high payout ratios but strong free cash flow conversion. Whether that constitutes a trap depends on whether you view the secular volume decline as a slow grind or a step function. The screener doesn't try to make that call.

Plug your own watchlist into the template version and you will get a different distribution. The whole point of the workbook is to give you a transparent checklist you control.

Building Your Own Yield Trap Screener in Excel

If you'd rather wire this up yourself in a fresh workbook, here is the minimum set of MarketXLS formulas to recreate the core screener. Put a ticker in cell A2 and these formulas in cells B2:M2.

Once those twelve formulas populate, add a flag count column using the same threshold logic from the template, then build the Trap Risk Score as a weighted sum. Drag the row down for as many tickers as you want in the screener. That's the entire framework.

Where the Yield Trap Concept Came From

The label "yield trap" predates Excel. Income investors in the 1970s used the term to describe utility and railroad stocks whose quoted yields screened cheap but whose underlying earnings power was eroding. The pattern repeated through every cycle since. Newspaper publishers in the early 2000s. REITs with rising vacancy in 2008. Master limited partnerships with unsustainable distributions in 2014-2015. Department store chains and aging consumer brands through the 2020s. The structural cause is always the same: a fading business is using the dividend as a signal to the market that everything is fine, until management runs out of room.

What changed in the data era is the ability to screen for the pattern systematically. Twenty years ago, an investor would have read the 10-K, run a few coverage ratios by hand, and made a judgment call. Today the same checklist runs against thousands of names in seconds, but the underlying signal is identical: payout coverage matters more than yield, cash matters more than earnings, and balance sheet flexibility matters more than either.

Common Mistakes When Screening for Yield Traps

A few traps in the trap screening process itself, worth flagging.

Confusing GAAP payout with cash payout. Net income includes non-cash items like depreciation and impairment charges. A REIT or pipeline operator with a 200% GAAP payout ratio may be paying out 70% of cash flow. Use FCF-based coverage as the cross-check.

Ignoring sector context. Utilities routinely run 60-70% payout ratios because their cash flows are exceptionally stable. A 70% payout on a software company is alarming. A 70% payout on a regulated electric utility is normal. The sector comparison sheet in the template helps with this.

Overreacting to a single soft quarter. A one-time charge can push payout ratio above 100% on TTM data even when the underlying business is healthy. Check the trailing four quarters individually, not just the TTM aggregate.

Treating dividend history as a guarantee. A company that has paid a dividend for 30 years can still cut it. The history matters, but it doesn't insulate the holding from the same tests every other name has to pass.

Mistaking high yield for safety. Counterintuitively, the highest-yield names in any sector are the most likely to fail the screen. The market does not give away 9% yields without a reason.

Combining the Screener With Other MarketXLS Tools

The Yield Trap Screener pairs well with a few other workflows you may already be running in MarketXLS.

The Dividend Safety Screener is the mirror image of this template. It looks for the safest dividends rather than the most at-risk. Running both on the same watchlist gives you the full distribution from "Very Safe" to "Likely Trap."

The Dividend Aristocrats Dashboard tracks the names that have raised dividends for 25 consecutive years. The intersection of an Aristocrat with a high Trap Score is one of the most useful screens for income managers because it surfaces former blue chips that may be slipping.

The Free Cash Flow Yield Dashboard drills deeper into the FCF coverage test. If a name screens with a high Trap Score primarily because of cash flow issues, the FCF dashboard breaks down the source of the gap.

For tracking options-based income alongside dividend income, see the Covered Call Management Guide, which explains how to monitor covered-call positions and premium income.

FAQ: Yield Trap Screening in Excel

What is a yield trap in stocks? A yield trap is a stock whose quoted dividend yield is high relative to its peers, where the high yield reflects a falling share price and signals risk of a future dividend cut rather than a genuine income opportunity. The pattern is typically caused by deteriorating business fundamentals that management masks by maintaining the dividend longer than the cash flow can support.

How is the yield trap screener different from a dividend safety screener? A dividend safety screener finds the safest dividends by ranking high on coverage, low on debt, and long on dividend history. A yield trap screener does the opposite: it surfaces the dividends most at risk by ranking high on payout, low on cash coverage, and large on price drawdown. The two screens use overlapping metrics but opposite cutoffs. Running both on the same watchlist gives you the full distribution.

Can a stock with high Trap Risk Score still be a good investment? The score is a checklist signal, not a verdict. A stock can score high on the trap screen and still be a reasonable holding if the investor believes the fundamentals will inflect. The point of the screener is to make sure the investor is taking that view consciously rather than buying high yield without recognizing the underlying coverage risk. The template flags risk so you can decide if you accept it.

What MarketXLS function gives me dividend payout ratio? The function is =DividendPayoutRatio("TICKER"). It returns trailing twelve-month dividends paid divided by net income, expressed as a percentage. Pair it with =DividendYield("TICKER") and =CashFlowPerShare("TICKER") for the core trap-screening checklist.

Why does the screener use free cash flow yield instead of just dividend yield? Dividend yield by itself tells you what the company is paying. Free cash flow yield tells you what the company is generating. The gap between the two is the cash being returned that isn't coming from current operations. If FCF yield is consistently lower than dividend yield, the dividend is being funded by something else (debt, asset sales, balance sheet cash) and that arrangement has a finite life.

How often should I rerun the screener? Quarterly rebalancing matches the data cadence. The fundamental inputs (payout ratio, FCF, interest coverage) update with each earnings report. The price-based input (drawdown from 52W high) updates daily. For income portfolios that turn over slowly, running the screener once after each earnings season catches most of the meaningful changes.

Does a high Trap Risk Score guarantee a dividend cut? No screening framework guarantees an outcome. The Trap Risk Score is built around historical patterns associated with cuts, but stocks with high scores can keep paying their dividends for years before either reversing course or eventually cutting. The score is a probability signal, not a prediction.

The Bottom Line

Yield trap screener excel templates are useful because they force discipline into a process that is otherwise driven by headline yield numbers. The six tests in this workbook (payout ratio, cash coverage, FCF coverage, debt/equity, interest coverage, drawdown) are not novel. The template puts them in one place, with MarketXLS formulas behind every cell, so you can run a watchlist quickly and see which holdings carry the most red flags.

The template is a checklist, not a recommendation. It will flag stocks you may decide to own anyway. It will give green lights to stocks that subsequently cut. The point is to make every yield decision a conscious one.

Run your watchlist through the screener after the next earnings season. Compare your Trap Risk distribution to the sector averages on the comparison sheet. Adjust position sizes accordingly. That is the whole workflow.

Learn more about live dividend functions and screener-building inside Excel at MarketXLS. For a walkthrough of how the dividend functions integrate with portfolio reporting, book a demo.

Download the templates again:

• - Pre-filled with current data as of May 31, 2026
• - Live-updating formulas

This article is educational and does not constitute investment advice. Tickers shown are examples of how the screening framework applies. Past dividend payments do not guarantee future dividend payments.