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

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

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

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

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

Excel操作手順

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

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

FV関数

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

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

=FV(利率, 期間, 定期支払額, 現在価値, 支払期日
利率一定の利率を指定します。
期間支払回数の合計を指定します。
定期支払額定期的な預け入れ金額を指定します。
現在価値省略可能でその場合、0 を指定したと見なされます。
最初に預入する頭金がある場合、指定します。
支払期日預入する期日を指定します。期末の場合は「0」、期首の場合は「1」を指定します。
省略可能でその場合、0 を指定したと見なされます。

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

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

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

利率を入力します。

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

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

積立額を入力します。

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

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

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

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

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

スポンサーリンク

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

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

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

画像に alt 属性が指定されていません。ファイル名: image-82.png

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

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

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

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

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

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

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

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

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

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

Excelを一度体系的に学ぶと、目に見えて生産性が飛躍的に向上します。

MOS Excel※は資格の取得、及び資格取得までのプロセスを通じて体系的にExcelスキルを身につけることができますのでコスパが良くおすすめです。

※Microsoft Office Specialist(MOS)ExcelはExcelの利用能力を証明する世界的な資格です。

筆者は「スペシャリスト(一般)」と「エキスパート(上級)」両方を取得して体系的にスキルを身に着けました。

MOS試験対策でどのテキストを使うべきか悩まれる方も多いのではないかと思いますが、以下がおすすめです。

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

スポンサーリンク

コメントを残す

メールアドレスが公開されることはありません。

CAPTCHA