著者: Googleスプレッドシートで配当利回りを計算する方法
GOOGLEFINANCEに配当利回り属性はありません。支払済み配当から実績利回りを出す式と、予想配当金額から先行利回りを求める式を紹介します。
Google Sheetsで配当利回りを計算するには、自分で数値を組み立てる必要があります。GOOGLEFINANCEは株価、EPS、PER、時価総額など多数の相場データ項目を返しますが、配当利回りは含まれていません。=GOOGLEFINANCE("AAPL","yield")は#N/Aを返し、別の表記を試しても認識されません。実用的な方法は二つあります。シートで管理する配当金の列から実績配当利回りを計算するか、発表済みの予想配当金額をセルに入力して割り算します。
GOOGLEFINANCEに配当利回り属性がない理由
この関数の属性は二つの系列に分かれる。一つは、二つの引数で取得するリアルタイムの気配値項目である。price、volume、pe、eps、marketcap、high52、changepctなど、約十数項目がある。もう一つは、日付または日付範囲を指定して取得する過去の価格項目である。open、high、low、close、volumeが該当する。配当記録はどちらにも属さない。支払額や配当履歴を返す属性はないため、利回りの分子は関数の外部から用意する必要がある。
配当利回りは、一株当たり年間配当を一株当たり株価で割ってパーセント表示したものだ。株価の部分は一つのセルで済む。手間がかかるのは配当の部分である。この比率自体が初めてなら、配当利回りが実際に示すものでスプレッドシートの操作に入る前の基本を確認できる。配当利回りの計算方法では計算手順を説明している。
以下が基礎となるデータである。ある一つの支払者が、おおむね過去三年間に行ったすべての支払いを示している。各行には権利落ち日と、一株当たりの支払額を並べている。権利落ち日とは、その日以降に株式を購入した投資家が、次回の配当を受け取れなくなる日である。
各数値の背後にある正確なSQL
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_dateCoca-ColaはSep 14, 2023に一株当たり$0.46、Jun 15, 2026に$0.53を支払った。この期間の支払いは12回である。チャートの二本目の線は、各支払額を四倍したものだ。その四半期の支払いが年間を通じて続いた場合に示される年間配当率である。年に一度段階的に上がり、その間は横ばいになる。この段階的な変化を反映しないことが、スプレッドシートの配当利回りで誤りが生じる主な原因である。全記録はCoca-Colaの配当履歴で確認できる。
Google Sheetsで配当利回りを算出する二つの方法
一つ目は、自分で作成した列を使うトレーリング利回りです。A列に権利落ち日、B列に1株当たりの現金配当を支払日ごとに1行ずつ入力し、次の式を使います。
- 過去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%と表示されるシートになります。
二つ目は、決定済みの配当金額から算出するフォワード利回りです。直近に決定された1株当たり配当金額をD2に、年間の支払回数をE2に入力します。
- フォワード利回り：
=D2*E2/GOOGLEFINANCE("KO","price")
D2を自動化する方法はありません。取締役会が配当金額を決定し、その発表資料から読み取って入力します。これがフォワード利回りの正確な扱いです。二つの分子のどちらを使うかが、トレーリング配当利回りとフォワード配当利回りの違いの核心です。
関数について、補足が二つあります。株価情報には最大20分の遅延があり、各セルの遅延時間は=GOOGLEFINANCE("KO","datadelay")で確認できます。また、過去の株価取得では数値ではなく2行2列の配列が返されるため、ラップ処理が必要です。=INDEX(GOOGLEFINANCE("KO","close",DATE(2026,6,30)),2,2)を使うと終値だけを取得できます。
年率換算に潜む誤り
配当データシートで最もよくある誤った計算は、直近の支払額を4倍する方法です。企業が増配するまでは機能します。しかし増配後は、直近12カ月の4回の支払いのうち3回が旧配当率で行われています。そのため、新しい支払額を4倍すると、株主が実際に受け取った金額を過大に見積もることになります。
以下のパネルでは、大型配当銘柄8銘柄について、この差を示しています。直近12カ月に1株当たり支払われた配当額と、直近の支払額を4倍した金額を並べています。
各数値の背後にある正確なSQL
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回で、1株当たり合計は$5.24でした。一方、直近の支払額を4倍すると$5.36となり、2.29%高くなります。配当利回りが3%近辺の銘柄では、この規模の誤差によって表示利回りが0.1ポイント以上動く可能性があります。銘柄の順位が入れ替わるには十分な差です。
支払回数の列は、乗算よりもSUMIFSを優先すべき理由を示す第二の要素です。直近12カ月の期間に、四半期配当が必ず4回入るとは限りません。権利落ち日は毎年数日ずれるため、期間によっては3回または5回の支払いが含まれます。合計額を確認する前に、どちらのケースかをCOUNTIFSで確認してください。
特別配当はランレートではない
もう一つの典型的な誤りは、一回限りの支払いを定期的な配当スケジュールの一部として扱うことだ。企業が余剰現金を一度に大きく分配すると、SUMIFSがその支払いを直近期間の合計に加え、利回りセルが跳ね上がる。その後、12カ月が経過すると、何事もなかったかのように元の水準へ戻る。
こうした支払いは珍しくない。このパネルでは、配当カレンダーが定期的なスケジュールではなく一回限りと示す米国市場の全支払いを、月ごとに集計している。
各数値の背後にある正確なSQL
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 monthJul 2026の直近の完了月では、111件の支払いに一回限りの印が付いていた。その月に権利落ちとなった全支払いの2.91%に相当する。12カ月移動合計にも、いずれか一つは遅かれ早かれ入り込む。
対策はフラグ列を設けることだ。各支払いの横にある列Cへregularまたはspecialを入力し、それを条件に加える。
=SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY(),$C$2:$C,"regular")
特別配当の行はシートに残すべきだ。実際に受け取った現金であり、受取実績の記録に含める必要がある。ただし、継続的な配当水準を示すための数値には含めてはならない。
完成したセルの出力
この方法で算出するトレーリング利回りには、異なるペースで動く二つの要素があります。配当合計は、支払いが対象期間に入るか外れるときや、増配が行われたときなど、年に数回変化します。一方、株価は毎セッション変動します。以下のパネルでは、同じ配当銘柄について、過去二年間の完成した計算結果を月ごとに示しています。
各数値の背後にある正確なSQL
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株当たり$1.89で、利回りは2.61%でした。Jul 2026時点では、合計は$2.08となり、利回りは2.37%でした。配当の列を誰も変更していないのに利回りのセルが変化するのは、トレーリング利回りの仕組みどおりです。分母が動いたためです。
一つのtickerで機能を確認したら、すべての保有銘柄についてブロックを下方向へコピーし、利回りを単純平均するのではなく、時価総額で加重して結果を集計します。この手順がポートフォリオ加重平均配当利回りです。
よくある質問
GOOGLEFINANCEに配当利回りの属性はありますか？
ありません。この関数が扱うのはリアルタイムの気配値項目と過去の価格で、配当や利回りに該当する項目はありません。=GOOGLEFINANCE("KO","yield")は#N/Aを返します。シート上の利回りは、入力した配当額と、関数が返す価格から算出します。
Google Sheetsで過去12カ月の配当利回りを計算するにはどうすればよいですか？
権利落ち日を一列目に、1株当たりの現金配当をその隣の列に入力します。=SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())で過去12カ月分を合計し、その合計を=GOOGLEFINANCE("KO","price")で割ります。結果のセルはパーセント表示に設定します。
配当利回りが証券会社のページと異なるのはなぜですか？
多くの場合、分子が異なるためです。予想利回りを表示するページでは、現在発表されている配当率を年率換算します。一方、12カ月分をSUMIFSで合計すると、旧配当率で支払われた配当を含む過去実績利回りになります。集計期間内に特別配当があると、差はさらに広がります。
特別配当を利回りから除外するにはどうすればよいですか？
各配当について、普通配当か特別配当かを示す列を追加します。その列を、SUMIFSの追加条件ペアとして指定します。特別配当の記録はシートに残しながら、利回りの計算からは除外できます。
Google Sheetsで配当履歴を自動取得できますか？
GOOGLEFINANCEでは取得できません。配当の列は手入力するか、支払記録を公開している情報源から貼り付けて管理します。このページの両方の方法で必要になる手作業は、この一つです。
ここに掲載した各パネルには、集計に使用したSQLが付属しています。パネルを開けば、合計の算出方法を正確に確認できます。同じ質問は、Strasmore terminalで平易な英語を使って尋ねることもできます。