Dividend Yield in Google Sheets: Two Fixes

To calculate dividend yield in Google Sheets you have to build the number yourself. GOOGLEFINANCE returns price, eps, pe, market cap and a long list of other quote fields, and dividend yield is not one of them: =GOOGLEFINANCE("AAPL","yield") returns #N/A, and no other spelling of the word resolves either. Two workarounds hold up. Compute a trailing yield from a dividend column you maintain in the sheet, or type the declared forward rate into a cell and divide.

Why GOOGLEFINANCE has no dividend yield attribute

The function's attributes come in two families. There are live quote fields, fetched with two arguments: price, volume, pe, eps, marketcap, high52, changepct and about a dozen more. Then there are historical price fields, fetched with a date or a date range: open, high, low, close, volume. Dividend records belong to neither family. No attribute returns a payment or a payout history, which leaves the numerator of the yield to come from outside the function.

Dividend yield is annual dividends per share divided by price per share, written as a percent. The price half of that is one cell. The dividend half is the work. If the ratio itself is new to you, what dividend yield actually measures covers it before any spreadsheet mechanics, and how to calculate dividend yield walks the arithmetic.

Here is the raw material: every payment one household payer made over roughly the last three years. Each row is an ex-dividend date, the date on and after which a buyer of the stock does not receive the upcoming payment, next to the cash paid per share.

SELECT
    toString(ex_dividend_date)                     AS ex_date,
    formatDateTime(ex_dividend_date, '%b %e, %Y')  AS ex_date_label,
    round(toFloat64(payment), 4)                   AS cash_amount,
    round(toFloat64(payment) * 4, 4)               AS annualized_run_rate
FROM
(
    SELECT
        ex_dividend_date,
        max(cash_amount) AS payment
    FROM global_markets.stocks_dividends
    WHERE ticker = 'KO'
      AND ex_dividend_date >= today() - 1120
      AND ex_dividend_date <= today()
    GROUP BY ex_dividend_date
)
ORDER BY ex_dividend_date

Coca-Cola paid $0.46 per share on Sep 14, 2023 and $0.53 on Jun 15, 2026, 12 payments across the window. The second line on the chart is each payment multiplied by four: the annual rate implied if that quarter's payment repeated all year. It steps up once a year and sits flat in between. That stepping is where most spreadsheet yields go wrong. The full record sits in Coca-Cola's dividend history.

Two ways to get dividend yield in Google Sheets

Method one is a trailing yield built from your own column. Put ex-dividend dates in column A and cash per share in column B, one row per payment, then:

• Trailing twelve month total: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())
• Live price: =GOOGLEFINANCE("KO","price")
• Trailing yield, with the cell formatted as a percentage: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())/GOOGLEFINANCE("KO","price")
• Payment count in the same window, as a check: =COUNTIFS($A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())

EDATE(TODAY(),-12) is the same calendar day twelve months back, so the window rolls forward on its own. Format the yield cell as a percentage rather than multiplying by 100 inside the formula. A sheet that does both prints 290% where it means 2.9%.

Method two is a forward yield from the declared rate. Put the most recently declared per-share rate in D2 and the number of payments a year in E2:

• Forward yield: =D2*E2/GOOGLEFINANCE("KO","price")

Nothing automates D2. A board declares a rate, and you read it off the declaration and type it in. That is the honest version of a forward yield, and the choice between the two numerators is the whole subject of trailing versus forward dividend yield.

Two smaller notes on the function. Quotes are delayed by up to 20 minutes, and the delay on any given cell is readable with =GOOGLEFINANCE("KO","datadelay"). And a historical fetch returns a two by two array rather than a number, so wrap it: =INDEX(GOOGLEFINANCE("KO","close",DATE(2026,6,30)),2,2) gives the closing price alone.

The annualization step, and the mistake it hides

The most common broken formula in a dividend sheet multiplies the latest payment by four. It works right up to the moment the company raises. After a raise, three of the four payments inside the trailing year were made at the old rate, and four times the new one overstates what a shareholder actually received.

The panel below sizes that gap for eight large payers: what each name paid per share over the trailing twelve months, next to four times its most recent payment.

SELECT
    ticker,
    round(toFloat64(sum(cash_amount)), 4)                          AS paid_last_12m,
    round(toFloat64(argMax(cash_amount, ex_dividend_date)) * 4, 4) AS latest_x4,
    round((toFloat64(argMax(cash_amount, ex_dividend_date)) * 4
           / toFloat64(sum(cash_amount)) - 1) * 100, 2)            AS gap_pct,
    count()                                                        AS payment_count
FROM
(
    SELECT
        ticker,
        ex_dividend_date,
        max(cash_amount) AS cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('KO', 'JNJ', 'PG', 'AAPL', 'MSFT', 'CVX', 'ABBV', 'IBM')
      AND ex_dividend_date >  today() - 365
      AND ex_dividend_date <= today()
    GROUP BY ticker, ex_dividend_date
)
GROUP BY ticker
ORDER BY gap_pct DESC

Sorted by the gap, JNJ sits at the top. It made 4 payments totalling $5.24 per share over the window, while four times its latest payment comes to $5.36, 2.29% higher. On a stock yielding near 3%, an error of that size moves the printed figure by a tenth of a percentage point or more, which is enough to reorder a sorted list.

The payment count column is the second argument for SUMIFS over multiplication. A rolling twelve month window does not always hold exactly four quarterly payments. Ex-dividend dates drift by a few days each year, and a window can catch three or five. COUNTIFS tells you which one happened before you trust the total.

Special dividends are not a run rate

The other classic error treats a one-off payment as part of the schedule. A company clears surplus cash with a single large distribution, the SUMIFS sweeps it into the trailing total, and the yield cell jumps. Twelve months later it quietly drops back.

These payments are not rare. The panel counts every payment across the US market that the dividend calendar marks as one-time rather than part of a repeating schedule, month by month.

SELECT
    toString(month_start)                           AS month,
    formatDateTime(month_start, '%b %Y')            AS month_label,
    countIf(freq = '0')                             AS one_time_payments,
    round(countIf(freq = '0') / count() * 100, 2)   AS one_time_share_pct
FROM
(
    SELECT
        toStartOfMonth(ex_dividend_date)        AS month_start,
        ticker,
        ex_dividend_date,
        ifNull(toString(any(frequency)), 'na')  AS freq
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= toStartOfMonth(today() - 730)
      AND ex_dividend_date <  toStartOfMonth(today())
    GROUP BY month_start, ticker, ex_dividend_date
)
GROUP BY month_start
ORDER BY month

In Jul 2026, the most recent complete month, 111 payments carried a one-time marking, 2.91% of everything that went ex-dividend that month. A rolling twelve month sum will meet one of them sooner or later.

The fix is a flag column. Put regular or special in column C beside each payment, then add it as a criterion:

• =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY(),$C$2:$C,"regular")

Keep the special rows in the sheet. They are real cash and they belong in a record of what was received. They do not belong in a number meant to describe an ongoing rate.

What the finished cell prints

A trailing yield built this way has two moving parts, and they move on different clocks. The dividend total changes a few times a year, when a payment enters or leaves the window or a raise lands. The price changes every session. The panel below runs the finished calculation month by month over the last two years for the same payer.

WITH
    month_close AS
    (
        SELECT
            toLastDayOfMonth(date) AS month_end,
            argMax(close, date)    AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'KO'
          AND date >= toStartOfMonth(today() - 730)
          AND date <  toStartOfMonth(today())
        GROUP BY month_end
    ),
    payouts AS
    (
        SELECT
            ex_dividend_date,
            max(cash_amount) AS cash_amount
        FROM global_markets.stocks_dividends
        WHERE ticker = 'KO'
          AND ex_dividend_date >= today() - 1160
          AND ex_dividend_date <= today()
        GROUP BY ex_dividend_date
    )
SELECT
    toString(month_end)                       AS month,
    formatDateTime(month_end, '%b %Y')        AS month_label,
    round(ttm, 4)                             AS ttm_dividends,
    round(ttm / close_px * 100, 2)            AS trailing_yield_pct
FROM
(
    SELECT
        m.month_end                                   AS month_end,
        toFloat64(any(m.close_px))                    AS close_px,
        toFloat64(sumIf(p.cash_amount,
            (p.ex_dividend_date >  subtractYears(m.month_end, 1))
            AND (p.ex_dividend_date <= m.month_end))) AS ttm
    FROM month_close AS m
    CROSS JOIN payouts AS p
    GROUP BY m.month_end
)
ORDER BY month

The dividend line steps, then sits flat. The yield line moves every month over the same period. In Aug 2024 the trailing total stood at $1.89 per share against a yield of 2.61%; by Jul 2026 the total stood at $2.08 and the yield read 2.37%. A yield cell that changes while nobody has touched the dividend column is doing what a trailing yield does: the denominator moved.

Once one ticker works, copy the block down for every holding and weight the results by market value rather than averaging the yields. That step is portfolio weighted dividend yield.

FAQ

Does GOOGLEFINANCE have a dividend yield attribute?

No. The function covers live quote fields and historical prices, and none of them is a dividend or a yield. =GOOGLEFINANCE("KO","yield") returns #N/A. A yield in a sheet is assembled from a dividend figure you supply and a price the function returns.

How do I calculate trailing dividend yield in Google Sheets?

Keep ex-dividend dates in one column and cash per share in the next, sum the last twelve months with =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY()), divide that total by =GOOGLEFINANCE("KO","price"), and format the result cell as a percentage.

Why does my dividend yield differ from the one on my broker's page?

Most often the numerator. A page quoting a forward yield annualizes the current declared rate, while a twelve month SUMIFS returns a trailing figure that still contains payments made at the old rate. A special dividend inside the window widens the gap further.

How do I keep a special dividend out of the yield?

Add a column marking each payment regular or special, then pass that column as an extra criterion pair in the SUMIFS. The special payment stays in the sheet and stays out of the yield.

Can Google Sheets pull dividend history automatically?

Not through GOOGLEFINANCE. The dividend column is maintained by hand or pasted in from a source that publishes payment records. That is the one manual step in both methods on this page.

Every panel here ships with the SQL that produced it, so open one to see exactly how a total was counted. The same questions can be asked in plain English on the Strasmore terminal.