【保存版】Googleスプレッドシートで米国株の年率（CAGR）を自動取得する方法

株式を長期保有する場合、

「直近〇年の平均リターンは？」

と「年率」で比較することは重要です。

今回は米国株式について、GoogleスプレッドシートとGOOLEFINANCE関数を使って年率（CAGR）を自動取得する方法を紹介します。

最終的にこのように取得できます

以下は完成のイメージです。
A列に銘柄（ティッカー）を入力すると、
B～E列にて直近1年、3年、5年、10年における年率を表示します。

使う数式はこれ

=IFERROR( 
    LET(
       ticker, "銘柄のセルを指定",
       year, "年数のセルを指定",
       startDate, EDATE(TODAY(),-12 * year),
       startData, GOOGLEFINANCE(ticker,"price",startDate,startDate+7),
       startPrice, INDEX(startData,2,2),
       nowPrice, GOOGLEFINANCE(ticker,"price"),
       ((nowPrice/startPrice)^(1/year)-1)
    ), 
    "-" 
)

この式により、「特定の銘柄（ティッカー）」の「n年間」における年率を取得することができます。

上に示した完成イメージの表で例を挙げると、
例えば、D4セル（MSFTの直近5年間における年率）にはこのように入力すればOKです。

=IFERROR( 
    LET(
       ticker, A4,
       year, D3,
       startDate, EDATE(TODAY(),-12 * year),
       startData, GOOGLEFINANCE(ticker,"price",startDate,startDate+7),
       startPrice, INDEX(startData,2,2),
       nowPrice, GOOGLEFINANCE(ticker,"price"),
       ((nowPrice/startPrice)^(1/year)-1)
    ), 
    "-" 
)

コードの具体的な説明

LET関数の役割

LET()は、数式の中で「変数」を作ることができる関数です。

=LET(名前1, 値1, 名前2, 値2, ... , 計算式)

のように使い、最後の引数である[計算式]の部分を出力します。

今回、LETを使って、ticker, year, startDate, startData, startPrice, nowPriceという変数を作ります。

それぞれの変数は以下の役割があります。

ticker：
銘柄のティッカーシンボルを格納する変数です（例：AAPL、MSFT、VOO など）。
この値をもとに GOOGLEFINANCE が株価データを取得します。

year：
何年分の年率を計算するかを指定する変数です。

startDate：
「○年前の日付」を計算して格納する変数です。
EDATE(TODAY(), -12 * year) によって、指定年数だけ過去にさかのぼった日付を作ります。
EDATE関数 は指定した日から数ヶ月前または数ヶ月後の同日の日付を返します。よって第二引数には月数が入ります。

startData：
GOOGLEFINANCE(ticker,"price",startDate,startDate+7) によって、数年前の日付（startDate）付近の株価データ（数日分）を取得する変数です。配列を返します。
※株式市場は土日・祝日が休みのため、確実に営業日の価格を取得できるよう、1週間分の範囲でデータを取得しています。次のstartPriceで、この中から一番古い日付の株価を取得します。

startPrice：
○年前の株価を格納する変数です。
INDEX(startData,2,2) によって、startData配列の中から一番古い日付の価格のみを抜き出しています。

nowPrice：
現在の株価を格納する変数です。
GOOGLEFINANCE(ticker,"price") によって、リアルタイムに近い価格を取得します。

ここまでで、〇年前の株価（startPrice）と現在の株価（nowPrice）を取得できました。

LETの最後の引数である((nowPrice/startPrice)^(1/year)-1)が返り値となり、これが年率を表しています。

IFERROR関数の役割

IFERROR( 計算式 , "-" ) によって、エラーが出たときに、"-"を表示するようにしています。

GOOGLEFINANCEはよくエラーを出します。
また、〇年前に上場していない場合は株価を取得できないため、これもエラーとなります。

その際、#N/Aや#VALUE!などと表示されると見栄えが悪いため、IFERROR関数を用いています。

仕組みは“資産”になる

株式投資の分析において、「仕組み」を持っているかどうかは重要です。

多くの人は、「5年で2倍」のようにリターンを“倍率”でとらえがちですが、

それは年率で何％なのか？

という視点で見られるようになると、投資の質は大きく変わります。

このシートを使えば、

• 10年間で年率15%以上を達成している銘柄がある。
• S&P500を継続的に上回っている銘柄がある。

といった事実も見えてきます。

また、一度作れば、Googleの仕様が変わらない限り継続的に使い続けられます。