【お役立ち情報】投信の複利リターン計算方法【Googleスプシ】

インデックス投資
インデックス投資

こんにちは、そーたろー(@sotarowassyoi)です。

 

  • Googleスプレッドシートを使って投信やETFの複利運用によるリターンを簡単に計算する方法を紹介します。
  • 初期投資あり/なし、つみたてNISA、iDeCoの各パターンの計算方法を解説します。

 

そーたろー
そーたろー

ここはどんなブログなの?

  • お金、投資、資産運用、副業が中心のブログです。

 

そーたろー

この記事を書いたそーたろーはこんな人です。

  • 2008年から国内・海外ETF、つみたてNISA、iDeCoなどでインデックス投資をしています。
  • 2020年より米国株オプション、サラリーマン大家、副業ブログを実験中です。

 

そーたろー

この記事は次のような人にオススメです

  • 表計算ソフトを使った複利計算の方法が知りたい人

 

スポンサーリンク

この記事の目的

私は資産運用としてインデックス投資を行っています。

インデックス投資では数十年という長期間に渡って資金を株式ファンドなどのリスク資産に投資し続けます。

投資期間中は一定の利回りを得ながら複利で資産を増やすことが狙いの投資手法になります。

数十年という遠い将来のことは正確にはわからないものの、インデックス投資では過去の平均的なリターンを用いて将来の見込みをざっくり把握しようとすることが多々あります。

複利の計算自体は得られた利益を元本に再投資していくという単純なものです。

でも途中で追加投資したり、いくつかのパターンを組み合わせたりして複雑になるケースもあるので、そういうときは表計算ソフトがよく用いられます。

今回は過去に検証したインデックス投資の複利運用データをGoogleスプレッドシートを使って計算する方法について解説します。

なお保有中の商品をGoogleスプレッドシートを使って一元管理する方法についての解説記事もありますのでよろしければどうぞ。

投信・ETFの評価損益を自動計算する方法【Googleスプシ】
Googleスプレッドシートを使って、複数の口座で保有する投信・ETFを一元管理する方法を紹介します。 関数を使って株価や為替レートを取得し、ポートフォリオの評価損益を自動計算して管理をラクにする方法を解説しま...

 

そーたろー
そーたろー

説明は細かいですが、そんなに難しくないですよ

 

今回の解説内容について

以下の記事で使用した複利運用検証データの作成方法について解説します。

老後資金準備は年利回り5%の『自分年金作り』がおすすめ【実践中】
私は2008年から老後資金の準備としてインデックス投資による『自分年金作り』を続けています。 つみたてNISA・iDeCoで年利回り5%程度を想定するのがリスク管理上、安心できるレベルの運用としておすすめです。...

ですのでお読みいただく方は、検証パターンなどの理解のためにまず最初に上記記事をご参照いただくのがよいと思います。

簡単に説明すると、35歳の人が30年、45歳の人が20年、それぞれ65歳まで株式インデックスで年利回り5%で運用する場合の想定リターンのお話です。

初期投資あり/なし、つみたてNISA、iDeCoを使う5つのパターン、各年齢で計10パターンの検証になります。

今回の記事はGoogleスプレッドシートを使って10パターンの結果はどうやって求まるの?について解説します。

なお上記の元記事でもお断りしていますが、試算は年一括投資のみなし計算としています。

もし毎月つみたて拠出の正確な複利計算をしたい場合は、この記事の後半で紹介するFV関数を使った方法も参考にしてください。

 

スポンサーリンク

Googleスプレッドシートによる投信の複利リターン計算

始める前に、この解説を理解するために必要な前提知識について触れておきます。

  • 複利の概念
  • インデックス投資と次の制度の概要
    • つみたてNISA
    • iDeCo
  • 表計算ソフトで次のことを理解していること
    • 計算式(加算、乗算)
    • セルの相対参照

これらの知識は資産運用を行っていく上で役に立ちますから、怪しい場合はしっかり学習して行きましょう。

それでは見ていきましょう。

 

初期投資のみのパターン

No.1、No.6は初期投資のみで、最初に投じた資金に対して年利回り5%の計算をしていきます。

初期投資はNo.1が500万円、No.6が1,000万円です。

No1.を例に説明します。

D2セルで年数の”1″年目に”500″万円を投じます。

2年目のD3セルは525万円となっており、D3セルに次の計算式を入れた計算結果です。

=D2*C2

計算式の意味は、D2セルの値”500″とC2セルの値”1.05″を”*”、つまり掛け算しなさい、ということです。

これでD3セルは年数の”2″年目に1年目の500万円が5%増えて”525″万円となります。

D4セル以降はD3セルと同じ計算式なので、41年目までD4セルをコピー&貼り付けしてNo.1は完了です。

No.6では初期投資が”1,000″万円に変わっただけなので同じ要領でI13セルは以下の計算式になります。

=I12*C12

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セルに次の計算式を入れた計算結果です。

=27.6+E2*C2

E3セルは年数の”2″年目に”27.6″万円を投じ、さらに1年目の27.6万円が5%増えた値が加算されます。

これでE3セルの値は”56.6″万円となります。

E4セル以降はE3セルをコピー&貼り付けします。

なお追加投資の終わりを65歳までとしていますので、65歳の31年目からは27.6万円の追加投資を行わないためE32セルは以下の計算式に変わります。

=E31*C31

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セルに次の計算式を入れた計算結果です。

=40+F2*C2

F3セルは年数の”2″年目に”40″万円を投じ、さらに1年目の40万円が5%増えた値が加算されます。

これでF3セルの値は”82″万円となります。

F4セル以降は23年目までF3セルをコピー&貼り付けします。

23年目まで貼り付ける理由は、現行のつみたてNISAはつみたて期間が20年ですが、法改正で2024年からは期間が5年延長されることが決まっており、2020年から始めた人は23年間つみたて可能となるためです。
この説明だけではわからないかもしれませんが、要するに制度がビミョーに難解ということですね。

24年目からは追加投資を行いませんので、F25セルは以下の計算式に変わります。

=F24*C24

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″万円となります。

=27.6+40

2年目のG3セルは139万円となっており、G3セルに次の計算式を入れた計算結果です。

=67.6+G2*C2

G3セルは年数の”2″年目に”67.6″万円を投じ、さらに1年目の67.6万円が5%増えた値が加算されます。

これでG3セルの値は”139″万円となります。

G4セル以降は23年目までG3セルをコピー&貼り付けします。

なおつみたてNISAの追加投資は23年目までになり、24年目から30年目までiDeCoのみの追加投資となるためセルが青色になり、G25セルは以下の計算式に変わります。

=27.6+G24*C24

G26セルからG31セルまでG25セルをコピー&貼り付けし、65歳までiDeCoで追加投資とします。

さらに31年目からは追加投資を行いませんので、G32セルは以下の計算式に変わります。

=G31*C31

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″万円を投じます。

=500+27.6+40

H3の2年目以降はNo.4、No.9のパターンと同様に、67.6万円の追加投資に加え、前年投じた投資額に5%増えた値を加算していきます。

=67.6+H2*C2

追加投資の終わり方についてはNo.4、No.9のパターンと同じです。

このパターンでは初期投資に加え、iDeCoとつみたてNISAで毎年67.6万円を追加投資しながら、前年までの資産を年利回り5%で増やして加算しています。

という感じで、以上がNo.1〜No.10までの複利計算の求め方の解説になります。

 

そーたろー
そーたろー

こ、こんな説明でわかってもらえただろうか

 

【おまけ1】電卓を使った複利計算

追加投資が組み合わわさるような複利計算は表計算ソフトが便利ですが、No.1、No.6(初期投資のみ)のようなパターンであれば電卓で簡単に計算することができます。

例えば以下の計算式を入力して、知りたい年数分繰り返しEnterキーを叩くだけです。

500×1.05

30回叩けば2,160.97で30年後は2,161万円とわかりますね。

 

スポンサーリンク

【おまけ2】表計算ソフトの関数を使った計算

Googleスプレッドシートの関数を使っても計算できます。

FV関数で複利計算する

今回紹介したGoogleスプレッドシートを使った時系列で計算するやり方に、複利計算ができるFV関数を組み合わせてもう少し正確な試算をすることもできます。

FV関数の数式と引数は以下のとおりです。

=FV(利率, 期間, 定期支払額, 現在価値, 支払期日)

例えばNo.2のところで以下の計算式を使っていますが、

=27.6+E2*C2

これを以下のようにします。

=FV(5%/12,12,-2.3,-E2)

上記のFV関数の例では毎月拠出の年5%複利の正確な計算になります。

この例で計算していくとNo.2の30年目は以下のような試算の違いが出ます。

  • みなし計算:1833.7万円
  • FV関数:1836.6万円

FV関数では月単位で複利計算しますので、30年で2.9万円増えています。

必要に応じて使い分けるとよいでしょう。

FV関数の使い方をまとめた以下の記事もどうぞ。

FV関数を使ったつみたて投資の複利計算【Googleスプシ】
GoogleスプレッドシートのFV関数を使ったつみたて投資の複利計算を紹介します。 初回一括投資、毎月つみたて(初期投資なし)、毎月つみたて(初期投資あり)の3パターンの解説をします。 ...

 

RRI関数で複利の利回りを求める

また複利運用の結果から利回りを求める場合はRRI関数を使います。

=RRI(期間, 現在価値, 将来価値)

先程の結果1,628,894.63円の10年複利の利回りを逆算する場合は以下のとおりです。

=RRI(10,1000000,1628894.63)

これで0.05が求まります。

RRI関数の使い方をまとめた以下の記事もどうぞ。

RRI関数を使ったETFの複利利回り計算【Googleスプシ】
GoogleスプレッドシートのRRI関数を使ってETFの過去のパフォーマンスから複利利回りを計算する方法を紹介します。 RRI関数を使った米国ETFの商品選定のコツも紹介します。 ...

 

【余談】表計算ソフトの操作

今回の解説ではGoogleスプレッドシートで簡単な計算を行い、計算式を別のセルにコピーして相対参照で使用しています。

こうした使い方は表計算ソフトの基本的な操作になると思います。

以前、以下の記事で「MOSは役に立たない資格だ」と書きました。

取得したけど微妙だった資格3つ、役に立たなそうな資格3つ【毒舌】
取得したけど微妙だった資格3つは、ビジネス実務法務検定、ビジネス会計検定、メンタルヘルス・マネジメント検定です。 役に立たなそうな資格3つは、ファイナンシャル・プランニング技能士、MOS(マイクロソフトオフィス...

理由は記事のとおり、表計算ソフトの操作は掛け算九九みたいなものだからです。

一方で、以下でおすすめしている基本情報技術者試験の午後試験には、開発言語の選択問題で表計算ソフトを選ぶことができます。

まだまだ間に合う中高年向けおすすめ資格7選【転職・再就職に有利】
中高年編では電気工事士、危険物取扱者など設備系や、宅地建物取引士、TOEIC、基本情報技術者などの定番資格をおすすめしています。 中高年が即使える資格を取りたい人 この記事...

基本情報技術者試験では2019年1月に開発言語の選択問題に対して出題範囲の大幅な変更を発表しています。

この変更では時代の流れに即し古い開発言語を止めて、新しい開発言語を取り入れるということでCOBOLの廃止、Pythonの追加となっています。

しかし依然として表計算ソフトは選択問題に残っていることから、表計算ソフトの開発スキル(VBAとか)についてはまだまだ現役ということですね。

MOSと基本情報技術者の午後問題とではレベル感の違う話ではありますが、表計算ソフトは広く普及しているのでスキルとしては大切だよなー、と思い今回の解説記事を作ったのでした。

 

スポンサーリンク

まとめ

Googleスプレッドシートを使って投信の将来リターンを計算する方法を解説しました。

所詮は遠い将来のことですから、計算どおりにはいきません。

しかしこうした検証を自分で行うことができれば、どのような資産運用をすればよいのかを自分でデザインすることができます。

説明を記事にすると長くて大変に感じますが、実際は簡単な計算式をコピーして使い回すだけなのでそこまで難しくないと思います。

今回解説したようなやり方は簡易的な方法ではありますが、自分のやっている資産運用を理解する意味でも大切なのではないでしょうか。

\ この記事をシェアする /
\そーたろーをフォローする/
スポンサーリンク
そーたろーの「王国DIYガイド」