積立投資が将来の資産形成に大きな影響を与えることは認識しているが、実際にどのくらいのインパクトがあるのか、シミュレーションされた数値を見たいと思い、エクセルを作成してみた。
年間収益額一覧表を作成する
まず、年間資産額と年間収益額を表示するための事前準備が必要である。
毎月の積立金額をB3セルに、予定利率をB4セルに入力する。
そして、年数をA6セルに、投資額累計をB6セルに、年間資産額をC6セルに、年間収益額をD6セルに追加する。
ちなみに、完成イメージは次の通りで、これから7行目以降に数式の入力を行う。
年間収益額の算出数式
年数を表示するために、A7セルには下記数式を入力する。
=ROW()-6&"年目"
A8セル以降は上記数式をオートフィル。
投資額累計を表示するために、B7セルには下記数式を入力する。
=$B$3*12*IF(MID(A7,3,1)="目",LEFT(A7,1),LEFT(A7,2))
「年数×12ヶ月×10,000円」を元本金額として表示するように設定している。
年数をLEFT関数で取得しているが、1桁年数から2桁年数に変わる時も、引き続き年数を抽出するために、IF関数で条件を設定している。
B8セル以降は上記数式をオートフィル。
資産額累計を表示するために、C7セルには下記数式を入力する。
=FV($B$4/12,IF(MID($A7,3,1)="目",LEFT(A7,1),LEFT(A7,2))*12,-$B$3)
C8セル以降は上記数式をオートフィル。
年間収益額を表示するために、D7セルには下記数式を入力する。
=ROUND(C7-B7,0)
D8セル以降は上記数式をオートフィル。
数式の解説
まず、A7セルに入力した数式についてある。
1.ROW関数を使い、行数と同じ値を抽出する。
2.「7」が抽出されたので、-6を付けることで、1になるように調整。
3.表示される値を「○年目」とするために、数式の後ろに「年目」という文字を追加する。
次に、B7セルに入力した数式についてある。
1.年間の投資額は12の倍数になるので、「1ヶ月の投資額×12×経過年数」となる数式を作る。
2.経過年数をA列で設定した値で抽出したいが、1年目と10年目では、抽出する文字数が違うため、MID関数を使い、参照したA列の「目」の文字が3番目にあれば左から1文字を抽出し、4番目にあれば2文字を抽出する。
次に、C7セルに入力した数式についてある。
1.月利を算出するために、年利を12で割る「$B$4/12」の数式を作成。
2.積立回数について、経過年数をA列で設定した値で抽出したいが、1年目と10年目では、抽出する文字数が違うため、MID関数を使い、参照したA列の「目」の文字が3番目にあれば左から1文字を抽出し、4番目にあればから左から2文字を抽出する。
毎月の積立回数が算出されたので、年間の積立回数に置き換えるために×12を加える。
3.積立金額を参照するために、B3セルを選択。
数字がプラス表記されるためにB3セルの前に「-」を付ける。
最後に、D7セルに入力した数式についてある。
1.C列で算出された数字が小数点になるため、値が整数になるように調整。
年間資産額から投資額累計を差し引くことで、収益がいくらになるか算出している。
エクセル関数の記事の紹介
今回、積立投資の年間収益を関数で表示するために、いくつかの関数を使用した。
使い方が難しいと思った関数があった場合は、使い方を載せている記事があるので、参考にしてほしい。
○LEFT関数・MID関数
【エクセル】LEFT関数とRIGHT関数とMID関数の使い方 - イシバシランブログ
○IF関数
○FV関数
