以前の記事で、意外と簡単に作れる積立投資のシミュレーション方法をお伝えしました。
ただこのシミュレーションは、年利が一切変わらず常に一定である前提でのシミュレーションになっています。
けど、現実ではそんなこと絶対にありえません。
実際は１日どころか１分レベルで株価が変動するため、年利も毎年必ず変わりますし、
毎月の積立投資であれば、積立数や積立金額も毎月変動します。
（※定額積立なら積立数が、定数積立なら積立金額が変動します）
それだけ大きく変動するため、将来の株価がどうなっていくのかは絶対に分からないのですが、
過去のデータを使えば、
もし過去に積立投資を初めていたらどうなっていたのか？
というシミュレーションは可能です。
そこで今回は、積立投資で最近有名になっているアメリカの株価指数 S&P500 の過去チャートを使って、
もし過去にS＆P500に積立投資していたらどうなっていたのか
というシミュレーションをしていきたいと思います。
【本記事の目標】
S&P500の過去チャートを使って、
積立投資のシミュレーションをしてみよう
まずはS&P500の過去チャートデータを取得しよう
S&P500の過去チャートを取得できるサイトはいくつかありますが、
オススメは
「 Stoop.com 」
です。
このサイトはS&P500の過去チャートを無料で確認できるだけでなく、CSVファイルとしてもダウンロードできるため、今回のように過去データを使って色々な検討をしていくうえでは非常にありがたいです。
Stoop.comからチャートをダウンロード
上記リンクに飛んでもらったら、直接ダウンロード直前の画面に行けますが、念のため「Stoop.com」のメインページからの行き方も説明します。
https://stooq.com/
のメインページの上にある検索ボックスに「^spx」を入力してエンターを押します。
するとS&P500のページに行きますので、そこで左下にあるメニュー選択部分から「Historical data」をクリックします。
するとデフォルトで1789年～のデータが表示されます。
ここで、取得したいデータ間隔等を設定してデータをダウンロードしていきます。
今回は日毎のデータを取得しようと思いますので、「Daily」を選択して「Show」をクリックします。
表示したデータをダウンロードする場合は、表示されている表の最後にある
「Download data in csv file …」をクリックします。
これでシミュレーションに必要なデータの取得は完了です。
Excelを使ってシミュレーションの準備
それではダウンロードしたデータを使ってシミュレーションを作っていきましょう。
ただ、ダウンロードしたファイルはデータ数が非常に多く、そのまま使ってシミュレーションをすると通常のパソコンだとまともに動作しなくなる可能性が高いため、快適に動作できるようにデータの圧縮をしていきます。
手順としては下記の３ステップです。
- 古すぎるデータを削除する
- 毎日の株価の変動を平均化して月平均の株価に変更
- 月初日のデータだけを残して他を削除（月ごとのデータに）
データ圧縮Step１：古すぎるデータを削除
まずは古すぎるデータを削除していきます。
ダウンロードしたデータを見ていただくと、『1789年5月』からのデータがあります。
200年以上も前のデータですので、さすがに今と時代が変わりすぎているためシミュレーションをしてもあまり意味がないと思います。
1900年以降（100年前ぐらい）であれば多少は参考になる可能性があるので、一旦1900年以降のデータだけ残して、後はすべて削除しておきます。
（2行目～5639行目を削除します）
データ圧縮Step２：月平均株価の計算
1900年以降はデータがさらに細かくなっており、おおよそ1日ごと（恐らく休日のデータは無いため一部飛び飛び）のデータになっています。
100年分×約365日＝約36500個のデータを取り扱うと、パソコンにそこそこ負荷がかかってくるので、毎日のデータではなく毎月のデータに圧縮していきたいと思います。
（100年分×12カ月＝約1200個のデータ）
毎月データに圧縮するために、まずは毎日のデータを平均して月平均の株価を計算していきます。
計算に使うのは、YEAR関数、MONTH関数、AVERAGEIF関数 です。
月の平均値を出すためには、各行のデータが『何年何月』なのか確認する必要があります。
A列に日付データがあるのですが、このままだと何日のデータも含まれてしまっているので、
ここから『何年』と『何月』のデータを取り出します。
そこで使うのが YEAR関数とMONTH関数です。
これで『何年』と『何月』のデータを取り出せたので、
後でAVERAGEIF関数で判定ができるように、下記のように1つにまとめておきます。
あとはI列のデータとE列のデータを使って、AVERAGEIF関数で各月の平均株価を計算していきます。
（※この計算を全行に実施すると計算が完了するまで少し時間がかかりますのでご注意ください。）
ちなみにAVERAGEIF関数については下記投稿で詳細説明をしていますので、
ご参考ください。
ダウンロードしたデータのB・C・D・E列は、それぞれ「Open」「High」「Low」「Close」になっています。
これらは全てその日の株価を示しており、
Open：その日の取引開始時の株価
High：その日の株価の最高値
Low：その日の株価の最低値
Close：その日取引終了時の株価
となっています。
今回の目的（積立投資のシミュレーション）であれば、
月平均の株価を計算する場合、OpenかCloseどちらかを使えば問題ないと思います。
これでJ列に月平均の株価を計算できたのですが、
この後データを圧縮していく際に計算式のまま残しておくとファイル自体が重くなりますし、
数値も動いてしまうので、下記手順で計算式を数値に変換しておきます。
データ圧縮Step３：月ごとデータに圧縮
月平均の株価が計算できれば、
後は各月のどれか1日のデータだけ残して他は削除してしまってOKです。
1日のデータだけ残して他を削除しようと思うと、フィルターを使って例えば毎月15日のデータ以外を削除する、というのが一番簡単な方法ですが、
このデータは株式取引のない日のデータが無いため、15日のデータが無い月というのが出てきます。
その場合、フィルターを使うとその月のデータが全て消えてしまうため使えません。
そこで、MATCH関数を使ってその月の最初の取引日かどうかの判別をします。
後はフィルターを使って、K列の「×」だけを表示させて、
全てを行ごと削除すれば、月初日のデータだけが残るようになります。
最後にフィルターを外せば、下記のように、
月平均の株価データを計算したうえで毎月の月初日のデータだけが残る状態になります。
ここまでの作業は使用しているパソコンに寄っては計算にかなり時間がかかる場合があります。
ただこの作業をしておけば、以降は快適に計算ができると思います。
Excelのシミュレーションシートを作成
計算に使う基データが完成したので、このデータを使ってシミュレーションシートを作っていきます。
シミュレーションシートの全体像
最終完成形のシミュレーションシートは下記のようになります。
以降でこのシート詳細を説明していきます。
（下記図の青字部分と投資結果部分が全て計算式になっています。）
基本情報入力部（シミュレーション条件設定）
シミュレーションをするために、
どんな条件でシミュレーションをさせるかの条件入力をする部分を用意します。
それが下記部分です。
まずは、積立投資を何年何月から開始したと想定するかを①に記入します。
また、その積立投資を何年間行ったかを②に記入します。
さらに、積立投資を行った後、実際にその投資額を回収した（取り崩しを行った）タイミングを③に記入します。
シミュレーションをするために必要な設定は上記の３点のみです。
後はこの情報を使って、基データから必要な計算を行っていきます。
計算部分
基データから該当データ抽出
まずは基データの中から、実際に投資を行った期間のデータだけを取り出します。
対象の期間を取り出すために、その期間が基データのどこにあるかを計算する必要があります。
その計算をしている部分が下記です。
それぞれの欄に入力している計算式は下記です。
①：=IF(OR(D4=””,E4=””),””,D4&”.”&TEXT(E4,”00″))
（入力値を「年.月」形式に変換。どちらか空欄の場合は表示しない。）
②：=MATCH(H5,基データ!I:I,0)-1
（①の内容が基データのI列の何行目にあるか確認）
③：=D6*12
（投資期間が何カ月（基データの何行分）に相当するか計算）
この計算結果を使えば、対象のデータがどこにあって、どのぐらいのデータを取り出せばいいかが分かります。
例えば上記の図の場合、『2015年1月』のデータは1377行目にあるため、そこから5年分のデータを取り出すには1377行目～1437行目（1377+60行目）のデータを取り出せば、投資期間のデータを取り出すことが出来ます。
実際に取出しを行っている部分が下記です。
上記欄にはほぼ同じ計算式が入力されています。
下記はO列に記載の計算式です。
=IF($N3>$J$5,””,OFFSET(基データ!$G$1,$I$5+$N3-1,0))
OFFSET関数の参照セルを取り出したいデータの1行目に設定しておけば、基データから好きなデータを取り出すことが出来ます。
またこの計算式は基データのデータ数（約1200行）分だけ下にコピーしておく必要があります。
定数積立の場合の計算
積立方法は大きく分けると２つあり、定数積立と定額積立があります。
定数積立は、株価がどんな額であろうと毎月同じ数の株を購入する方法で、毎月の積立額が変動します。
対して定額積立は、常に一定の額で積み立てるため、購入する株数が毎月変動します。
このように積立方法で若干計算方法が変わってくるため、シミュレーションもそれぞれで実施する必要があります。
定数積立の場合、基データに記載済みの月平均株価のデータを使えばシミュレーションが可能です。
単純に毎月１株ずつ購入したと考えれば良いので、毎月の積立額は月平均株価を合計していけばOKです。
購入した株の総数は、積み立てを行った月数に相当するので、上記データだと行数がそのまま株数になります。
この株数に、入力した取り崩しタイミングでの株価を掛けてあげれば、取り出し額になります。
そのためには取り崩しタイミングでの株価が必要ですが、それは下記部分で計算しています。
取出し額-積立額の差分が、この積立投資によるリターンになります。
定数積立の最終結果表示
抽出した対象期間のデータ全てに上記の計算を行い、最終の結果を記載しているのが下記部分です。
それぞれの項目の意味と計算式は下記の通りです。
■総積立額：最終的に投資した総額
=OFFSET($S$2,$D$6*12,0)
■取出し総額：指定したタイミングで全額を回収した際の総額
=OFFSET($T$2,$D$8*12,0)
■総リターン：取出し総額-総積立額、積立投資によって得た利益
=D14-D13
■UP率：投資額に対して何％資産が増えたか
=D15/D13
■平均年利：1年あたりの年利
=D16/D8
またグラフについては、Q列をx軸に、Ｒ列・S列・T列をy軸にしています。
定額積立の場合の計算
先ほどは定数積立でシミュレーションをしましたが、
実際に積立投資をする場合は定額積立をする場合の方が多いと思います。
（定額積立の方が月々の出費を計算しやすいため実際にするならこちらがオススメ）
定額積立の計算をする場合は、
『月々の投資額が１だった時に何株購入できるか』
を事前に計算しておく必要があります。
上記の計算をあらかじめ基データのシートに追加しておけば、
後は定数積立の場合とほぼ同じような計算になっていきます。
この定額購入した場合の毎月の株数計算していけば、定額積立のシミュレーションが可能です。
定額積立の場合は毎月１だけ積み立てていくので、経過した月数＝積立額になります。
ただ、購入した株数が毎月変動するので、購入した株数の合計は計算しておく必要があります。
指定したタイミングで取り崩しを行う場合、ここで計算した購入株数の合計に取り崩しタイミングでの株価を掛けてあげれば計算が可能です。
取り崩しタイミングでの株価は定数積立の際に計算したものと同じでOKです
定額積立の最終結果表示
抽出した対象期間のデータ全てに上記の計算を行い、最終の結果を記載しているのが下記部分です。
それぞれの項目の意味と計算式は下記の通りです。
（項目の意味は定数積立の場合と同じです）
■総積立額：最終的に投資した総額
=OFFSET($W$2,$D$6*12,0)
■取出し総額：指定したタイミングで全額を回収した際の総額
=OFFSET($X$2,$D$8*12,0)
■総リターン：取出し総額-総積立額、積立投資によって得た利益
=I14-I13
■UP率：投資額に対して何％資産が増えたか
=I15/I13
■平均年利：1年あたりの年利
=I16/D8
またグラフについては、Q列をx軸に、Ｒ列・W列・X列をy軸にしています。
実際にこのシミュレーションを使って計算してみる
このシミュレーションの中身を説明してきましたが、
実際に使ってみないとよく分からないですよね。
ということで、実際にこれを使って計算をしてみましょう。
Case1. 2010年1月から10年間積み立てをしていたら？
今から14年前、2010年から10年間積み立てをしていたら、
積立投資でどんな利益が出ていたのでしょうか？
まずは情報入力エリアに下記のように情報を入力します。
その結果は下記の通りです。
定額積立の場合、総額で$120を投資していたら取り出したときには$213になっており、
78%も増額。平均年利でいくと8%になりました。
（S&P500はアメリカの指標のためドル（＄）で書いています）
Case2. 25年前から今まで投資していたら？
25年前に投資を開始していて、もし今取り崩したらどうなっていたのでしょうか。
25年前は1999年です。
この場合の結果は下記の通りです。
定額積立の場合、総額で$300を投資していたら取り出したときには$903になっており、
増額率は驚異の201%！単純に投資額が3倍になって帰ってきました。
ダウンロード
このように、このシミュレーションを使えば、
もし過去にS&P500を指標とするインデックス投資に積立投資していたらどうなっていたのか、
を見ることが出来ます。
過去に戻ることはできませんが、
新NISAも始まりましたし、今後積立投資をしていくうえで参考にしてもらったらいいかと思います。
今回作ったシミュレーションのExcelファイルは下記にありますので、
ご自由にダウンロードしてみて、お好きな時代でのシミュレーションをしてみてください。
コメント