Excel
アイキャッチ 猫

今回はつみたてNISAの資産運用シミュレーションExcelVBAで作ってみました。
計算方法は金融庁のシミュレーションサイトと同様のものとなります。
よかったら前回の記事もどうぞ♪

つみたてNISAシミュレーション作成手順

今回は、
1.EXCELシート作成
2.VBAコード記述
3.マクロ設定
の流れで紹介していきます。

1.EXCELシート作成

まず、入力フォームとして以下を作成します。

EXCELシート作成

ボタンについては「開発」タブ→「挿入」→「ボタン」から作成できます。

EXCELシート作成

2.VBAコード記述

まず、「開発」タブ→「Visual Basic」からVBA記述画面を開きます。

VBAコード記述

記述していくVBAコードについては以下です。
内容の説明については、コードのコメントを参照ください。

基本的に、前回紹介した計算方法のイメージ通り作成します。

1.計算① 配列に月々の複利計算結果を格納

2.計算② ①の配列から、月々の累計元本・積立額を算出。年毎に配列へ格納

VBAコード記述

3.出力用配列作成 ②の配列を元に出力したい要素を計算し、配列へ格納

4.出力 出力用配列から、それぞれEXCELシートに出力していく
 (3までで出力用データの計算は全て行い、
  後は4でほぼそのまま出力するだけのイメージ)

Sub TumitateNisa()

    '積立年数から月数をセット
    Dim TOSHIKIKAN_NEN As Integer
    TOSHIKIKAN_NEN = Range("C2").Value2
    Dim TOSHIKIKAN_NEN_ARRAY As Integer
    TOSHIKIKAN_NEN_ARRAY = TOSHIKIKAN_NEN - 1
    Dim TOSHIKIKAN_TUKI As Integer
    TOSHIKIKAN_TUKI = TOSHIKIKAN_NEN * 12
    Dim TOSHIKIKAN_TUKI_ARRAY As Integer
    TOSHIKIKAN_TUKI_ARRAY = TOSHIKIKAN_TUKI - 1
    
    '月投資額をセット
    Dim TOSHIGAKU_TUKI As Long
    TOSHIGAKU_TUKI = Range("C3").Value2
    
    '利回り(年)から利回り(月)表面金利をセット
    Dim RIMAWAEI_TUKI As Double
    RIMAWAEI_TUKI = 1 + (Range("C4").Value2 / 12 / 100)
    
    '各配列用 年数が最大100年まで(12か月*100年)
    'コードがややこしくなるのと、
    'あまり増えても環境によってはEXCELがフリーズしたりするので
    '今回は動的配列にはしない
    Const MAXARRAY_1200 As Integer = 1200
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''
    '
    ' 計算① 配列に月々の複利計算結果を格納
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim TumitateArray(MAXARRAY_1200, MAXARRAY_1200) As Double
    For i = 0 To TOSHIKIKAN_TUKI_ARRAY
        
        Dim Tumitate_Tuki As Double
        Tumitate_Tuki = TOSHIGAKU_TUKI
        For j = 0 To TOSHIKIKAN_TUKI_ARRAY
        
            TumitateArray(i, j) = 0
            
            If j >= i Then
                TumitateArray(i, j) = Tumitate_Tuki
                
                Tumitate_Tuki = Tumitate_Tuki * RIMAWAEI_TUKI
            End If
    
        Next j
    
    Next i
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''
    '
    ' 計算② ①の配列から、月々の累計元本・積立額を算出。年毎に配列へ格納
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim YearSumArray(MAXARRAY_1200, 1) As Double
    Dim GanponSum As Double
    GanponSum = 0
    Dim YearNum As Integer
    YearNum = 0
    For j = 0 To TOSHIKIKAN_TUKI_ARRAY
        
        If j > 0 And j Mod 12 = 0 Then
            YearArrayNum = YearArrayNum + 1
        End If
        
        Dim TumitateSum As Double
        TumitateSum = 0
        For i = 0 To TOSHIKIKAN_TUKI_ARRAY
            TumitateSum = TumitateSum + TumitateArray(i, j)
        Next i
        
        '累計元本
        GanponSum = GanponSum + TOSHIGAKU_TUKI
        YearSumArray(YearArrayNum, 0) = GanponSum
        '累計積立額
        YearSumArray(YearArrayNum, 1) = TumitateSum
    
    Next j
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''
    '
    ' 出力用配列作成 ②の配列を元に出力したい要素を計算し、配列へ格納
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim YearTumitateArray(MAXARRAY_1200, 3) As Double
    For i = 0 To TOSHIKIKAN_NEN_ARRAY
        '累計元本
        YearTumitateArray(i, 0) = YearSumArray(i, 0)
        '累計運用益
        YearTumitateArray(i, 1) = YearSumArray(i, 1) - YearSumArray(i, 0)
        '累計積立額
        YearTumitateArray(i, 2) = YearSumArray(i, 1)
        '累計節税額
        YearTumitateArray(i, 3) = YearTumitateArray(i, 1) * (20.315 / 100)
    
    Next i

    '''''''''''''''''''''''''''''''''''''''''''''''''''
    '
    ' 出力 出力用配列から、それぞれEXCELシートに出力していく
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''
    '前回のシュミレーション結果をクリアしておく
    Range("B7:F106").Clear
    
    '出力開始位置の一つ上にへカーソル移動
    Range("B6").Select

    'それぞれEXCELシートに出力
    For i = 0 To TOSHIKIKAN_NEN_ARRAY
        
        ActiveCell.Offset(1, 0).Activate
        '年
        ActiveCell.Value2 = i + 1
        '累計元本
        ActiveCell.Offset(0, 1).Value2 = Round(YearTumitateArray(i, 0))
        '累計運用益
        ActiveCell.Offset(0, 2).Value2 = Round(YearTumitateArray(i, 1))
        '累計積立額
        ActiveCell.Offset(0, 3).Value2 = Round(YearTumitateArray(i, 2))
        '累計節税額
        ActiveCell.Offset(0, 4).Value2 = Round(YearTumitateArray(i, 3))
        
    Next i
    
    '罫線を引く
    ActiveCell.CurrentRegion.Select
    Selection.Borders.LineStyle = xlContinuous
        
    '桁区切りする
    Selection.NumberFormatLocal = "#,###"
    
    '最終行の文字を赤色にする
    Selection.Rows(Selection.Rows.Count).EntireRow.Select
    Selection.Font.ColorIndex = 3
    
    'カーソルを年数に戻す
    Range("C2").Select

End Sub

3.マクロ設定

最後に作成したマクロをボタンに登録します。
「右クリック」→「マクロの登録」

マクロ設定

マクロを選択して、完了です。

マクロ設定

シミュレーション使用方法について

使用方法は簡単です。
「年数」「月投資額」「利回り(年)」を入力して、
「つみたてNISAシミュレーションを開始する」ボタンをクリックするだけ♪

シミュレーション使用方法について

試しに、金融庁のシミュレーションと比べてみましたが、
問題なく同じ結果となりました。
資産運用シミュレーション : 金融庁

シミュレーション使用方法について

色々と分析してみた

せっかくなので、いくつかのパターンでシミュレーションしてみました♪

利回りが3%と5%どれくらい差が出る?
(20年・月20,000円・利回り3%or利回り5%)

2%変わるだけで、運用益が倍変わることが分かります。

[3%の場合]

シミュレーション

[5%の場合]

シミュレーション

20年間最大額積み立てた場合、いくら貯まる?
(20年・月33,333円・利回り5%)

つみたてNISAは年40万円・20年間つみたてが可能です。
そこで仮に平均利回りが5%だった場合、以下の結果となりました。

シミュレーション

老後2000万円問題!
月5000円積み立てると何年で2000万円貯まる?
(60年・月5,000円・利回り5%)

月5,000円ですと、56年で達成可能なようです。
元本と運用益の差から、複利の凄さがよくわかりますね。

シミュレーション
シミュレーション

万が一、今後経済が不況に陥り、
利回り-5%(赤字)だとどれだけ損する?
(20年・月20,000円・利回り-5%)

前述の利回り5%と比べると、意外に損失が低いことが分かります。

[-5%の場合]

シミュレーション

[5%の場合]

シミュレーション

まとめ

今回はつみたてNISAの資産運用シミュレーションをExcelVBAで作ってみましたが、いかがでしたでしょうか。

金融庁のシミュレーションサイトと比べ、
・各年の金額が詳細に把握できる
・節税額が分かる
・利回りが赤字の場合のシミュレーションができる
・長期(100年まで)のつみたてNISAのシミュレーションができる
・VBAコードを公開しているため、コピーしてカスタマイズ可能

等、メリットも多いため、使っていただく利点はあるかと思います!

ご興味のある方は是非、試してくださいね♪

人気ブログランキングへ