Dividend Yield for Google Sheets: Two Fixes

To calculate dividend yield for Google Sheets, you need build the number by yourself. GOOGLEFINANCE dey return price, eps, pe, market cap and plenty other quote fields, but dividend yield no dey among dem: =GOOGLEFINANCE("AAPL","yield") dey return #N/A, and no other way wey you spell the word go work. Two workaround dey reliable. You fit calculate trailing yield from dividend column wey you dey maintain for the sheet, or enter the declared forward rate for one cell then divide.

Why GOOGLEFINANCE no get dividend yield attribute

The function attributes dey come in two families. Live quote fields dey come with two arguments: price, volume, pe, eps, marketcap, high52, changepct and about twelve more. Then historical price fields dey come with date or date range: open, high, low, close, volume. Dividend records no belong to any of the two families. No attribute dey return payment or payout history, so dividend yield numerator need come from outside the function.

Dividend yield na annual dividends per share divided by price per share, written as percentage. The price side na one cell. The dividend side na where the work dey. If the ratio still new to you, wetin dividend yield really dey measure explain am before any spreadsheet mechanics, while how to calculate dividend yield show the arithmetic.

This na the raw material: every payment wey one household payer make over roughly the last three years. Each row show ex-dividend date, meaning the date wey buyer wey buy the stock on or after am no go receive the coming payment, beside 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 pay $0.46 per share on Sep 14, 2023 and $0.53 on Jun 15, 2026, with 12 payments across the window. The second line for the chart na each payment multiplied by four: the annual rate wey e imply if that quarter payment repeat throughout the year. E step up once every year and remain flat between the increases. Na this step-up dey cause most spreadsheet yield errors. The complete record dey for Coca-Cola dividend history.

Two ways to get dividend yield for Google Sheets

Method one na trailing yield wey you build from your own column. Put ex-dividend dates for column A and cash per share for column B, one row for each payment, then:

• Total for trailing twelve months: =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 percentage: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())/GOOGLEFINANCE("KO","price")
• Payment count for the same window, as a check: =COUNTIFS($A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())

EDATE(TODAY(),-12) na the same calendar day twelve months ago, so the window dey move forward by itself. Format the yield cell as percentage instead of multiplying by 100 inside the formula. Sheet wey do both fit show 290% when wetin e mean na 2.9%.

Method two na forward yield from the declared rate. Put the latest declared per-share rate for D2 and the number of payments per year for E2:

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

Nothing dey automate D2. Board dey declare the rate, then you read am from the declaration and type am in. Na the honest way to calculate forward yield, and the choice between the two numerators na the whole subject of trailing versus forward dividend yield.

Two smaller notes about the function. Quotes dey delayed by up to 20 minutes, and you fit read the delay for any particular cell with =GOOGLEFINANCE("KO","datadelay"). Historical fetch dey return a two by two array instead of one number, so wrap am: =INDEX(GOOGLEFINANCE("KO","close",DATE(2026,6,30)),2,2) go give you only the closing price.

The annualization step, and the mistake it hides

The most common broken formula for dividend sheet na to multiply the latest payment by four. E dey work correct until company increase dividend. After the increase, three out of the four payments inside the trailing year still use the old rate. So, multiplying the new rate by four go overstate wetin shareholder actually receive.

The panel below measure this gap for eight big payers: how much each company pay per share over the trailing twelve months, beside four times the latest 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

When dem arrange am by the gap, JNJ dey top. E make 4 payments wey total $5.24 per share during the period. But four times the latest payment na $5.36, wey dey 2.29% higher. For stock wey dey yield around 3%, error of this size fit change the printed figure by one-tenth of a percentage point or more. That one fit enough to change the order for a sorted list.

The payment count column na the second reason why SUMIFS better pass multiplication. A rolling twelve-month window no always contain exactly four quarterly payments. Ex-dividend dates fit shift by some days every year, and the window fit catch three or five payments. COUNTIFS go show you which one happen before you trust the total.

Special dividends no be run rate

Another common mistake na to treat one-off payment like say e dey part of the normal schedule. Company fit use one big distribution take clear surplus cash. SUMIFS go sweep am enter trailing total, and yield cell go jump. After twelve months, e go quietly fall back.

These payments no too rare. The panel dey count every payment for US market wey dividend calendar mark as one-time, instead of 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

For Jul 2026, the latest complete month, 111 payments get one-time marking. That na 2.91% of all payments wey go ex-dividend that month. Rolling twelve-month sum go meet one of them sooner or later.

The fix na flag column. Put regular or special for column C beside each payment, then add am as criterion:

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

Keep the special rows for the sheet. Na real cash dem be, and dem belong inside record of wetin you receive. But dem no belong inside number wey suppose describe an ongoing rate.

Wetin the finished cell dey print

Trailing yield wey you build like this get two moving parts, and each one dey move on different clock. Dividend total dey change some times every year, when payment enter or comot from the window, or when company increase am. Price dey change every trading session. The panel below dey run the finished calculation month by month for 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

Dividend line dey jump, then e remain flat. Yield line dey move every month for the same period. For Aug 2024, trailing total stand at $1.89 per share against yield of 2.61%; by Jul 2026, total stand at $2.08 and yield read 2.37%. If yield cell change while nobody touch dividend column, na exactly wetin trailing yield dey do: denominator don move.

Once one ticker work, copy the block down for every holding, then weight the results by market value instead of averaging the yields. That step na portfolio weighted dividend yield.

FAQ

GOOGLEFINANCE get dividend yield attribute?

No. The function dey cover live quote fields and historical prices, but none of dem na dividend or yield. =GOOGLEFINANCE("KO","yield") dey return #N/A. To get yield for sheet, you go combine dividend figure wey you provide with price wey the function return.

How I fit calculate trailing dividend yield for Google Sheets?

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

Why my dividend yield different from the one for my broker page?

Most times, na the numerator cause am. Page wey quote forward yield dey annualize the current declared rate. But twelve-month SUMIFS dey return trailing figure wey still include payments made under the old rate. If special dividend dey inside the period, the difference go wide more.

How I fit remove special dividend from the yield?

Add column wey mark each payment as regular or special. Then pass that column as extra criterion pair inside SUMIFS. The special payment go remain for the sheet, but e no go enter the yield.

Google Sheets fit pull dividend history automatically?

No be through GOOGLEFINANCE. You need maintain the dividend column by hand or paste am from source wey dey publish payment records. Na this be the only manual step for both methods on this page.

Every panel for here come with the SQL wey produce am, so open one to see exactly how dem count each total. You fit ask the same questions in plain English for the Strasmore terminal.