Stopa dywidendy w Google Sheets: dwie metody obliczeń

Obliczanie stopy dywidendy w Google Sheets

Aby obliczyć stopę dywidendy w arkuszu Google Sheets, należy samodzielnie przygotować odpowiednie zestawienie. Funkcja GOOGLEFINANCE zwraca cenę, EPS, P/E, kapitalizację rynkową oraz długą listę innych wskaźników, jednak stopa dywidendy nie znajduje się wśród nich: =GOOGLEFINANCE("AAPL","yield") zwraca #N/A, a żadna inna pisownia tego terminu nie przynosi rezultatu. Istnieją dwa sprawdzone sposoby obejścia tego problemu. Można obliczyć stopę historyczną (trailing yield) na podstawie kolumny z dywidendami, którą użytkownik prowadzi w arkuszu, lub wpisać zadeklarowaną stopę dywidendy forward do komórki i wykonać dzielenie.

Dlaczego funkcja GOOGLEFINANCE nie posiada atrybutu rentowności dywidendy

Atrybuty funkcji dzielą się na dwie grupy. Pierwszą stanowią pola notowań bieżących, pobierane za pomocą dwóch argumentów: price, volume, pe, eps, marketcap, high52, changepct oraz kilkunastu innych. Drugą grupę tworzą pola cen historycznych, pobierane z uwzględnieniem daty lub zakresu dat: open, high, low, close, volume. Dane o dywidendach nie należą do żadnej z tych grup. Żaden atrybut nie zwraca informacji o płatnościach ani historii wypłat, co oznacza, że licznik rentowności musi zostać wyznaczony poza funkcją.

Rentowność dywidendy to roczna dywidenda na akcję podzielona przez cenę akcji, wyrażona w procentach. Część dotycząca ceny stanowi jedną komórkę. Część dotycząca dywidendy wymaga dodatkowej pracy. Jeśli sam wskaźnik jest dla Państwa nowością, artykuł co faktycznie mierzy rentowność dywidendy wyjaśnia to zagadnienie przed przejściem do mechaniki arkusza kalkulacyjnego, a jak obliczyć rentowność dywidendy przeprowadza przez proces obliczeń.

Oto surowe dane: każda płatność dokonana przez jednego emitenta w ciągu ostatnich około trzech lat. Każdy wiersz to data ex-dividend, czyli dzień, od którego nabywca akcji nie otrzymuje nadchodzącej płatności, zestawiona z kwotą gotówki wypłaconą na akcję.

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

Spółka Coca-Cola wypłaciła $0.46 na akcję w dniu Sep 14, 2023 oraz $0.53 w dniu Jun 15, 2026, co daje łącznie 12 płatności w tym okresie. Druga linia na wykresie przedstawia każdą płatność pomnożoną przez cztery: jest to roczna stopa implikowana, przy założeniu, że płatność z danego kwartału powtarzałaby się przez cały rok. Wartość ta rośnie raz w roku i pozostaje stała w międzyczasie. To właśnie ten skokowy charakter jest przyczyną błędów w większości obliczeń rentowności w arkuszach. Pełny zapis znajduje się w historii dywidend Coca-Coli.

Dwie metody obliczania stopy dywidendy w Google Sheets

Pierwsza metoda polega na obliczeniu stopy kroczącej na podstawie własnej kolumny. Należy wprowadzić daty ex-dividend w kolumnie A oraz kwotę gotówki na akcję w kolumnie B, po jednym wierszu na każdą płatność, a następnie:

• Suma z ostatnich dwunastu miesięcy: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())
• Bieżąca cena: =GOOGLEFINANCE("KO","price")
• Stopa krocząca, z komórką sformatowaną jako wartość procentowa: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())/GOOGLEFINANCE("KO","price")
• Liczba płatności w tym samym oknie, w celach kontrolnych: =COUNTIFS($A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())

EDATE(TODAY(),-12) to ten sam dzień kalendarzowy dwanaście miesięcy wcześniej, dzięki czemu okno przesuwa się automatycznie. Należy sformatować komórkę stopy jako wartość procentową, zamiast mnożyć przez sto wewnątrz formuły. Arkusz, który wykonuje obie te czynności, wyświetli dwieście dziewięćdziesiąt procent tam, gdzie oznacza to dwa i dziewięć dziesiątych procenta.

Druga metoda to stopa dywidendy forward obliczana na podstawie ogłoszonej stawki. Należy wprowadzić ostatnio ogłoszoną stawkę na akcję w komórce D2 oraz liczbę płatności w roku w komórce E2:

• Stopa dywidendy forward: =D2*E2/GOOGLEFINANCE("KO","price")

Nic nie automatyzuje komórki D2. Zarząd ogłasza stawkę, a użytkownik odczytuje ją z komunikatu i wpisuje ręcznie. Jest to rzetelna wersja stopy forward, a wybór między tymi dwoma licznikami stanowi sedno zagadnienia stopa dywidendy krocząca kontra forward.

Dwie dodatkowe uwagi dotyczące funkcji. Notowania są opóźnione o maksymalnie dwadzieścia minut, a opóźnienie dla danej komórki można odczytać za pomocą =GOOGLEFINANCE("KO","datadelay"). Ponadto pobranie danych historycznych zwraca tablicę dwa na dwa zamiast pojedynczej liczby, dlatego należy ją opakować: =INDEX(GOOGLEFINANCE("KO","close",DATE(2026,6,30)),2,2) zwraca wyłącznie cenę zamknięcia.

Krok annualizacji i błąd, który ukrywa

Najczęściej spotykany błędny wzór w arkuszach dywidendowych polega na pomnożeniu ostatniej wypłaty przez cztery. Działa on poprawnie do momentu, w którym spółka podnosi dywidendę. Po podwyżce trzy z czterech wypłat w ujęciu rocznym zostały zrealizowane według starej stawki, a pomnożenie nowej stawki przez cztery zawyża kwotę, którą akcjonariusz faktycznie otrzymał.

Poniższe zestawienie obrazuje tę różnicę dla ośmiu dużych płatników dywidendy: zestawiono kwotę wypłaconą na akcję w ciągu ostatnich dwunastu miesięcy z czterokrotnością ostatniej płatności.

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

W zestawieniu posortowanym według wielkości luki, na pierwszym miejscu znajduje się JNJ. Spółka zrealizowała 4 płatności o łącznej wartości $5.24 na akcję w tym okresie, podczas gdy czterokrotność ostatniej wypłaty wynosi $5.36, czyli o 2.29% więcej. W przypadku akcji o rentowności bliskiej 3%, błąd tej wielkości zmienia prezentowaną wartość o jedną dziesiątą punktu procentowego lub więcej, co wystarczy, aby zmienić kolejność na posortowanej liście.

Kolumna z liczbą płatności stanowi drugi argument przemawiający za stosowaniem funkcji SUMIFS zamiast mnożenia. Kroczący okres dwunastu miesięcy nie zawsze obejmuje dokładnie cztery płatności kwartalne. Daty ex-dividend przesuwają się każdego roku o kilka dni, przez co w danym oknie mogą znaleźć się trzy lub pięć wypłat. COUNTIFS informuje, która z tych sytuacji miała miejsce, zanim zaufasz sumie.

Dywidendy specjalne nie stanowią stałego elementu stopy zwrotu

Innym klasycznym błędem jest traktowanie jednorazowej wypłaty jako części harmonogramu. Spółka przekazuje nadwyżkę gotówki w formie pojedynczej, wysokiej dystrybucji, funkcja SUMIFS uwzględnia ją w sumie kroczącej, a komórka z rentownością gwałtownie rośnie. Po dwunastu miesiącach wartość ta po cichu wraca do poprzedniego poziomu.

Takie wypłaty nie należą do rzadkości. Panel zlicza każdą płatność na rynku amerykańskim, którą kalendarz dywidend oznacza jako jednorazową, a nie będącą częścią powtarzalnego harmonogramu, w ujęciu miesięcznym.

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

W Jul 2026, czyli ostatnim pełnym miesiącu, 111 płatności posiadało oznaczenie jednorazowości, co stanowiło 2.91% wszystkich wypłat, dla których nastąpił dzień ex-dividend w tym miesiącu. Dwunastomiesięczna suma krocząca prędzej czy później uwzględni jedną z nich.

Rozwiązaniem jest kolumna z flagą. Należy umieścić regular lub special w kolumnie C obok każdej płatności, a następnie dodać ją jako kryterium:

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

Należy zachować wiersze z dywidendami specjalnymi w arkuszu. Stanowią one realną gotówkę i powinny figurować w rejestrze otrzymanych środków. Nie powinny jednak być uwzględniane w wyliczeniach mających na celu określenie bieżącej stopy wypłat.

Co pokazują gotowe obliczenia

Rentowność krocząca wyliczona w ten sposób składa się z dwóch zmiennych elementów, które zmieniają się w różnym czasie. Suma dywidend zmienia się kilka razy w roku, gdy płatność wchodzi w zakres obliczeń lub z niego wypada, albo gdy następuje podwyżka dywidendy. Cena zmienia się na każdej sesji. Poniższy panel przedstawia gotowe obliczenia w ujęciu miesięcznym z ostatnich dwóch lat dla tego samego emitenta.

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

Linia dywidendy jest schodkowa i okresowo płaska. Linia rentowności zmienia się co miesiąc w tym samym okresie. W Aug 2024 suma krocząca wynosiła 1.89 USD na akcję przy rentowności 2.61%; do Jul 2026 suma wyniosła 2.08 USD, a rentowność osiągnęła poziom 2.37%. Jeśli komórka rentowności zmienia się, mimo że nikt nie modyfikował kolumny dywidend, oznacza to, że rentowność krocząca działa zgodnie z założeniami: zmienił się mianownik.

Gdy jeden ticker działa poprawnie, należy skopiować blok dla każdego składnika portfela i wyważyć wyniki według wartości rynkowej, zamiast wyciągać średnią z rentowności. Ten krok to ważona portfelowo rentowność dywidendy.

Najczęściej zadawane pytania

Czy funkcja GOOGLEFINANCE posiada atrybut rentowności dywidendy?

Nie. Funkcja obsługuje bieżące notowania oraz ceny historyczne, z których żadna nie dotyczy dywidendy ani rentowności. =GOOGLEFINANCE("KO","yield") zwraca #N/A. Rentowność w arkuszu kalkulacyjnym wylicza się na podstawie podanej wartości dywidendy oraz ceny zwróconej przez funkcję.

Jak obliczyć kroczącą rentowność dywidendy w Google Sheets?

Należy umieścić daty ex-dividend w jednej kolumnie, a kwotę wypłaty na akcję w kolejnej, zsumować wypłaty z ostatnich dwunastu miesięcy za pomocą =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY()), podzielić tę sumę przez =GOOGLEFINANCE("KO","price") i sformatować komórkę z wynikiem jako wartość procentową.

Dlaczego moja rentowność dywidendy różni się od tej na stronie brokera?

Najczęściej przyczyną jest licznik. Strona podająca rentowność prognozowaną (forward yield) annualizuje bieżącą zadeklarowaną stawkę, podczas gdy funkcja SUMIFS dla dwunastu miesięcy zwraca wartość kroczącą, która wciąż uwzględnia płatności dokonane według starej stawki. Dywidenda specjalna ujęta w tym okresie dodatkowo zwiększa rozbieżność.

Jak wykluczyć dywidendę specjalną z obliczeń rentowności?

Należy dodać kolumnę oznaczającą każdą płatność jako regularną lub specjalną, a następnie przekazać tę kolumnę jako dodatkową parę kryteriów w funkcji SUMIFS. Płatność specjalna pozostanie w arkuszu, ale nie wpłynie na wynik rentowności.

Czy Google Sheets może automatycznie pobierać historię dywidend?

Nie za pośrednictwem GOOGLEFINANCE. Kolumna dywidend musi być prowadzona ręcznie lub uzupełniana danymi wklejanymi ze źródła publikującego rejestry płatności. Jest to jedyny ręczny etap w obu metodach opisanych na tej stronie.

Każdy panel zawiera kod SQL, który wygenerował dane, więc można go otworzyć, aby sprawdzić dokładny sposób obliczenia sumy. Te same pytania można zadać w języku angielskim za pośrednictwem terminala Strasmore.