【Excel】つみたてNISAシミュレーション（2）【VBA】

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

マクロ（VBA）が難易度が高い！と思われる方は一度こちらも見てくださいね♪
練習になるかもしれません。

【Excel VBA】初心者でも簡単にマクロを作る方法

業務効率化したい方必見！OfficeのExcel VBAで"初心者でも簡単に"マクロを作成する方法を紹介しています。知識0でも理解できるようサンプルはもちろん画像たっぷりで説明していますので、是非見て試してみてください♪

つみたてNISAシミュレーション作成手順
シミュレーション使用方法について
色々と分析してみた

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

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

１．EXCELシート作成

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

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

２．VBAコード記述

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

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

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

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

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

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

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

■TumitateNisaマクロ

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