こんにちは。『お金に困らない生活（インデックス投資ブログ）』管理人のそーたろー（@sotarowassyoi）です。
ここはどんなブログなの？
- お金、投資、資産運用、副業が中心のブログです。
この記事を書いたそーたろーはこんな人です。
- 2008年から国内・海外ETF、つみたてNISA、iDeCoなどでインデックス投資をしています。
- 2020年より米国株オプション、サラリーマン大家、副業ブログを実験中です。
この記事は次のような人にオススメです
- 表計算ソフトを使った複利計算の方法が知りたい人
この記事の目的
私は資産運用としてインデックス投資を行っています。
インデックス投資では数十年という長期間に渡って資金を株式ファンドなどのリスク資産に投資し続けます。
投資期間中は一定の利回りを得ながら複利で資産を増やすことが狙いの投資手法になります。
数十年という遠い将来のことは正確にはわからないものの、インデックス投資では過去の平均的なリターンを用いて将来の見込みをざっくり把握しようとすることが多々あります。
複利の計算自体は得られた利益を元本に再投資していくという単純なものです。
でも途中で追加投資したり、いくつかのパターンを組み合わせたりして複雑になるケースもあるので、そういうときは表計算ソフトがよく用いられます。
今回は過去に検証したインデックス投資の複利運用データをGoogleスプレッドシートを使って計算する方法について解説します。
なお保有中の商品をGoogleスプレッドシートを使って一元管理する方法についての解説記事もありますのでよろしければどうぞ。
説明は細かいですが、そんなに難しくないですよ
今回の解説内容について
以下の記事で使用した複利運用検証データの作成方法について解説します。
ですのでお読みいただく方は、検証パターンなどの理解のためにまず最初に上記記事をご参照いただくのがよいと思います。
簡単に説明すると、35歳の人が30年、45歳の人が20年、それぞれ65歳まで株式インデックスで年利回り5%で運用する場合の想定リターンのお話です。
初期投資あり/なし、つみたてNISA、iDeCoを使う5つのパターン、各年齢で計10パターンの検証になります。
今回の記事はGoogleスプレッドシートを使って10パターンの結果はどうやって求まるの？について解説します。
なお上記の元記事でもお断りしていますが、試算は年一括投資のみなし計算としています。
もし毎月つみたて拠出の正確な複利計算をしたい場合は、この記事の後半で紹介するFV関数を使った方法も参考にしてください。
Googleスプレッドシートによる投信の複利リターン計算
始める前に、この解説を理解するために必要な前提知識について触れておきます。
これらの知識は資産運用を行っていく上で役に立ちますから、怪しい場合はしっかり学習して行きましょう。
それでは見ていきましょう。
初期投資のみのパターン
No.1、No.6は初期投資のみで、最初に投じた資金に対して年利回り5%の計算をしていきます。
初期投資はNo.1が500万円、No.6が1,000万円です。
No1.を例に説明します。
D2セルで年数の”1″年目に”500″万円を投じます。
2年目のD3セルは525万円となっており、D3セルに次の計算式を入れた計算結果です。
計算式の意味は、D2セルの値”500″とC2セルの値”1.05″を”*”、つまり掛け算しなさい、ということです。
これでD3セルは年数の”2″年目に1年目の500万円が5%増えて”525″万円となります。
D4セル以降はD3セルと同じ計算式なので、41年目までD4セルをコピー＆貼り付けしてNo.1は完了です。
No.6では初期投資が”1,000″万円に変わっただけなので同じ要領でI13セルは以下の計算式になります。
I14セル以降は41年目までI13セルをコピー＆貼り付けします。
これでNo.1、No.6の複利計算は完了です。
このパターンでは毎年、前年の資産に1.05を掛けることになるので年利回り5%複利で資産が増えることになります。
iDeCoのみのパターン
No.2、No.7はセルが青色ですのでiDeCoのみのパターンになります。
先程のNo.1、No.6と違って毎月23,000円、12ヶ月で276,000円の追加投資をしながら年利回り5%の運用をします。
No2.を例に説明します。
E2セルで年数の”1″年目に”27.6″万円を投じます。
2年目のE3セルは56.6万円となっており、E3セルに次の計算式を入れた計算結果です。
E3セルは年数の”2″年目に”27.6″万円を投じ、さらに1年目の27.6万円が5%増えた値が加算されます。
これでE3セルの値は”56.6″万円となります。
E4セル以降はE3セルをコピー＆貼り付けします。
なお追加投資の終わりを65歳までとしていますので、65歳の31年目からは27.6万円の追加投資を行わないためE32セルは以下の計算式に変わります。
E33セル以降はE32セルをコピー＆貼り付けします。
No.7も同じ要領で計算します。
これでNo.2、No.7の複利計算は完了です。
このパターンではiDeCoで毎年27.6万円を追加投資しながら、前年までの資産を年利回り5%で増やして加算していますね。
つみたてNISAのみのパターン
No.3、No.8はセルが黃色ですのでつみたてNISAのみのパターンになります。
先程のNo.2、No.7と同様に今度は12ヶ月で400,000円の追加投資をしながら年利回り5%の運用をします。
No3.を例に説明します。
F2セルで年数の”1″年目に”40″万円を投じます。
2年目のF3セルは82万円となっており、F3セルに次の計算式を入れた計算結果です。
F3セルは年数の”2″年目に”40″万円を投じ、さらに1年目の40万円が5%増えた値が加算されます。
これでF3セルの値は”82″万円となります。
F4セル以降は23年目までF3セルをコピー＆貼り付けします。
24年目からは追加投資を行いませんので、F25セルは以下の計算式に変わります。
F26セル以降はF25セルをコピー＆貼り付けします。
No.8も同じ要領で計算しますが、追加投資は65歳までなのでNo3.と違い追加投資の期間が20年で終了となります。
これでNo.3、No.8の複利計算は完了です。
このパターンではつみたてNISAで毎年40万円を追加投資しながら、前年までの資産を年利回り5%で増やして加算していますね。
つみたてNISAとiDeCoのパターン
No.4、No.9はセルが赤色ですのでつみたてNISAとiDeCo両方を使うパターンになります。
先程のNo.2、No.7およびNo.3、No.8と同様に今度は12ヶ月で276,000円＋400,000円＝676,000円の追加投資をしながら年利回り5%の運用をします。
No4.を例に説明します。
G2セルで年数の”1″年目に67.6万円を投じますが、以下の計算式を使い、表示は千円の桁を四捨五入しているため”68″万円となります。
2年目のG3セルは139万円となっており、G3セルに次の計算式を入れた計算結果です。
G3セルは年数の”2″年目に”67.6″万円を投じ、さらに1年目の67.6万円が5%増えた値が加算されます。
これでG3セルの値は”139″万円となります。
G4セル以降は23年目までG3セルをコピー＆貼り付けします。
なおつみたてNISAの追加投資は23年目までになり、24年目から30年目までiDeCoのみの追加投資となるためセルが青色になり、G25セルは以下の計算式に変わります。
G26セルからG31セルまでG25セルをコピー＆貼り付けし、65歳までiDeCoで追加投資とします。
さらに31年目からは追加投資を行いませんので、G32セルは以下の計算式に変わります。
G33セル以降はG32セルをコピー＆貼り付けします。
No.4は以上です。
No.9についてはNo4.と違い、つみたてNISAのみでつみたて終了の65歳に達してしまいますのでiDeCoの期間がありません。
このパターンではiDeCoとつみたてNISAで毎年67.6万円を追加投資しながら、前年までの資産を年利回り5%で増やして加算しています。
初期投資＋つみたてNISAとiDeCoのパターン
No.5、No.10は初期投資ありで、追加投資はつみたてNISAとiDeCo両方を使うパターンになります。
No5.を例に説明します。
H2セルで年数の”1″年目に以下の計算式により500万円＋27.6万円＋40万円の計”568″万円を投じます。
H3の2年目以降はNo.4、No.9のパターンと同様に、67.6万円の追加投資に加え、前年投じた投資額に5%増えた値を加算していきます。
追加投資の終わり方についてはNo.4、No.9のパターンと同じです。
このパターンでは初期投資に加え、iDeCoとつみたてNISAで毎年67.6万円を追加投資しながら、前年までの資産を年利回り5%で増やして加算しています。
という感じで、以上がNo.1〜No.10までの複利計算の求め方の解説になります。
こ、こんな説明でわかってもらえただろうか
【おまけ１】電卓を使った複利計算
追加投資が組み合わわさるような複利計算は表計算ソフトが便利ですが、No.1、No.6（初期投資のみ）のようなパターンであれば電卓で簡単に計算することができます。
例えば以下の計算式を入力して、知りたい年数分繰り返しEnterキーを叩くだけです。
30回叩けば2,160.97で30年後は2,161万円とわかりますね。
【おまけ２】表計算ソフトの関数を使った計算
Googleスプレッドシートの関数を使っても計算できます。
FV関数で複利計算する
今回紹介したGoogleスプレッドシートを使った時系列で計算するやり方に、複利計算ができるFV関数を組み合わせてもう少し正確な試算をすることもできます。
FV関数の数式と引数は以下のとおりです。
例えばNo.2のところで以下の計算式を使っていますが、
これを以下のようにします。
上記のFV関数の例では毎月拠出の年5%複利の正確な計算になります。
この例で計算していくとNo.2の30年目は以下のような試算の違いが出ます。
FV関数では月単位で複利計算しますので、30年で2.9万円増えています。
必要に応じて使い分けるとよいでしょう。
FV関数の使い方をまとめた以下の記事もどうぞ。
RRI関数で複利の利回りを求める
また複利運用の結果から利回りを求める場合はRRI関数を使います。
先程の結果1,628,894.63円の10年複利の利回りを逆算する場合は以下のとおりです。
これで0.05が求まります。
RRI関数の使い方をまとめた以下の記事もどうぞ。
【余談】表計算ソフトの操作
今回の解説ではGoogleスプレッドシートで簡単な計算を行い、計算式を別のセルにコピーして相対参照で使用しています。
こうした使い方は表計算ソフトの基本的な操作になると思います。
以前、以下の記事で「MOSは役に立たない資格だ」と書きました。
理由は記事のとおり、表計算ソフトの操作は掛け算九九みたいなものだからです。
一方で、以下でおすすめしている基本情報技術者試験の午後試験には、開発言語の選択問題で表計算ソフトを選ぶことができます。
基本情報技術者試験では2019年1月に開発言語の選択問題に対して出題範囲の大幅な変更を発表しています。
この変更では時代の流れに即し古い開発言語を止めて、新しい開発言語を取り入れるということでCOBOLの廃止、Pythonの追加となっています。
しかし依然として表計算ソフトは選択問題に残っていることから、表計算ソフトの開発スキル（VBAとか）についてはまだまだ現役ということですね。
MOSと基本情報技術者の午後問題とではレベル感の違う話ではありますが、表計算ソフトは広く普及しているのでスキルとしては大切だよなー、と思い今回の解説記事を作ったのでした。
まとめ
Googleスプレッドシートを使って投信の将来リターンを計算する方法を解説しました。
所詮は遠い将来のことですから、計算どおりにはいきません。
しかしこうした検証を自分で行うことができれば、どのような資産運用をすればよいのかを自分でデザインすることができます。
説明を記事にすると長くて大変に感じますが、実際は簡単な計算式をコピーして使い回すだけなのでそこまで難しくないと思います。
今回解説したようなやり方は簡易的な方法ではありますが、自分のやっている資産運用を理解する意味でも大切なのではないでしょうか。
