Excel
アイキャッチ 猫

金融庁が公開しているつみたてNISAの資産運用シミュレーションをExcelで作ってみようと思います。
金融庁のサイトでは具体的な計算方法はありませんでしたが、試行錯誤しながら調べてみましたので気になる方はどうぞ♪
資産運用シミュレーション : 金融庁

つみたてNISAとは

つみたてNISAとは年間40万円までを20年間運用でき、分配金と売却益が非課税となる制度です。
購入可能な商品は金融庁が認めた投資信託に限られており、低リスクで運用でき長期投資に向いています。

NISAイメージ

本来、20.315%も課税されるためとってもお得!

詳しくは金融庁のつみたてNISAの概要

金融庁の資産運用シミュレーション

まず、金融庁では以下が公開されています。

(金融庁)資産運用シミュレーション

毎月の積立金額・想定利回り(年率)・積立期間を入力し、
「計算する」ボタンで最終積立金額を表示します。

(金融庁)資産運用シミュレーション

また、グラフについても表示できます。

次項から、上記の計算方法やグラフの出力方法についてご紹介します。

計算方法

金融庁のページには以下のように書かれていますので、こちらを参考に計算します。
【本シミュレーションの前提条件】
※年一回の複利計算をしています。
※計算結果は小数点以下を四捨五入しています。

ただ、以下の点には気をつけてください。
・年一回の複利計算
 →想定利回り(年率)に基づき、月に一回の複利計算(表面金利)をする
・計算結果は小数点以下を四捨五入
 →計算結果以外は四捨五入しない。

上記を考慮した計算が以下になります。
(数式→数式の表示から簡単に計算式を出せますよ♪)

資産運用シミュレーション計算方法
資産運用シミュレーション計算方法

上記のように月投資額、利回り(年)を入力することで、1年間の積立資産や運用額が分かるようにしました。
※本記事は計算方法中心の内容のため、あえて期間を1年限定にしています。
 期間変動可能なシミュレーションは、次回の記事でVBAを使い作成します。

利回り(月)の表面金利は、年率/12となりますので、それを使用して毎月の積立額にかけていきます。また、月に一回の複利のため、前月の額に金利をかけます。
それらが期間分続き、合計したものが積立資産になります。

資産推移(計算結果)は、ホームタブ→数値→「,」をつけることで、表示上は小数点以下四捨五入となりますので、「ROUND関数」は使いませんでした。

期間1年 月投資額20000円 利回り(年)5%で計算

結果は以下になります。前述の金融庁のシミュレーション結果と同じ積立資産なので、計算方法は合ってそうですね♪

資産運用シミュレーション計算方法

グラフ

次にグラフ作成です。

まず、グラフ出力用にデータを整形します。
グラフ作成後にグラフの機能でデータを整形してもいいので好みの問題ですが、整形したデータをそのまま出力した方が楽で正確です。
整形時は、金融庁のシミュレーションを見ると千円単位で四捨五入なので、合わせました。

資産運用シミュレーショングラフ

前述のデータ(期間1年 月投資額20000円 利回り(年)5%で計算)を使用すると結果は以下になります。

資産運用シミュレーショングラフ

このデータを使って、今回はグラフを作成します。
(1)グラフ用データにカーソルをあて、挿入タブ→縦棒→任意の形状を選択

資産運用シミュレーショングラフ

(1)結果

資産運用シミュレーショングラフ

(2)原本・運用益・積立資産の順番が想定と異なるため、データ選択→順番の入れ替え

資産運用シミュレーショングラフ
資産運用シミュレーショングラフ

(2)結果

資産運用シミュレーショングラフ

(3)それぞれの値を表示する。データラベル→中央

資産運用シミュレーショングラフ

(3)結果

資産運用シミュレーショングラフ

(4)積立資産は運用益と原本の合計額のため、値だけ表示させる。
   右クリック→データラベルの書式設定→塗りつぶし→塗りつぶしなし

資産運用シミュレーショングラフ

(4)結果

資産運用シミュレーショングラフ

ここからは見栄えの問題なので、気にしないのであれば以下は不要です。
(5)右クリック→軸の書式設定→最小値と最大値の固定値を設定

資産運用シミュレーショングラフ

(5)結果

資産運用シミュレーショングラフ

(6)積立資産の値をグラフに近づけたいので、
   レイアウト→データラベル→内側軸寄り

資産運用シミュレーショングラフ

(6)結果

資産運用シミュレーショングラフ

(7)最後に単位をつける。
   レイアウト→テキストボックス→横書きテキストボックス→(「万円」)入力

資産運用シミュレーショングラフ
資産運用シミュレーショングラフ

以上で完成になります。
これでおおよそ金融庁のシミュレーションと同じようなものができました♪

資産運用シミュレーショングラフ

まとめ

今回は金融庁のシミュレーションを計算し、グラフ化してみました。
計算方法については、
想定利回り(年率)に基づき、月に一回の複利計算(表面金利)をする
が一番重要な点です。
次回は、実際の資産運用にも使えるようなつみたてNISAのシミュレーションをVBA使って紹介しますので、良かったらどうぞ♪

また、金融庁のシミュレーションの計算方法が書かれているWebサイトが見つからず苦慮しましたが、以下のサイト等を見てなんとか分かりました。
ありがとうございました!
複利計算(元利合計)
積立かんたんシミュレーション

人気ブログランキングへ