イシバシランブログ

「エクセル関数」と「お金に関する情報」をメインテーマに書いています。

【エクセル】積立投資の毎月収益を計算してみた

積立投資について、年間の数字を算出する方法は分かるが、月別に分けたときにどのような推移なのか、シミュレーションされた数値を見たいと思い、エクセルを作成してみた。

 

毎月収益額の一覧表を作成する

まず、毎月資産額と毎月収益額を表示するための事前準備が必要となる。

毎月の積立金額を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関数

【エクセル】IF関数の使い方 - イシバシランブログ

 

○FV関数

【エクセル】FV関数の使い方_将来価値の算出 - イシバシランブログ