Google Sheets 배당수익률 계산 공식 두 가지

Google Sheets에서 배당수익률 계산하기

Google Sheets에서 배당수익률(dividend yield)을 계산하려면 직접 수식을 구성해야 합니다. GOOGLEFINANCE 함수는 주가(price), 주당순이익(EPS), 주가수익비율(P/E), 시가총액(market cap) 등 다양한 종목 정보를 제공하지만, 배당수익률은 포함되어 있지 않습니다. =GOOGLEFINANCE("AAPL","yield")을 입력해도 #N/A가 반환될 뿐이며, 다른 철자로 입력해도 결과는 같습니다. 이를 해결하는 두 가지 방법이 있습니다. 시트에 직접 관리하는 배당금 열을 활용해 후행 수익률(trailing yield)을 계산하거나, 공시된 예상 배당금을 셀에 입력한 뒤 이를 주가로 나누는 방식입니다.

GOOGLEFINANCE에 배당수익률 속성이 없는 이유

해당 함수의 속성은 두 가지 계열로 나뉩니다. 첫 번째는 두 개의 인수로 호출하는 실시간 시세 필드로, price, volume, pe, eps, marketcap, high52, changepct 및 십여 개의 추가 항목이 있습니다. 두 번째는 날짜나 기간을 지정하여 호출하는 과거 가격 필드로, open, high, low, close, volume가 이에 해당합니다. 배당 기록은 이 두 계열 어디에도 속하지 않습니다. 지급액이나 지급 이력을 반환하는 속성이 없으므로, 수익률 계산의 분자가 되는 배당금 데이터는 함수 외부에서 가져와야 합니다.

배당수익률(dividend yield)은 주당 연간 배당금을 주당 가격으로 나눈 값이며 백분율로 표시합니다. 이 중 가격 부분은 하나의 셀로 해결됩니다. 문제는 배당금 부분입니다. 만약 이 비율의 개념이 생소하다면, 배당수익률이 실제로 측정하는 것에서 스프레드시트 작업 이전에 알아야 할 내용을 다루고 있으며, 배당수익률 계산 방법에서 산술 과정을 설명합니다.

다음은 원천 데이터입니다. 한 우량 배당 기업이 지난 약 3년간 지급한 모든 배당 내역입니다. 각 행은 배당락일(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)는 Sep 14, 2023에 주당 $0.46를, Jun 15, 2026에 $0.53를 지급했으며, 해당 기간 동안 총 12회의 배당이 있었습니다. 차트의 두 번째 선은 각 배당금을 4배로 곱한 값으로, 해당 분기의 배당이 연간 지속된다고 가정할 때의 연간 환산율입니다. 이 수치는 1년에 한 번씩 계단식으로 상승하며 그 사이에는 평탄하게 유지됩니다. 대부분의 스프레드시트 수익률 계산에서 오류가 발생하는 지점이 바로 이 계단식 변화 구간입니다. 전체 기록은 코카콜라의 배당 이력에서 확인할 수 있습니다.

Google Sheets에서 배당수익률을 구하는 두 가지 방법

첫 번째 방법은 직접 만든 열을 활용하여 후행 수익률(trailing yield)을 산출하는 것입니다. A열에는 배당락일(ex-dividend date)을, B열에는 주당 현금 배당액을 행별로 입력한 뒤 다음 수식을 사용합니다.

• 최근 12개월 합계: =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)은 12개월 전 동일한 달력을 가리키므로, 기간은 자동으로 갱신됩니다. 수식 내에서 100을 곱하는 대신 셀 서식을 백분율로 지정하십시오. 두 방식을 모두 적용한 시트에서 2.9%를 290%로 표시하는 오류가 발생할 수 있습니다.

두 번째 방법은 공시된 배당률을 바탕으로 선행 수익률(forward yield)을 구하는 것입니다. D2 셀에 가장 최근 공시된 주당 배당액을, E2 셀에 연간 배당 횟수를 입력합니다.

• 선행 수익률: =D2*E2/GOOGLEFINANCE("KO","price")

D2 셀의 값을 자동으로 불러올 방법은 없습니다. 이사회에서 배당률을 공시하면 해당 내용을 확인하여 직접 입력해야 합니다. 이것이 정석적인 선행 수익률 산출 방식이며, 두 분자 값 중 무엇을 선택할지는 후행 배당수익률과 선행 배당수익률의 차이에 관한 핵심 주제입니다.

함수와 관련하여 두 가지 주의사항이 있습니다. 주가 정보는 최대 20분 지연되며, 특정 셀의 지연 시간은 =GOOGLEFINANCE("KO","datadelay")으로 확인할 수 있습니다. 또한 과거 데이터 호출 시 숫자가 아닌 2x2 배열이 반환되므로, =INDEX(GOOGLEFINANCE("KO","close",DATE(2026,6,30)),2,2)와 같이 감싸서 사용해야 종가만 추출할 수 있습니다.

연율화 단계와 그 이면에 숨겨진 오류

배당금 시트에서 가장 흔히 발견되는 잘못된 공식은 최근 지급액에 4를 곱하는 방식입니다. 이 방식은 기업이 배당을 인상하기 전까지는 정확하게 작동합니다. 배당 인상 이후에는 지난 1년간 지급된 네 번의 배당 중 세 번이 이전 배당률로 지급된 것이기에, 최근 배당금에 4를 곱하면 주주가 실제로 받은 금액보다 과대평가됩니다.

아래 표는 여덟 개의 대형 배당주를 대상으로 이러한 차이를 보여줍니다. 각 종목이 지난 12개월 동안 주당 얼마를 지급했는지와, 가장 최근 지급액의 4배를 비교한 수치입니다.

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를 지급했으나, 최근 지급액의 4배는 $5.36로 2.29%만큼 더 높게 나타납니다. 배당수익률이 3%에 가까운 주식에서 이러한 규모의 오류는 표기된 수치를 0.1%포인트 이상 변동시키며, 이는 정렬된 리스트의 순서를 바꿀 만큼 충분한 차이입니다.

지급 횟수 열은 곱셈 대신 SUMIFS 함수를 사용해야 하는 두 번째 근거입니다. 12개월 롤링 윈도우(rolling twelve month window)에 항상 정확히 네 번의 분기 배당이 포함되는 것은 아닙니다. 배당락일(ex-dividend date)은 매년 며칠씩 이동하며, 특정 기간에는 세 번 혹은 다섯 번의 배당이 포함될 수 있습니다. COUNTIFS는 합계 수치를 신뢰하기 전에 어떤 상황이 발생했는지 알려줍니다.

특별 배당은 경상적 수익이 아니다

또 다른 전형적인 오류는 일회성 지급액을 정기적인 배당 일정의 일부로 간주하는 것입니다. 기업이 잉여 현금을 한 번의 대규모 분배로 해소하면, SUMIFS 함수는 이를 후행 합계에 포함시키고 배당수익률 셀은 급등합니다. 그리고 12개월 후 해당 수치는 조용히 다시 하락합니다.

이러한 지급은 드문 일이 아닙니다. 본 패널은 미국 시장 전반에서 배당 캘린더가 반복적인 일정이 아닌 일회성으로 표시한 모든 지급 건을 월별로 집계합니다.

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건의 지급이 일회성으로 표시되었으며, 이는 해당 월에 배당락일(ex-dividend date)을 맞이한 전체 건수의 2.91%에 해당합니다. 12개월 롤링 합계를 산출하다 보면 조만간 이러한 일회성 배당을 마주하게 됩니다.

해결책은 플래그 열을 활용하는 것입니다. 각 지급액 옆의 C열에 regular 또는 special을 기입한 뒤, 이를 조건으로 추가하십시오:

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

특별 배당 행은 시트에 그대로 유지하십시오. 이는 실제 현금이며 수령 내역 기록에 포함되어야 합니다. 다만, 지속적인 배당률을 나타내기 위한 수치에는 포함해서는 안 됩니다.

최종 셀에 표시되는 값

이런 방식으로 산출된 후행 수익률(trailing yield)은 두 가지 변동 요소를 가지며, 이들은 서로 다른 주기로 움직입니다. 배당금 총액은 배당 지급이 기간 내에 포함되거나 제외될 때, 혹은 배당 인상이 발생할 때 연간 몇 차례 변동합니다. 반면 주가는 매 거래일마다 변합니다. 아래 패널은 동일한 배당 지급 종목을 대상으로 지난 2년간 월별로 최종 계산값을 산출한 결과입니다.

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%로 나타났습니다. 배당금 열을 수정하지 않았는데도 수익률 셀이 변하는 것은 후행 수익률의 본래 기능, 즉 분모인 주가가 변했기 때문입니다.

하나의 티커(ticker)에 대해 작업이 완료되면, 해당 블록을 모든 보유 종목으로 복사하십시오. 이때 수익률을 단순 평균하지 말고 시장 가치(market value)에 따라 가중치를 적용해야 합니다. 이 단계가 바로 포트폴리오 가중 배당 수익률입니다.

자주 묻는 질문(FAQ)

GOOGLEFINANCE에 배당수익률(dividend yield) 속성이 있습니까?

없습니다. 해당 함수는 실시간 시세 필드와 과거 가격 데이터만을 제공하며, 배당금이나 수익률 항목은 포함되어 있지 않습니다. =GOOGLEFINANCE("KO","yield")는 #N/A을 반환합니다. 시트 내의 수익률은 사용자가 입력한 배당금 수치와 함수가 반환하는 가격을 조합하여 산출해야 합니다.

Google Sheets에서 후행 배당수익률(trailing dividend yield)은 어떻게 계산합니까?

한 열에는 배당락일(ex-dividend date)을, 다음 열에는 주당 현금 배당액을 기록하십시오. =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())을 사용하여 최근 12개월 치를 합산한 뒤, 그 합계를 =GOOGLEFINANCE("KO","price")로 나누고 결과 셀의 서식을 백분율로 지정하십시오.

왜 제가 계산한 배당수익률이 증권사 페이지의 수치와 다릅니까?

대부분 분자 값의 차이 때문입니다. 증권사 페이지의 선행 수익률(forward yield)은 현재 공시된 배당률을 연율화한 것이지만, 12개월 SUMIFS 합계는 이전 배당률로 지급된 금액까지 포함하는 후행 수치이기 때문입니다. 해당 기간 내에 특별 배당이 포함되어 있다면 그 차이는 더욱 벌어집니다.

특별 배당을 수익률 계산에서 제외하려면 어떻게 해야 합니까?

각 지급액이 정기 배당인지 특별 배당인지 표시하는 열을 추가한 뒤, 해당 열을 SUMIFS의 추가 조건 범위로 설정하십시오. 이렇게 하면 특별 배당금은 시트에는 남되 수익률 계산에서는 제외됩니다.

Google Sheets에서 배당 이력을 자동으로 가져올 수 있습니까?

GOOGLEFINANCE 함수로는 불가능합니다. 배당금 열은 직접 입력하거나 배당 지급 기록을 게시하는 외부 소스에서 복사해 와야 합니다. 이 페이지에 소개된 두 가지 방법 모두에서 이 과정은 유일한 수동 작업입니다.

이곳의 모든 패널은 해당 데이터를 산출한 SQL과 함께 제공되므로, 패널을 열어 합계가 어떻게 계산되었는지 정확히 확인할 수 있습니다. 동일한 질문은 Strasmore 터미널에서 평이한 영어로도 문의할 수 있습니다.