30代会社員のなおつんです。
このブログでは30代会社員の悩みを同じ会社員へ向け共有し、今日よりも明日へ一歩前進できるような記事を書いています。
私は製造業（メーカー）の営業系の部署で働いている傍ら、個人投資家として株や投資信託を保有しながら様々な投資案件にも挑戦しています。
その中で得られた知識や失敗談を共有して同じ投資家の方やこれから投資家を目指す方に役立つ情報をお届けしています。
令和時代は「老後2,000万円問題」や「終身雇用の崩壊」が叫ばれる中で将来に不安を感じて投資を始める方が益々増えているようです。
しかし、「結局いくら儲かるの？」とか「何年くらい積み立てれば良いの？」といった漠然な疑問を持っている人も多いと思います。
積み立て投資を始めたいけれど、老後までにどのくらいお金が増えるかを自分で計算したい。
実はインターネットでちょっと調べると関数や計算式を使った複利の計算方法がたくさんあるのですが、計算式だけを並べた難しい内容のものが多く「なぜこの計算になるのか？」という事まで分かりやすく説明した記事はほとんどありません。
今回はそんな方のために「自分で簡単に出来るエクセルの投資の計算グラフの作り方」をすごく分かりやすく解説します。
中学校の数学レベルの知識で誰でも簡単に試算表を作れます。
さっさと計算グラフを作りたい方は、目次から”超分かりやすい「エクセル表に計算式を入力」”までジャンプしてください。
超分かりやすい「投資の計算グラフ」
計算する上で重要な「複利」については過去の関連記事を参照してもらうとより理解出来ると思います。
資産形成は複利を最大限に活かす
今回の記事を読んで出来る事のイメージを持ってもらうために下に完成形のグラフを用意しました。
複利試算グラフ⇩
ちなみにこのグラフは１か月に33,000円ずつ積み立てして年利３％で運用（税金分も考慮）した場合を想定して作成したグラフです。（赤の縦線は投資30年目を分かりやすく示しただけです。）
貯金などで単純に積み立てだけをした場合と複利で運用した場合の差は歴然です。
このグラフが作れるようになれば自分で積み立て金額や想定利回りを変更できますので、簡単に計算が出来るようになります。
超分かりやすい「複利の計算式と考え方」
ではグラフを作る前に計算式の考え方を解説します。
先ほど見せたグラフの中の「積み立て＋複利計算」という折れ線グラフを分解すると上のようになります。
もっと分かりやすくするために１年ごとに分けて解説します。
複利の計算式 ＜１年目＞
今回は１か月の積み立て金額を33,000円で想定していますので、１年目（正確には１年経過した時点）の積み立て分の金額は 33,000円 × 12 ＝ 396,000円 となります。
これに想定利回り３％の利息分が追加されるので、396,000円 × 0.03 ＝ 11,880円 が利息分の計算式です。
積み立て分と利息を合わせた時の計算式は、
ここで忘れてはいけないのが、利息分に掛かる税金です。
積み立てNISAやIdecoであれば税金は免除されるため上記の計算式をそのまま使えますが、今回はより正確に試算するため税金分も計算します。
投資によって掛かる税金は20.315％なのでこれを控除した最終的な計算式は以下の通りです。
赤字の部分が税金分は控除すための計算式です。この計算式は後ほど登場します。
少しややこしくなってきたので、ここで分かりやすいように１年目の計算式を図に当てはめると以下のようになります。
複利の計算式 ＜２年目＞
２年目のポイントは「元本」がある事で、この「元本」は１年目で計算した式の合計と同じになります。
ここからさらに積み立てをして利息を加え、最終的に税金を控除する作業が必要になります。
元本 ＋ ２年目の積み立て分 ＋ 利息 － 税金 という簡単な計算です。
少しややこしいのは利息の計算です。
利息分の計算式は、(元本 + (33,000 × 12 )) × 0.03 となります。
投資家にとって嬉しいのは、元本と積み立て分の両方に利息が掛かる事でこれが複利の力としてかなり効いてきます。
次に税金分の計算ですが、繰り返し重要なポイントは税金は利息分に掛かるので、このような計算式になります。
((元本 + (33,000 × 12 )) × 0.03)×(1-0.20315)
これを全て合わせた２年目の計算式は、
計算式では難しくなってきたので、図式化したものが以下になります。
複利の計算式 ＜３年目以降＞
３年目以降は、前年の計算式＝元本として代入して２年目と同じように利息分と税金分を計算するだけで出来ます。
計算式自体は２年目のものと全く一緒で、「元本＝前年」と考えて計算式をコピーするだけです。
最初は理解するのに時間が掛かりますが、一度理解出来れば非常にシンプルな計算式であることが分かります。
超分かりやすい「エクセル表に計算式を入力」
ここからは実際にエクセルに計算式を入力してグラフを作っていきます。
基本的なエクセルの操作が分かっている前提で解説していくのでご了承ください。
まずは、エクセルに以下のような文字と数字を入力していきます。
投資年数の上限は20年でも30年でも好みで決めて大丈夫です。
今回は30年でグラフを作っていきます。
”B3”のセルは「年間の積み立て金額」として「B2 * 12」で計算させておきます。
「想定利回り」と「税率」はパーセント表示でも良いし、「0.03」や「0.20315」の小数点表示でもどちらでも大丈夫です。（個人的にはパーセント表示が好きです。）
ピンクの”D列”は「投資年数」とします。これは前項の計算式を解説した時に出てきた「１年目、２年目、、、」という言葉と同じ意味を持つ数字です。
みどりの“E列”は「積み立て＋複利運用」、紫の”F列”は「積み立て」とする事で、グラフを作成した時に複利によって資産にどれだけ差が出るか一目で分かるようになります。
数式の入力
下の図の”E3”のセルに１年目の計算式を入力します。
また”F3″のセルには”=B3”として年間の積み立て金額をそのままコピーさせます。
ちなみに”F列”は396,000円をずっと足していくだけなので、２年目以降から”B3″に掛け算をして計算していくか、「前の列＋B3」を繰り返す方法のどちらで大丈夫です。
次に２年目の”E4”のセルですが、これも計算式を入力します。
先ほどの１年目と違うところは、１年目の計算式が２年目の元本として計算式が組み込まれているところです。（上の図の計算式内の青い”E3”の部分）
ちなみに”B3”、”B4”、”B5”のセルは参照するセルを固定しておくため、「F4キー」を押して”$B$3”、”$B$4”、”$B$5”とそれぞれ固定させておきましょう。
ここまで来ればあとは数式を下にコピーしていくだけです。
注意点は必ず２年目の計算式をコピーする事です。
間違えて１年目の計算式をコピーすると間違ったグラフが出来上がります。
超分かりやすい「グラフの作成」
グラフの作成はエクセルをそこそこ使える人であればなんてことありません。
必要な部分を選択してグラフを折れ線グラフを挿入するとある程度グラフは出来ます。
あとはこのグラフに目盛りを追加したり色を変えたりして好みの形に整えます。
私が作ったグラフはこんな感じになりました。⇩
毎月33,000円を年利３％で30年間積み立てて投資をすると、約1,600万円くらいになります。
積み立てだけと比べて500万円くらいの差になります。
想定利回りを４％に変更してみると下のグラフになり、複利の力のすごさが実感できます。⇩
まとめ
今回は投資初心者に向けて複利の計算方法を自分で出来る方法を解説しました。
伝えたかった事はネット上の計算アプリなどに頼らない自分で計算してみる大切さです。
今回紹介した計算式は私が何度も頭を捻って考えた独自のものですが、それでも完璧ではなく利息や税金を掛けるタイミングによって別の方が公開している計算方法と若干の差はあります。
私の計算方法を実際にやってみて、より正確な計算方法があれば教えてください。
このブログでは会社員や学生さんの役に立つ情報や考え方を発信しています。
ぜひ興味があれば別の記事を読んでいただきお役に立てればと思います。
