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