배당수익률 계산법 3가지와 Coca-Cola 사례

SELECT ex_dividend_date AS date,
       formatDateTime(ex_dividend_date, '%b %e, %Y') AS ex_date_label,
       round(cash_amount, 4) AS dividend_per_share_usd,
       round(sum(cash_amount) OVER (ORDER BY ex_dividend_date), 4) AS trailing_total_usd
FROM global_markets.stocks_dividends
WHERE ticker = 'KO'
  AND distribution_type = 'recurring'
  AND cash_amount > 0
  AND ex_dividend_date >= toDate('2025-07-01')
  AND ex_dividend_date <= toDate('2026-06-30')
ORDER BY ex_dividend_date

WITH px AS (
    SELECT ticker, argMax(close, window_start) AS price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('KO', 'PEP', 'JNJ', 'PG', 'VZ', 'XOM', 'CVX', 'MCD')
      AND window_start >= now() - INTERVAL 7 DAY
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker
),
dv AS (
    SELECT ticker,
           sum(cash_amount) AS ttm_dividends,
           argMax(cash_amount, ex_dividend_date) AS latest_payment,
           max(frequency) AS pay_frequency
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('KO', 'PEP', 'JNJ', 'PG', 'VZ', 'XOM', 'CVX', 'MCD')
      AND distribution_type = 'recurring'
      AND cash_amount > 0
      AND ex_dividend_date > today() - INTERVAL 1 YEAR
      AND ex_dividend_date <= today()
    GROUP BY ticker
)
SELECT px.ticker AS ticker,
       round(px.price, 2) AS price,
       round(dv.ttm_dividends, 4) AS ttm_dividends_usd,
       round(dv.latest_payment * dv.pay_frequency, 4) AS forward_dividends_usd,
       round(dv.ttm_dividends / px.price * 100, 2) AS trailing_yield_pct,
       round(dv.latest_payment * dv.pay_frequency / px.price * 100, 2) AS forward_yield_pct,
       round(abs(dv.latest_payment * dv.pay_frequency - dv.ttm_dividends) / px.price * 100, 2) AS gap_abs_pct
FROM px
INNER JOIN dv ON px.ticker = dv.ticker
ORDER BY gap_abs_pct DESC

WITH px AS (
    SELECT toStartOfMonth(toDate(toTimeZone(window_start, 'America/New_York'))) AS month_start,
           argMax(close, window_start) AS price,
           max(toDate(toTimeZone(window_start, 'America/New_York'))) AS last_day
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'KO'
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2024-06-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY month_start
),
dv AS (
    SELECT ex_dividend_date, cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker = 'KO'
      AND distribution_type = 'recurring'
      AND cash_amount > 0
      AND ex_dividend_date >= toDate('2023-06-01')
)
SELECT formatDateTime(px.month_start, '%Y-%m') AS month,
       formatDateTimeInJodaSyntax(px.month_start, 'MMMM yyyy') AS month_label,
       round(any(px.price), 2) AS price,
       round(sum(dv.cash_amount), 4) AS ttm_dividends_usd,
       round(sum(dv.cash_amount) / any(px.price) * 100, 2) AS dividend_yield_pct
FROM px, dv
WHERE dv.ex_dividend_date <= px.last_day
  AND dv.ex_dividend_date > px.last_day - INTERVAL 1 YEAR
GROUP BY px.month_start
ORDER BY px.month_start

WITH cur AS (
    SELECT argMax(cash_amount, ex_dividend_date) * 4 AS annual_now
    FROM global_markets.stocks_dividends
    WHERE ticker = 'KO'
      AND distribution_type = 'recurring'
      AND cash_amount > 0
      AND ex_dividend_date <= toDate('2026-06-30')
),
px_now AS (
    SELECT argMax(close, window_start) AS price_now
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'KO'
      AND window_start >= now() - INTERVAL 7 DAY
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
),
buys AS (
    SELECT toYear(toTimeZone(window_start, 'America/New_York')) AS year,
           argMax(close, window_start) AS cost_basis
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'KO'
      AND toMonth(toTimeZone(window_start, 'America/New_York')) = 6
      AND toDayOfMonth(toTimeZone(window_start, 'America/New_York')) >= 20
      AND toYear(toTimeZone(window_start, 'America/New_York')) BETWEEN 2019 AND 2025
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY year
)
SELECT buys.year AS year,
       round(buys.cost_basis, 2) AS cost_basis_usd,
       round(cur.annual_now, 4) AS annual_dividend_now_usd,
       round(cur.annual_now / buys.cost_basis * 100, 2) AS yield_on_cost_pct,
       round(cur.annual_now / px_now.price_now * 100, 2) AS current_yield_pct,
       round(cur.annual_now / buys.cost_basis * 100 - cur.annual_now / px_now.price_now * 100, 2) AS yoc_premium_pts
FROM buys, cur, px_now
ORDER BY year

SELECT ticker,
       round(argMax(dividend_yield, date) * 100, 2) AS dividend_yield_pct,
       round(argMax(dividend_yield * price, date), 2) AS annual_dividend_usd,
       round(argMax(earnings_per_share, date), 2) AS earnings_per_share_usd,
       round(argMax(dividend_yield * price / earnings_per_share, date) * 100, 1) AS payout_ratio_pct
FROM global_markets.stocks_ratios
WHERE ticker IN ('KO', 'JNJ', 'PG', 'VZ', 'XOM', 'CVX', 'MO', 'IBM')
  AND date = (SELECT max(date) FROM global_markets.stocks_ratios)
  AND dividend_yield > 0
  AND earnings_per_share > 0
GROUP BY ticker
ORDER BY payout_ratio_pct DESC