【Excel】FV関数／What-If分析のデータテーブルを使った複利計算

FV関数／What-If分析のデータテーブルを使った運用年数と利回り複利計算

以下は毎月3万円拠出して、運用年数と運用利回り（年利）を変えた時に資産額がどれくらいになるのかをシミュレーションしたものになります。

今回は上記のような表をExcelを用いて手軽に計算する方法を解説します。

今回紹介するExcelスキルはFV関数とWhat-If分析のデータテーブルです。

Excel操作手順

まずは以下のように、年利と運用期間と積立額を入力した表を作成しました。

資産額の入力欄にFV関数を用いて結果を出力します。

FV関数

指定した利率、期間、積立額で運用した場合の満期後の将来価値を求めます。

FV関数のFVはFuture Value（将来価値）という意味です。

利率と期間は定期支払額と時間の単位を一致させます。

積立額が毎月3万円の場合、利率は月利を入力し、期間も月数を入力することになります。

早速FV関数を使って求めていきます。

利率を入力します。

ここではC2の年利を月利にするため12で割ります。

期間もC3の数字を月単位にします。

積立額を入力します。

定期支払額を入力する箇所なので、マイナスをつけるのを忘れないようにしましょう。

省略可能ですが、現在の積立額・残高は０ということで０を入力します。

もし例えば残高が10万円ある場合、この10万円も運用のために拠出すると考えて「-100,000」と入力をして、必ずマイナスをつけます。

省略可能ですが、今回は期末払として０を入力します。

括弧を閉じてEnterを押すと、月3万円の積立、年利1.0%で1年間運用した場合の資産額を計算することができました。

スポンサーリンク

What-If分析のデータテーブル

次に以下の年利と運用期間の組合せを色々と変えてみた時の資産額を計算していきます。

冒頭の表のようなアウトプットイメージを作ることをゴールとします。

まずは下準備として先ほど資産額を入力したC6を表の一番左上に位置させるように、年利のタイトル行と運用年数のタイトル列を作成します。

表を選択して、データ→「予測」の中にある「What-If分析」→データテーブルを選択します。

すると、行の代入セルと列の代入セルを入力するダイアログボックスが表示されますので、行の代入セルには年利を入力してあるC2を選択します。

列の代入セルには期間を入力してあるC3を選択します。

OKを押すと、冒頭と結果が同じ表を作成することができました。

これで完成になりますが、下の例では表のタイトル列の運用期間を0年、5年、10年と5年刻みに変更をしました。

すると表の中の結果も一括で再計算されるようになっています。

この様にデータテーブルのタイトル行、タイトル列の数値を後から変更しても適切な計算結果が表示されるメリットがあります。

ただしこれは、データテーブル左上に元となる計算式が存在している場合に限ります。

元となる計算式が存在するC6の数字を、C6以外の場所に移動させた場合、計算がうまくいきませんので、表の邪魔に感じる方は、文字の色を背景色の白と同じにするなど、数値は入力されているけれども見せない工夫をする必要があります。

今回の内容が参考になれば幸いです。