最近流行りになりつつある投資、特に積立投資は
2024年から新NISAが始まる影響で非常に注目されています。
積立投資については、あまり馴染みのない方からすると
何をしているのかよく分からない。。。
と感じる部分が多いと思います。
それを解消するために、積立投資の様々なシミュレーションが
証券会社のホームページ等に置かれています。
ただ実は積立投資のシミュレーションって、
Excelを使えば意外と簡単に自作できる
って知ってました？
今回は、Excelで作れる投資信託のシミュレーションをご紹介したいと思います。
【本記事の目標】
Excelで投資信託のシミュレーションを作ろう
積立投資ってそもそも何をしてるの？
積立投資って？
積立投資というのは、その名前の通り、
コツコツと積み立てながら投資していく投資
です。
一度に大金を使って投資をしてしまうのではなく、
少額でも良いので
定期的に
定額を
長期間かけて
投資をしていくのが、積立投資です。
積立投資する商品は主にどんなもの？
少額を、定期的に、長期間かけて投資していくので、不動産のように最初に高額な投資が必要な商品では積立投資はできません。
じゃあ積立投資に向いている投資はどういったものがあるかというと、
株式、投資信託、債券
といった、比較的低価格でも購入でき、いつでも売買ができるものになります。
特に投資信託に関しては、基本的に１円単位で投資していくことが可能ですので、非常に小額からでも投資を始めることが出来ます。
積立投資すると何が良いの？
投資をする一番のメリットというのは 複利 が効いてくることです。
複利の計算については下記記事でも書いていますが、時間を掛ければかけるほど、その効果は大きくなります。
じゃあ、早めに大きな金額を投資すれば、それだけ複利が効いて一番のメリットが高いんじゃないか
というと、そうでもないです。
というのも、上記記事の計算では投資した商品そのものの価値の変動が考慮されていないからです。
上記記事での複利の計算は、株・投資信託・債券を想定して書いていますが、
これらの商品は特に、購入したときの価値と売るときの価値が変動する可能性が非常に高いです。
そのため、年利５％で運用して毎年増やしたくて全財産で投資信託を購入したのに、翌年に大暴落が起こって購入したときより価値が大幅に下がってしまう、ということが起こりえます。
こういったときに被害を最小限にできるのが 積立投資（ドルコスト平均法） です。
例えば、
全財産を一括で投資して、翌年に大暴落で半値になってしまうと、単純に全財産が半値になってしまいますが、
全財産を10%ずつ10年かけて投資していたら、投資開始の翌年に大暴落で半値になったとしても、半値になるのは全財産の10%分だけで、翌年以降は半値になった商品を倍の数だけ購入できます。
これがさらに翌年、大暴落から復帰して元の価値に戻ったら、半値で購入したものは倍の価値になり、大きな利益を得ることが出来ます。
このように、大暴落が起ころうと何が起ころうと、常に淡々と一定額を積立投資していくことで、商品価値の変動に一喜一憂することなく、ただただ複利で価値が増えていくのを見守ることが出来ます。
Excelで実際に積立投資のシミュレーションを作ってみよう
基本は複利の計算
じゃあ、実際にExcelを使って積立投資のシミュレーションを作ってみましょう。
とはいえ、基本は複利の計算で使ったシートがベースになります。
複利の計算では最初に投資した以降は何もしない想定で計算シートを作りましたが、
今回はそこが積立投資に変わるだけです。
ですので、複利の効果は同じようにかかってきます。
Excelシートの構成
今回作る【積立投資シミュレーションシート】は下記のイメージで作っていこうと思います。
基本の数値を入力
まずは計算したい基本情報を入力する部分を作ります。
今回の計算では
- 年齢（自身の現在の年齢）
- 初期投資額（最初に一括で投資する金額）
- 毎年の積立額（1年間の合計積立額）
- 投資した商品の年利
を記入し、その情報で計算をしていきます。
この部分はただの情報記入欄ですので、上記４点が記入できるように欄を作っておきます。
計算式を入力
計算式が記入されているのは左側の、A列、B列、C列、です。
また計算式は、2行目、3行目以降で分かれていますので、それぞれ説明していきます。
A列2行目：最初の年齢
A列には年齢を記入していきます。
A列の2行目は、計算を開始する時点での年齢ですので、”G2”セルに記入してある年齢をそのまま持ってきます。
＝G2
B列2行目：最初の投資額
B列には実際に投資した額（支払った額）を記入していきます。
B列2行目は最初の投資金額ですので、”G3”セルに記入してある初回の一括投資金額をそのまま持ってきます。
＝G3
C列2行目：最初の投資額の価値
C列には投資した額がどんな価値になっていくかを記入していきます。
C列2行目は最初の時点ですので、まだ金利も無いため投資額がそのまま価値になります。
＝G3
A列3行目以降：年齢
A列には年齢を記入していきます。
年齢は１年ごとに１歳追加していけばOKですので、１つ上のセルに＋１をすればOKです。
＝A2＋１
上記の式を”A3”セルに記入して、それを下にコピーしていきます。
（今回は50セル下までコピーしました）
B列3行目以降：投資額
B列には実際に投資した額（支払った額）を記入していきます。
今回は積立投資ですので、毎年一定の金額を投資していきます。
毎年の投資金額は”G4”セルの値ですので、前年の投資金額に”G4”セルの値を足していきます。
=B2+$G$4
（上記の式を”B3”セルに記入して、それを下にコピーしていきます。）
”G4”セルの前に【＄】マークがついていますが、これは絶対参照にする際の記号です。
絶対参照については下記記事に記載していますので、ご確認ください。
C列3行目以降：投資額の変化
C列には投資した額がどんな価値になっていくかを記入していきます。
投資した額の価値は、1年後には 前年の価値×（１＋年利） になりますが、
積立投資の場合、その年に投資した分については金利がかからないので、そのままの金額になります。
=C2*(1+$G$5)+$G$4
ここで、
『C2*(1+$G$5)』 が 前年の価値×（１＋年利）
『$G$4』 が その年に投資した分
になります。
上記の式を”C3”セルに記入して、それを下にコピーしていきます。
今回は分かりやすくするため、2行目と3行目以降で計算式を分けて紹介しました。
ただこの場合、間違えて2行目の計算式を全ての行にコピーしてしまうと、3行目以降の計算式が消えてしまい、計算が出来なくなってしまいます。
それを避けようと思うと、2行目以降全てに同じ式を適用させる必要があります。
この場合の計算式も下記に記載しておきます。
若干分かりにくいですが、１つずつ分解していくと理解できるかと思います。
■A列2行目以降
=$G$2+ROW()-ROW($A$2)
■B列2行目以降
=IF(ROW()=ROW($B$2),$G$3,B1+$G$4)
■C列2行目以降
=IF(ROW()=ROW($C$2),$G$3,C1*(1+$G$5)+$G$4)
グラフを追加する
上記までで計算式の入力は完了です。
後は基本情報の入力欄に好きな数値を入力してみて、
自身の年齢が何歳の時に、投資した額がどのぐらいの価値に増えているのかを見ていけばOKです。
ただせっかくなので、数値だけではなくグラフも作っておきましょう。
グラフ化する際は、A~C列を選択して『散布図』のグラフを挿入します。
挿入されたグラフには、
① 投資額
② 価値
の２つの線があると思いますが、この線の差分が投資によって増えた金額になります。
（このグラフは書式を色々変更しています。
自身のシートでも見やすいように変更していただいてOKです。）
実際に計算してみよう
積立投資シミュレーションシートは上記で完成です。
意外と簡単でしょう？
では実際にいくつかのケースをシミュレーションしてみましょう。
ケース１：30歳でから毎年10万を積立投資（年利3%）
この場合の推移は下記のようになります。
毎年10万円、毎月だと1万円以下の積立投資でも、
30年後の60歳時点で1.5倍にも増加しており、300万円を投資して470万円まで増加します。
ケース２：20歳で100万を一括投資、以降は毎年20万を積立投資（年利4%）
この場合の推移は下記のようになります。
20歳の時に何とか100万円を捻出して、以降はちょっと頑張って毎年20万円を積立投資して、
比較的現実的な年利４％で運用すれば、
60歳時点では総投資額は900万円ですが、その価値は1800万円になっており倍に膨らんでいます。
シミュレーションを作ってみて
積立投資のシミュレーションは色んなサイトに転がっています。
なので、検索すれば色んなサイトが出てくると思います。
シミュレーションをしたいだけであれば、そういったシミュレーションを使うのが一番早いと思いますが、それだとそのシミュレーションで何をしているのかを理解できず、応用問題や別の活用には使えなくなります。
その点、今回のようにシミュレーションソフトを自作すれば、中身を把握できているので、さらに発展させたシミュレーションを自分で作っていって、自分だけのシミュレーションソフトを作ることが出来ます。
次回は積立投資で一番有名なS&P500の過去データを使って、「過去に積立投資をしていたら？」というシミュレーションをする方法について書いていこうと思います。
コメント