Как рассчитать дивидендную доходность в Google Таблицах

12
Для расчета дивидендной доходности в Google Таблицах необходимо вычислять показатель самостоятельно. Функция GOOGLEFINANCE возвращает цену, прибыль на акцию (EPS), коэффициент P/E, рыночную капитализацию и длинный список других полей котировок, однако дивидендная доходность в их число не входит: =GOOGLEFINANCE("AAPL","yield") возвращает #N/A, и ни один другой вариант написания этого слова не дает результата. Существуют два надежных способа решения задачи. Вычислите доходность за двенадцать месяцев на основе столбца с дивидендами, который вы ведете в таблице, или введите объявленный прогнозный размер выплат в ячейку и выполните деление.

Почему у функции GOOGLEFINANCE нет атрибута дивидендной доходности

Атрибуты функции делятся на две группы. Первая — это поля котировок в реальном времени, которые запрашиваются с помощью двух аргументов: price, volume, pe, eps, marketcap, high52, changepct и еще примерно дюжины других. Вторая — это поля исторических цен, которые запрашиваются с указанием даты или диапазона дат: open, high, low, close, volume. Данные о дивидендах не относятся ни к одной из этих групп. Ни один атрибут не возвращает историю выплат, поэтому числитель для расчета доходности приходится получать из внешних источников.

Дивидендная доходность — это годовая сумма дивидендов на акцию, деленная на цену акции, выраженная в процентах. Цена — это одна ячейка. Дивиденды требуют расчетов. Если сам коэффициент для вас в новинку, в статье что на самом деле измеряет дивидендная доходность этот вопрос разобран до перехода к работе с таблицами, а в как рассчитать дивидендную доходность приведена пошаговая арифметика.

Вот исходные данные: все выплаты, произведенные одним эмитентом за последние три года. Каждая строка — это 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 выплаты за указанный период. Вторая строка на графике — это каждая выплата, умноженная на четыре: годовая ставка, подразумеваемая при условии, что квартальная выплата сохранится в течение года. Она повышается один раз в год и остается неизменной в промежутках. Именно на этом этапе «ступенчатого» изменения совершается большинство ошибок при расчете доходности в таблицах. Полный список доступен в истории дивидендов Coca-Cola.

Два способа расчета дивидендной доходности в Google Таблицах

Первый метод — это расчет доходности за двенадцать месяцев на основе собственных данных. Укажите даты ex-dividend в столбце A, а размер выплаты на акцию — в столбце B (по одной строке на каждую выплату). Затем используйте следующие формулы:

• Сумма выплат за двенадцать месяцев: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())
• Текущая цена: =GOOGLEFINANCE("KO","price")
• Доходность за двенадцать месяцев (ячейку нужно отформатировать как процентную): =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) соответствует календарному дню двенадцать месяцев назад, поэтому временной интервал сдвигается автоматически. Форматируйте ячейку с доходностью как процентную, вместо того чтобы умножать результат на сто внутри формулы. В таблице, где не соблюдается это правило, значение два целых девять десятых процента отобразится как двести девяносто процентов.

Второй метод — это расчет форвардной доходности на основе объявленной ставки. Введите размер последней объявленной выплаты на акцию в ячейку D2, а количество выплат в год — в ячейку E2:

• Форвардная доходность: =D2*E2/GOOGLEFINANCE("KO","price")

Автоматизировать значение в D2 невозможно. Совет директоров объявляет ставку, вы находите её в официальном сообщении и вводите вручную. Это наиболее точный вариант расчета форвардной доходности, а выбор между этими двумя показателями — основная тема статьи доходность за двенадцать месяцев против форвардной дивидендной доходности.

Два небольших замечания по работе функции. Котировки могут задерживаться на срок до двадцати минут, а задержку для любой конкретной ячейки можно проверить с помощью =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% больше. Для акции с доходностью около трех процентов ошибка такого масштаба меняет итоговый показатель на одну десятую процентного пункта или более, чего достаточно для изменения порядка в отсортированном списке.

Столбец с количеством выплат — это второй аргумент в пользу использования функции СУММЕСЛИ (SUMIFS) вместо простого умножения. Скользящее двенадцатимесячное окно не всегда содержит ровно четыре квартальные выплаты. Даты ex-dividend каждый год немного смещаются, и в окно могут попасть три или пять выплат. COUNTIFS показывает, какой именно случай произошел, прежде чем вы доверитесь итоговой сумме.

Специальные дивиденды не являются регулярными выплатами

Другая типичная ошибка — рассматривать разовый платеж как часть графика выплат. Компания распределяет излишки денежных средств посредством одной крупной выплаты, функция SUMIFS включает её в совокупный показатель за двенадцать месяцев, и значение доходности резко возрастает. Через год этот показатель незаметно снижается.

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

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")

Сохраняйте строки со специальными дивидендами в таблице. Это реальные денежные средства, и они должны присутствовать в отчете о фактически полученных доходах. Однако им не место в показателе, который призван отражать текущий уровень регулярных выплат.

Что показывает итоговая ячейка

Скользящая доходность, рассчитанная таким образом, состоит из двух переменных, которые изменяются с разной периодичностью. Сумма дивидендов меняется несколько раз в год, когда выплата попадает в расчетный период или выходит из него, либо когда происходит повышение размера дивидендов. Цена меняется каждую торговую сессию. В приведенной ниже таблице расчет выполнен помесячно за последние два года для того же эмитента.

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

Линия дивидендов ступенчатая, с периодами стагнации. Линия доходности меняется каждый месяц на протяжении того же периода. В Aug 2024 совокупный скользящий показатель составлял $1.89 на акцию при доходности 2.61%; к Jul 2026 общая сумма достигла $2.08, а доходность составила 2.37%. Если ячейка доходности меняется, хотя столбец дивидендов не корректировался, это означает, что скользящая доходность работает штатно: изменился знаменатель.

Как только расчет для одного тикера настроен, скопируйте блок для каждого актива в портфеле и взвесьте результаты по рыночной стоимости, а не путем простого усреднения доходностей. Этот этап называется портфельная взвешенная дивидендная доходность.

Часто задаваемые вопросы

Есть ли у GOOGLEFINANCE атрибут для дивидендной доходности?

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

Как рассчитать дивидендную доходность за двенадцать месяцев в Google Таблицах?

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

Почему моя дивидендная доходность отличается от данных на странице брокера?

Чаще всего дело в числителе. Страница, отображающая прогнозную (forward) доходность, берет текущую объявленную ставку и экстраполирует её на год. Функция SUMIFS за двенадцать месяцев возвращает исторический показатель, который все еще включает выплаты по старой ставке. Специальный дивиденд, попавший в этот период, еще сильнее увеличивает разрыв.

Как исключить специальный дивиденд из расчета доходности?

Добавьте столбец, в котором отметьте каждую выплату как регулярную или специальную, а затем добавьте этот столбец в качестве дополнительной пары критериев в функцию SUMIFS. Специальная выплата останется в таблице, но не будет учитываться в расчете доходности.

Может ли Google Таблица автоматически подгружать историю дивидендов?

Не через GOOGLEFINANCE. Столбец с дивидендами ведется вручную или заполняется данными из источника, публикующего отчеты о выплатах. Это единственный этап, требующий ручного ввода в обоих методах, описанных на этой странице.

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