Dividend Yield у Google Sheets: два рішення

Щоб розрахувати dividend yield у Google Sheets, показник потрібно сформувати самостійно. GOOGLEFINANCE повертає ціну, EPS, P/E, ринкову капіталізацію та довгий перелік інших котирувальних показників, але dividend yield серед них немає: =GOOGLEFINANCE("AAPL","yield") повертає #N/A, і жодне інше написання цього терміна також не працює. Є два надійні обхідні рішення. Розрахуйте trailing yield на основі стовпця з дивідендами, який ви ведете в таблиці, або введіть оголошений forward rate в окрему комірку й виконайте ділення.

Чому GOOGLEFINANCE не має атрибута dividend yield

Атрибути функції поділяються на дві групи. Є поля поточних котирувань, які отримують за допомогою двох аргументів: price, volume, pe, eps, marketcap, high52, changepct і ще близько дюжини інших. Є також поля історичних цін, які отримують із датою або діапазоном дат: open, high, low, close, volume. Дані про дивіденди не належать до жодної з цих груп. Жоден атрибут не повертає інформацію про виплату або історію виплат. Тому чисельник dividend yield потрібно отримувати за межами функції.

Dividend yield — це річні дивіденди на акцію, поділені на ціну акції, у відсотках. Ціна — це одна комірка. Розрахунок дивідендної частини потребує більше роботи. Якщо саме співвідношення вам ще не знайоме, матеріал що насправді вимірює dividend yield пояснює його до переходу до роботи з таблицями, а матеріал як розрахувати dividend yield показує арифметику.

Ось вихідні дані: кожна виплата одному платнику домогосподарств за приблизно останні три роки. У кожному рядку зазначено ex-dividend date — дату, починаючи з якої покупець акції не отримує найближчу виплату, — і суму грошової виплати на одну акцію.

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 виплатила $0.46 на акцію Sep 14, 2023 і $0.53 Jun 15, 2026, тобто за цей період відбулося 12 виплат. Друга лінія на графіку показує кожну виплату, помножену на чотири: це річна ставка, яка передбачає повторення відповідної квартальної виплати протягом усього року. Вона підвищується раз на рік і залишається незмінною між такими підвищеннями. Саме на цьому етапі найчастіше виникають помилки під час розрахунку dividend yield у таблицях. Повна історія доступна в матеріалі історія дивідендів Coca-Cola.

Два способи розрахувати dividend yield у Google Sheets

Перший метод — trailing yield, розрахований на основі власного стовпця. У стовпці A вкажіть ex-dividend dates, а в стовпці B — грошову виплату на акцію, по одному рядку на кожну виплату:

• Загальна сума за останні дванадцять місяців: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())
• Поточна ціна: =GOOGLEFINANCE("KO","price")
• Trailing yield із форматом комірки у відсотках: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())/GOOGLEFINANCE("KO","price")
• Кількість виплат за той самий період для перевірки: =COUNTIFS($A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())

EDATE(TODAY(),-12) — це та сама календарна дата дванадцять місяців тому, тому період автоматично зміщується вперед. Встановіть для комірки з yield формат відсотків, а не множте результат на 100 у самій формулі. Таблиця, що робить обидві операції, покаже 290% замість 2,9%.

Другий метод — forward yield на основі оголошеної ставки. Укажіть у D2 найновішу оголошену виплату на акцію, а в E2 — кількість виплат на рік:

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

Автоматизувати D2 неможливо. Рада директорів оголошує ставку, після чого ви берете її з повідомлення про оголошення і вводите вручну. Це коректний варіант розрахунку forward yield, а вибір між двома чисельниками і є суттю питання trailing yield чи forward dividend yield.

Ще два короткі зауваження щодо функції. Котирування надходять із затримкою до 20 хвилин, а затримку для конкретної комірки можна перевірити за допомогою =GOOGLEFINANCE("KO","datadelay"). Крім того, історичний запит повертає масив два на два, а не одне число, тому його потрібно обгорнути: =INDEX(GOOGLEFINANCE("KO","close",DATE(2026,6,30)),2,2) повертає лише ціну закриття.

Річний перерахунок і помилка, яку він приховує

Найпоширеніша помилка у формулах дивідендних таблиць — помножити останню виплату на чотири. Це працює доти, доки компанія не підвищить дивіденд. Після підвищення три з чотирьох виплат за останні дванадцять місяців були здійснені за старою ставкою, тому множення нової виплати на чотири завищує фактичну суму, отриману акціонером.

Панель нижче показує цей розрив для восьми великих платників дивідендів: скільки кожна компанія виплатила на акцію за останні дванадцять місяців і скільки становить чотириразовий розмір останньої виплати.

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

За величиною розриву на першому місці розташована JNJ. За цей період вона здійснила 4 виплат загальною сумою $5.24 на акцію, тоді як чотириразовий розмір останньої виплати становить $5.36, що на 2.29% більше. Для акції з dividend yield близько 3% така помилка змінює відображуваний показник на десяту відсоткового пункту або більше. Цього достатньо, щоб змінити порядок у відсортованому списку.

Стовпець із кількістю виплат — другий аргумент на користь SUMIFS замість простого множення. Плаваюче вікно за останні дванадцять місяців не завжди містить рівно чотири квартальні виплати. Ex-dividend dates щороку зміщуються на кілька днів, тому до вікна можуть потрапити три або п’ять виплат. COUNTIFS показує, який саме варіант має місце, перш ніж ви довіритеся підсумку.

Спеціальні дивіденди не є run rate

Інша типова помилка — включати одноразову виплату до регулярного графіка. Компанія розподіляє надлишок грошових коштів однією великою виплатою, SUMIFS додає її до підсумку за попередні дванадцять місяців, і показник yield різко зростає. Через дванадцять місяців він непомітно повертається до попереднього рівня.

Такі виплати не є рідкістю. Панель враховує кожну виплату на всьому ринку США, яку дивідендний календар позначає як одноразову, а не як частину регулярного графіка, окремо за кожен місяць.

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

У Jul 2026, останньому повному місяці, 111 виплат мали позначку одноразових. Це 2.91% усіх виплат, за якими того місяця настала ex-dividend date. Розрахунок за попередні дванадцять місяців рано чи пізно включить одну з них.

Рішення — додати окремий стовпець-прапорець. Введіть regular або special у стовпець C поруч із кожною виплатою, а потім додайте цей стовпець як критерій:

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

Не видаляйте спеціальні рядки з таблиці. Це фактично отримані грошові кошти, і вони мають бути в обліку отриманих виплат. Але їх не слід включати до показника, який має відображати поточний рівень виплат.

Як виглядає готова комірка

Розрахована таким способом trailing yield має дві змінні складові, і вони змінюються з різною частотою. Сума дивідендів змінюється кілька разів на рік, коли виплата потрапляє у розрахункове вікно або вибуває з нього, чи коли компанія підвищує дивіденд. Ціна змінюється на кожній торговій сесії. Панель нижче щомісяця відображає готовий розрахунок за останні два роки для того самого платника дивідендів.

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

Рядок дивідендів змінюється ступінчасто, а потім залишається незмінним. Рядок yield змінюється щомісяця протягом того самого періоду. У Aug 2024 trailing total становив $1.89 на акцію за yield на рівні 2.61%; у Jul 2026 сума становила $2.08, а yield — 2.37%. Якщо значення yield змінюється, хоча ніхто не торкався стовпця дивідендів, це означає, що trailing yield працює саме так: змінився знаменник.

Коли розрахунок для одного тикера працює, скопіюйте цей блок униз для кожної позиції та зважте результати за ринковою вартістю, а не усереднюйте значення yield. Цей крок називається зважена за портфелем dividend yield.

FAQ

Чи має GOOGLEFINANCE атрибут dividend yield?

Ні. Функція охоплює поля поточних котирувань та історичні ціни, але серед них немає ні dividend, ні yield. =GOOGLEFINANCE("KO","yield") повертає #N/A. Yield у таблиці розраховується на основі dividend, який ви вводите, і ціни, яку повертає функція.

Як розрахувати trailing dividend yield у Google Sheets?

Зберігайте ex-dividend dates в одному стовпці, а cash per share — у наступному. Підсумуйте дані за останні дванадцять місяців за допомогою =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY()), поділіть отриману суму на =GOOGLEFINANCE("KO","price") і застосуйте до комірки формат відсотка.

Чому мій dividend yield відрізняється від показника на сторінці мого брокера?

Найчастіше причина — у чисельнику. Сторінка з forward yield перераховує поточну оголошену ставку в річному вимірі, тоді як SUMIFS за дванадцять місяців повертає trailing figure, до якого все ще входять виплати за попередньою ставкою. Спеціальний dividend у цьому періоді збільшує розбіжність.

Як виключити спеціальний dividend із yield?

Додайте стовпець, у якому кожна виплата позначена як regular або special, а потім передайте цей стовпець як додаткову пару критерію в SUMIFS. Спеціальна виплата залишиться в таблиці, але не враховуватиметься в yield.

Чи може Google Sheets автоматично завантажувати історію dividend?

Не через GOOGLEFINANCE. Стовпець dividend потрібно вести вручну або вставляти з джерела, яке публікує дані про виплати. Це єдиний ручний крок в обох методах на цій сторінці.

Кожна панель містить SQL-запит, за допомогою якого її було побудовано. Відкрийте панель, щоб точно побачити, як було розраховано підсумок. Ті самі запитання можна поставити простою англійською мовою на терміналі Strasmore.