Dividend Yield: How to Find, Calculate, and Screen for High-Yield Stocks in Google Sheets and Excel (2026)

Dividend Yield is one of the most important metrics for income-focused investors. It tells you how much cash income you can expect for every dollar invested in a stock, expressed as a percentage. Whether you are building a retirement portfolio, generating passive income, or comparing investment opportunities, understanding dividend yield — and knowing how to calculate and screen for it efficiently — is essential.

This guide covers everything about dividend yield: what it is, how to calculate it manually and in Excel, what constitutes a "good" dividend yield, how to screen for high-yield stocks using MarketXLS, the relationship between yield and stock price, common traps to avoid, and how to build a complete dividend analysis dashboard in your spreadsheet.

What Is Dividend Yield?

Dividend yield is a financial ratio that measures the annual dividend income relative to the current stock price. The formula is straightforward:

Dividend Yield = (Annual Dividends Per Share ÷ Current Stock Price) × 100

For example, if a company pays $4.00 per share in annual dividends and its stock trades at $100, the dividend yield is 4.0%.

Dividend yield is expressed as a percentage and provides a quick way to compare the income-generating potential of different stocks. A stock with a 5% yield pays more income per dollar invested than a stock with a 2% yield, all else being equal.

Key Points About Dividend Yield

The Inverse Relationship Between Yield and Price

One of the most misunderstood aspects of dividend yield is its inverse relationship with stock price. Since yield is calculated by dividing dividends by price, the yield automatically increases when the stock price drops — even if the dividend has not changed.

This means a very high dividend yield can sometimes be a warning sign rather than a buying opportunity. If a stock's yield suddenly jumps from 3% to 8%, it might be because the stock price has collapsed due to deteriorating business fundamentals, and the dividend may soon be cut. This is known as a yield trap.

How to Calculate Dividend Yield Manually

To calculate dividend yield yourself, you need two pieces of information:

1. Annual dividends per share — The total dividends paid per share over the past 12 months (or the forward annual dividend if declared)
2. Current stock price — The most recent trading price

Example Calculation

Suppose Company XYZ:

• Paid quarterly dividends of $0.75 per share (four payments in the last year)
• Annual dividend = $0.75 × 4 = $3.00
• Current stock price = $60.00

Dividend Yield = ($3.00 ÷ $60.00) × 100 = 5.0%

Trailing vs. Forward Dividend Yield

• Trailing dividend yield uses dividends actually paid over the past 12 months divided by the current price. This is backward-looking and factual.
• Forward dividend yield uses the declared or expected annual dividend divided by the current price. This is forward-looking and may not materialize if the company changes its dividend policy.

Most financial data providers, including MarketXLS, report the trailing dividend yield by default.

Calculating Dividend Yield in Excel With MarketXLS

MarketXLS provides dedicated functions that eliminate the need for manual calculations. You can pull dividend data for any stock directly into Excel with a single formula.

The =DividendYield() Function

The simplest way to get dividend yield in Excel:

=DividendYield("AAPL")

This returns the current trailing dividend yield for Apple as a percentage. No manual calculation needed — the function pulls live data and computes the yield automatically.

The =DividendPerShare() Function

To see the actual dollar amount of dividends paid per share:

=DividendPerShare("AAPL")

This returns the annual dividend per share, which you can use for further analysis or verification.

The =DividendFrequency() Function

Different companies pay dividends on different schedules. To check the payment frequency:

=DividendFrequency("AAPL")

This tells you whether the company pays dividends monthly, quarterly, semi-annually, or annually. Most U.S. companies pay quarterly, but some REITs and Canadian companies pay monthly.

The =Last() Function

To get the current stock price for manual yield calculations or other analysis:

=Last("AAPL")

Building a Complete Dividend Dashboard

Here is a practical Excel layout for analyzing dividend yield across multiple stocks:

This dashboard gives you a complete picture of dividend income across your portfolio and updates in real time as prices change.

Using Dividend Yield in Google Sheets

MarketXLS works as a Google Sheets add-on with the same function names as the Excel version. You use the exact same formulas:

=DividendYield("MSFT")
=DividendPerShare("MSFT")
=Ex_DividendDate("MSFT")
=PayoutRatio("MSFT")
=Last("MSFT")

Install the MarketXLS add-on from the Google Workspace Marketplace and access all 1,000+ financial functions directly in your browser. This is useful for collaborative portfolio analysis, sharing dividend dashboards with clients, or when you prefer Google Sheets over Excel.

The Google Sheets version provides the full MarketXLS function library, including dividend data, fundamental data, options, technical indicators, portfolio analytics, and stock screening. Learn more on the MarketXLS for Google Sheets page.

What Is a "Good" Dividend Yield?

There is no universal answer to what constitutes a good dividend yield — it depends on the sector, market conditions, and your investment goals. However, here are general benchmarks:

Dividend Yield Benchmarks by Category

The Sweet Spot

For most income investors, a yield between 2.5% and 5.0% from financially healthy companies represents the sweet spot — high enough to generate meaningful income but not so high that it signals underlying problems.

Screening for High-Yield Dividend Stocks

Screening for dividend stocks requires more than just sorting by yield. A comprehensive screen should consider multiple factors to avoid yield traps and identify sustainable dividend payers.

Key Screening Criteria

1. Dividend Yield — Minimum 2.5% (or your target threshold)
2. Dividend Per Share — Confirm the actual dollar amount is meaningful
3. Dividend Frequency — Quarterly or monthly preferred for consistent income
4. Payout Ratio — Below 75% for most sectors (below 90% for REITs and utilities)
5. Revenue Growth — Positive revenue growth supports future dividend increases
6. P/E Ratio — Reasonable valuation relative to sector peers
7. Market Capitalization — Larger companies tend to have more stable dividends

Building a Dividend Screen in Excel With MarketXLS

You can build a powerful dividend screen entirely in Excel:

// Column A: Ticker symbols (enter your list)
// Column B: Current price
=Last("KO")

// Column C: Dividend yield
=DividendYield("KO")

// Column D: Dividend per share
=DividendPerShare("KO")

// Column E: Dividend frequency
=DividendFrequency("KO")

// Column F: P/E Ratio
=PERatio("KO")

// Column G: Market cap
=MarketCapitalization("KO")

// Column H: Revenue
=Revenue("KO")

By entering a list of ticker symbols in Column A and dragging these formulas down, you can screen dozens or hundreds of stocks in seconds. Then use Excel's built-in sorting and filtering to identify the stocks that meet your criteria.

Interpreting Screen Results

When reviewing your dividend screen results:

• Very high yields (8%+) — Investigate carefully. The yield may be elevated because the stock price has dropped, and a dividend cut may be imminent.
• Yields below the sector average — May indicate a growth-oriented company that reinvests earnings rather than distributing them.
• Consistent dividend frequency — Quarterly dividends are the norm for U.S. stocks. Monthly dividends are a bonus for income investors.
• Low P/E with high yield — Could be a value opportunity or a value trap. Cross-reference with revenue and earnings trends.

Dividend Yield by Sector

Different sectors of the economy tend to have very different dividend yield profiles:

Sector Dividend Yield Comparison

Understanding sector norms helps you evaluate whether a particular stock's yield is attractive or concerning relative to its peers.

Common Dividend Yield Traps and How to Avoid Them

Trap 1: The Falling Knife Yield

A stock drops from $100 to $50, and the yield doubles from 3% to 6%. The high yield looks attractive, but the stock price decline suggests the business is struggling, and the dividend may soon be cut.

How to avoid: Always check why the yield is high. Look at recent earnings, revenue trends, and news. A declining stock price without a corresponding business problem may be a genuine opportunity, but a declining stock price with deteriorating fundamentals is a trap.

Trap 2: The One-Time Special Dividend

Some companies pay large one-time special dividends that temporarily inflate the trailing yield. The yield returns to normal (much lower) after the special dividend period rolls off.

How to avoid: Check the dividend payment history. Look for consistency in regular quarterly or monthly payments rather than one-time spikes.

Trap 3: The Unsustainable Payout Ratio

A company paying out 120% of its earnings as dividends is borrowing or depleting reserves to maintain its dividend. This is not sustainable.

How to avoid: Calculate the payout ratio (Dividends Per Share ÷ Earnings Per Share). A payout ratio above 80% for most sectors, or above 95% for REITs and utilities, is a warning sign.

Trap 4: The Cyclical Dividend

Companies in cyclical industries (energy, mining, materials) may have high yields during the peak of the business cycle when earnings are strong. As the cycle turns, earnings drop and dividends are cut.

How to avoid: Look at dividend history across full business cycles (5–10 years). Companies that maintained dividends through downturns are more reliable.

Dividend Yield vs. Total Return

Dividend yield is only one component of total return. Total return includes:

Total Return = Price Appreciation + Dividend Income

A stock with a 2% dividend yield and 10% price appreciation delivers a 12% total return — higher than a stock with a 6% dividend yield and 0% price appreciation (6% total return).

Income investors should not focus exclusively on yield at the expense of total return. A balanced approach considers both income and capital growth.

Dividend Growth vs. High Current Yield

For long-term investors, dividend growth investing often outperforms high current yield strategies because the compounding effect of rising dividends creates an increasing yield on cost over time.

Tax Considerations for Dividend Income

Dividend income is taxed differently depending on whether dividends are qualified or non-qualified:

• Qualified dividends — Taxed at the long-term capital gains rate (0%, 15%, or 20% depending on income). Most dividends from U.S. companies held for more than 60 days qualify.
• Non-qualified (ordinary) dividends — Taxed at your ordinary income tax rate, which can be significantly higher.

REIT dividends are generally taxed as ordinary income, which is one reason REIT yields are higher — investors demand a higher pre-tax yield to compensate for the less favorable tax treatment.

Note: Tax laws are complex and change frequently. Consult a qualified tax professional for advice specific to your situation.

Building a Dividend Income Portfolio in Excel

Here is a framework for building and tracking a dividend income portfolio using MarketXLS:

Portfolio Tracker Layout

// For each stock in your portfolio:
A1: Ticker (e.g., "JNJ")
B1: =Last("JNJ")                    // Current price
C1: =DividendYield("JNJ")           // Current yield
D1: =DividendPerShare("JNJ")        // Annual dividend per share
E1: =DividendFrequency("JNJ")       // Payment frequency
F1: 200                              // Shares owned (manual entry)
G1: =D1*F1                          // Annual dividend income
H1: =G1/12                          // Monthly dividend income
I1: =B1*F1                          // Position value
J1: =PERatio("JNJ")                 // Valuation check

By building this for your entire portfolio, you can instantly see:

• Total annual dividend income across all holdings
• Monthly income estimates
• Portfolio-weighted average yield
• Diversification across sectors and payment frequencies

Frequently Asked Questions

What is dividend yield and how is it calculated?

Dividend yield is a financial ratio that expresses annual dividend payments as a percentage of the current stock price. The formula is: Annual Dividends Per Share ÷ Current Stock Price × 100. For example, a stock paying $2 in annual dividends with a price of $50 has a 4% dividend yield. In Excel, MarketXLS calculates this automatically with =DividendYield("TICKER").

What is a good dividend yield for income investing?

A good dividend yield depends on the sector and your risk tolerance. Generally, yields between 2.5% and 5.0% from financially stable companies represent a healthy balance between income and safety. Yields above 6–8% should be investigated carefully, as they may signal business distress or an unsustainable payout ratio.

How often do companies pay dividends?

Most U.S. companies pay dividends quarterly (four times per year). Some REITs and Canadian companies pay monthly. A smaller number pay semi-annually or annually. You can check any stock's payment schedule using =DividendFrequency("TICKER") in MarketXLS.

Why does dividend yield go up when a stock price falls?

Dividend yield is calculated by dividing the annual dividend by the stock price. When the stock price decreases and the dividend stays the same, the ratio (yield) increases. This is a mathematical relationship, not necessarily a positive signal — a rising yield due to a falling stock price may indicate business problems.

Can I screen for high-yield dividend stocks in Excel?

Yes. Using MarketXLS, you can enter a list of ticker symbols and use functions like =DividendYield(), =DividendPerShare(), =DividendFrequency(), =PERatio(), and =MarketCapitalization() to build a complete screening spreadsheet. Then use Excel's sort and filter features to identify stocks meeting your yield criteria.

What is the difference between dividend yield and dividend per share?

Dividend yield is a percentage that shows the return relative to the stock price. Dividend per share is the actual dollar amount paid per share annually. A stock at $200 paying $4 per share has a 2% yield, while a stock at $40 paying $2 per share has a 5% yield — the second stock has a higher yield despite paying fewer dollars per share.

Getting Started With Dividend Analysis in MarketXLS

MarketXLS gives income investors everything they need to find, analyze, and track dividend stocks in Excel and Google Sheets. With functions like =DividendYield(), =DividendPerShare(), =DividendFrequency(), and =Last(), you can build custom screening tools, portfolio trackers, and income calculators tailored to your investment strategy.

To explore the full range of dividend analysis tools available, visit the MarketXLS and find the plan that fits your investing needs.

Conclusion

Dividend Yield is a fundamental metric for income investors, but using it effectively requires more than just sorting stocks by yield percentage. By understanding the formula, recognizing yield traps, screening across multiple criteria, and building dynamic spreadsheets with real-time data, you can construct a dividend portfolio that generates reliable income while avoiding common pitfalls. Whether you use Excel or Google Sheets, MarketXLS provides the tools to make dividend analysis fast, accurate, and comprehensive.

Disclaimer

None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person. The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein. The article is written to help users collect the required information from various sources deemed to be an authority in their content. The trademarks, if any, are the property of their owners, and no representations are made.