Monthly Dividend Yield: Calculate and Track Your Income Using Excel in 2026

Monthly dividend yield is the annual dividend yield divided by 12: a stock yielding 4.8% a year has a monthly dividend yield of 0.4%. In Excel with MarketXLS, =DividendYield("AAPL")/12 returns it directly (DividendYield returns a decimal, so format the cell as a percentage). Because most US stocks pay quarterly, actual cash arrives in lumps, so this guide also shows how to map each holding's payments to calendar months and total your income for each month.

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

What Is Dividend Yield?

Dividend yield is the ratio of a company's annual dividend payment to its current stock price, expressed as a percentage. The basic formula is:

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

For example, if a company pays $4.00 per share annually and its stock trades at $100, the dividend yield is 4 percent. This tells you that for every $100 invested, you receive $4 per year in dividend income.

Dividend yield is a relative measure: it lets you compare the income potential of different stocks regardless of their share price. A $20 stock paying $1.00 per share (5 percent yield) generates more income per dollar invested than a $200 stock paying $4.00 per share (2 percent yield).

Why Monthly Dividend Yield Matters

Annual dividend yield is useful for comparison, but monthly dividend yield is what matters for practical income planning:

• Expense matching: Most bills arrive monthly. Knowing your monthly dividend income helps you determine whether your portfolio covers your expenses.
• Cash flow planning: Monthly yield helps you project income for the next 3, 6, or 12 months.
• Reinvestment timing: If you reinvest dividends, understanding monthly patterns helps you plan purchases.
• Portfolio construction: Building a portfolio that pays dividends every month requires understanding each holding's payment schedule.

The simplest way to calculate monthly dividend yield is to divide the annual yield by 12:

Monthly Dividend Yield = Annual Dividend Yield ÷ 12

However, this is an approximation. Most companies pay dividends quarterly, not monthly. Some pay semi-annually or annually. A small number pay monthly. Understanding the actual payment frequency and schedule is crucial for accurate income planning.

Understanding Dividend Payment Frequencies

Companies distribute dividends on different schedules:

For accurate monthly income projections, you need to know not just the yield but also the frequency and timing of each holding's dividend payments.

Calculating Monthly Dividend Yield in Excel with MarketXLS

MarketXLS provides several functions that make dividend analysis straightforward. Let us walk through each one.

Getting the Annual Dividend Yield

The DividendYield() function returns the trailing twelve-month dividend yield as a decimal (0.005 means 0.5%):

=DividendYield("AAPL")

Formula documentation: DividendYield

This returns Apple's current dividend yield based on the trailing twelve months of dividend payments divided by the current stock price. Use this as your starting point for monthly calculations.

Getting the Dividend Per Share

To know the actual dollar amount paid per share annually:

=DividendPerShare("AAPL")

Formula documentation: DividendPerShare

This returns the trailing twelve-month dividend per share. For Apple, this might return something like $1.00, meaning each share pays $1.00 per year in dividends.

Checking Dividend Frequency

To understand how often a company pays dividends:

=DividendFrequency("AAPL")

Formula documentation: DividendFrequency

This returns the payment frequency as text, such as "Quarterly" or "Monthly". To use it in math, convert it to a number of payments per year:

=SWITCH(DividendFrequency("AAPL"),"Monthly",12,"Quarterly",4,"Semi-Annual",2,"Annual",1)

Formula documentation: DividendFrequency

For most US stocks this returns 4. For monthly payers like Realty Income (O), it returns 12. SWITCH requires Excel 2019 or Microsoft 365.

Getting the Current Stock Price

To calculate yield yourself or verify the computed yield:

=Last("AAPL")

Formula documentation: Last

This returns the last traded price (15-minute delayed on the Standard plan). You can use it to check the dividend yield:

=DividendPerShare("AAPL") / Last("AAPL")

Formula documentation: DividendPerShare, Last

This should approximately match the value returned by =DividendYield("AAPL").

Computing Monthly Dividend Yield

Now, combine these functions to calculate the monthly dividend yield:

// Method 1: Simple division of annual yield
=DividendYield("AAPL") / 12

// Method 2: Using dividend per share and price
=(DividendPerShare("AAPL") / 12) / Last("AAPL")

// Method 3: Dollar amount per payment, per share
=DividendPerShare("AAPL") / SWITCH(DividendFrequency("AAPL"),"Monthly",12,"Quarterly",4,"Semi-Annual",2,"Annual",1)

Formula documentation: DividendYield, DividendPerShare, Last, DividendFrequency

Method 3 gives you the actual dollar amount you receive per payment (not per month), which is more accurate for quarterly payers. For a stock paying $1.00 annually with quarterly frequency, each payment is $0.25.

Building a Monthly Dividend Income Tracker

Let us build a comprehensive dividend income tracker that shows exactly how much you earn each month.

Step 1: Set Up Your Holdings

Create a table with your portfolio:

Step 2: Calculate Monthly Income

Add a column for estimated monthly income:

H2: =G2/12    // Monthly income from this holding

Copy down for all holdings. Then sum at the bottom:

H7: =SUM(H2:H6)    // Total estimated monthly income

Step 3: Calculate Portfolio-Level Monthly Yield

// Total portfolio value
I7: =SUMPRODUCT(B2:B6, C2:C6)

// Portfolio monthly yield
J7: =(H7 / I7) * 12    // Annualized portfolio yield (decimal)
K7: =J7 / 12                  // Monthly portfolio yield

Step 4: Add Payment Month Tracking

For more precise tracking, create a 12-column grid (one per month) and map each holding's payment months. Quarterly payers typically pay in a consistent pattern:

• January, April, July, October: Many financial stocks
• February, May, August, November: Many tech and healthcare stocks
• March, June, September, December: Many consumer staples

Monthly payers like Realty Income (O) pay every month. By mapping each holding to its payment months, you get an accurate picture of which months generate more or less income.

Strategies for Building Monthly Dividend Income

Strategy 1: The Monthly Payer Approach

The simplest way to ensure monthly income is to invest in stocks and funds that pay monthly dividends. REITs, business development companies (BDCs), and certain ETFs commonly pay monthly. This eliminates the need to juggle payment schedules.

Strategy 2: The Quarterly Stagger

Most US stocks pay quarterly. By selecting stocks from different quarterly cycles, you can create a portfolio that generates income every month:

Hold at least one stock from each cycle, and you receive dividend income every single month.

Strategy 3: The Dividend ETF Approach

Dividend-focused ETFs often hold dozens or hundreds of stocks across all payment cycles. The ETF itself may pay monthly or quarterly, but because its holdings have staggered payment dates, the ETF receives income continuously and distributes it on a regular schedule.

Strategy 4: The Yield Ladder

Diversify across yield levels:

• High yield (4%+): Provides maximum current income but may carry higher risk
• Medium yield (2-4%): Balances income with growth potential
• Low yield (<2%): Growth-oriented companies that may increase dividends over time

This approach provides income today while positioning for income growth tomorrow.

Factors That Affect Monthly Dividend Yield

Stock Price Changes

Dividend yield moves inversely with stock price. If a stock's price drops 20 percent and the dividend stays the same, the yield increases by 25 percent. This is why unusually high yields sometimes signal trouble: the price may have dropped due to deteriorating fundamentals.

Dividend Cuts or Increases

Companies can change their dividend at any time. A dividend cut directly reduces your monthly income. Conversely, annual dividend increases, common among "Dividend Aristocrats," grow your monthly cash flow.

Interest Rate Environment

When interest rates rise, bonds and savings accounts become more competitive with dividend stocks. This can push stock prices down (increasing yields) as income investors shift allocation. When rates fall, dividend stocks become relatively more attractive.

Tax Considerations

Qualified dividends are taxed at lower capital gains rates (0%, 15%, or 20% depending on income bracket). Non-qualified dividends are taxed as ordinary income. The after-tax monthly yield depends on your tax situation and the type of dividends your holdings pay. Consulting a tax professional is advisable for optimizing after-tax dividend income.

Currency Risk

For international dividend stocks, exchange rate fluctuations can affect the dollar amount you receive. A strong dollar reduces the value of foreign dividends when converted.

Comparing Dividend Yield Across Sectors

Different sectors offer different typical yield ranges:

Building a monthly income portfolio across sectors adds diversification while maintaining cash flow.

Using Historical Data to Analyze Dividend Trends

MarketXLS can pull a stock's price history, which shows how its yield has changed over time:

=GetHistory("JNJ", "2020-01-01", "2024-12-31", "Monthly")

Formula documentation: GetHistory

This pulls monthly price data for Johnson & Johnson. By combining this with known historical dividend amounts, you can chart how the yield has fluctuated and whether the stock's income potential has grown.

You can also track dividend growth patterns. Companies that consistently increase dividends, such as Dividend Aristocrats (25+ consecutive years of increases) and Dividend Kings (50+ years), have historically provided growing income, though past increases do not guarantee future ones.

Advanced Monthly Yield Calculations

Yield on Cost

Yield on cost measures your dividend yield based on your original purchase price, not the current market price:

// If you bought AAPL at $150 and it now pays $1.00/share annually
Yield on Cost = ($1.00 / $150) × 100 = 0.67%
Monthly Yield on Cost = 0.67% / 12 = 0.056%

For long-term holders of dividend growers, yield on cost can be significantly higher than the current market yield.

Forward Yield vs. Trailing Yield

• Trailing yield: Based on dividends actually paid over the past 12 months
• Forward yield: Based on the next 12 months of expected dividends

If a company recently increased its dividend, the forward yield will be higher than the trailing yield. MarketXLS DividendYield() uses trailing data; ForwardAnnualDividendYield() returns the forward yield.

Dividend Reinvestment Impact

If you reinvest dividends to buy additional shares, your monthly income grows through compounding:

// Year 1: 1,000 shares × $1.00 annual dividend = $1,000/year ($83.33/month)
// Reinvested at $50/share = 20 new shares
// Year 2: 1,020 shares × $1.05 annual dividend (5% increase) = $1,071/year ($89.25/month)

Over decades, dividend reinvestment can dramatically increase monthly income. Building this projection in Excel helps visualize long-term income growth.

Building a Dividend Calendar in Excel

A dividend calendar shows exactly which payments arrive in which months:

Step 1: List All Holdings with Payment Data

A1: Ticker    B1: Shares    C1: Div/Payment    D1: Frequency    E1: Pay Months
A2: O         B2: 200       C2: =DividendPerShare("O")/12    D2: =DividendFrequency("O")    E2: Monthly
A3: JNJ       B3: 150       C3: =DividendPerShare("JNJ")/4  D3: =DividendFrequency("JNJ")  E3: Mar,Jun,Sep,Dec
A4: AAPL      B4: 100       C4: =DividendPerShare("AAPL")/4 D4: =DividendFrequency("AAPL") E4: Feb,May,Aug,Nov

Formula documentation: DividendPerShare, DividendFrequency

Step 2: Create a 12-Month Grid

Add columns for January through December. For each holding, enter the payment amount in the months it pays:

• Monthly payers: amount in every column
• Quarterly payers: amount in their four payment months
• Semi-annual payers: amount in their two payment months

Step 3: Sum Each Month

At the bottom of each month column, sum all payments. This gives you the exact expected income for each month of the year.

MarketXLS also offers a pre-built Dividend Calendar Template that automates much of this process.

Common Mistakes in Dividend Yield Analysis

1. Chasing high yields: An unusually high yield often signals a price decline or potential dividend cut. Always investigate why a yield is high before investing.

2. Ignoring total return: Dividend yield is only part of total return. A stock with a 2 percent yield and 10 percent price appreciation outperforms a stock with a 5 percent yield and 0 percent appreciation.

3. Not accounting for taxes: Your effective monthly income depends on your tax bracket and whether dividends are qualified or non-qualified.

4. Assuming static dividends: Companies can cut or eliminate dividends at any time. Building a portfolio with dividend safety margins is important.

5. Overlooking payment timing: A quarterly payment in March does not help with February bills. Understanding exact payment dates matters for cash flow planning.

Frequently Asked Questions

How do I calculate monthly dividend yield from annual yield?

Divide the annual dividend yield by 12. For example, if a stock has a 4.8 percent annual dividend yield, the monthly dividend yield is 0.4 percent (4.8 ÷ 12). In MarketXLS, use =DividendYield("AAPL") / 12 to get this directly in Excel. This is an approximation: actual monthly cash flow depends on payment frequency and timing.

What is a good monthly dividend yield?

There is no universal answer, as it depends on your income needs and risk tolerance. A portfolio yielding 3-5 percent annually translates to 0.25-0.42 percent monthly. For a $500,000 portfolio at 4 percent annual yield, that is approximately $1,667 per month before taxes. Higher yields are available but often come with higher risk.

Which stocks pay monthly dividends?

Monthly dividend payers include many REITs (such as Realty Income), business development companies, closed-end funds, and certain ETFs. Most common stocks pay quarterly. Use =DividendFrequency("O") in MarketXLS to check a stock's payment frequency; it returns text such as "Monthly".

How can I track ex-dividend dates for my portfolio?

MarketXLS provides the =Ex_DividendDate() function to retrieve upcoming ex-dividend dates. You must own the stock before the ex-dividend date to receive the next payment. Building a tracker with this function helps you plan purchases and monitor payment eligibility.

Does reinvesting dividends increase my monthly yield?

Reinvesting dividends does not change the yield percentage, but it increases the number of shares you own, which increases total monthly dollar income. Over time, this compounding effect can significantly grow your monthly cash flow, especially when combined with companies that regularly increase their dividends.

How do interest rates affect dividend yields?

When interest rates rise, bond yields become more competitive with dividend stocks, which can push stock prices down and dividend yields up. When rates fall, dividend stocks become more attractive, potentially pushing prices up and yields down. The relationship is not mechanical: company fundamentals, growth prospects, and market sentiment also play roles.

Getting Started with Dividend Tracking in MarketXLS

Building a monthly dividend yield tracker in Excel takes just a few steps with MarketXLS:

1. Set up your portfolio: List tickers and share counts
2. Add MarketXLS formulas: Use =DividendYield(), =DividendPerShare(), =DividendFrequency(), and =Last() to populate key data
3. Calculate monthly income: Divide annual income by 12 or map payments to specific months
4. Create a calendar view: Track which months generate more or less income
5. Monitor and adjust: Refresh regularly to capture dividend changes and price movements

Visit MarketXLS Pricing to explore plans that fit your dividend investing workflow. Learn more at MarketXLS.

Conclusion

Monthly dividend yield analysis turns annual percentages into monthly income figures. By using MarketXLS functions like =DividendYield(), =DividendPerShare(), =DividendFrequency(), and =Last() directly in Excel, you can build comprehensive income trackers, dividend calendars, and portfolio optimization tools that show how much dividend income arrives in each month. This article is educational and not investment advice.