VBA

【ウェブサイトと同じことをマクロVBAで】積立金額シミュレーションをエクセル上で行う方法(FV関数使います。)

記事内に商品プロモーションを含む場合があります

このページでわかること

ワークシート関数FV関数の使い方と積立の計算・シミュレーションを行うことが出来ます。

こんにちはhokkyokunです。
投資をされている方は
積立金額のシミュレーションを一度は行ったことがあると思います。

それをエクセル上でやってしまいましょう。
オフラインでも可能ですし、色々カスタマイズもできます。
例えば、複数の条件での比較なんかも付け加えることができます。

やってみましょう。

FV関数とは

FV関数はエクセル上で動かす関数で

構文:FV(rate,nper,pmt[,pv[,type]])

rateは利率です。年利の場合、投資を毎月行うのであれば月利に変換する必要があります。
nperは回数です。毎月行うのであれば、何ヶ月積立するのか計算します。
pmtは1回あたりの積立金額です。
pvは頭金です。手元にいくらあるか、を入力します。
typeは期日の計算です。期末にするか、期首にするかですが、シミュレーションをするのにはどちらでもいいように思います。

実際に使ってみよう

一つ例を見てみましょう。

年利6.5%、積立期間1年、積立金額10,000円、頭金(手元にある金額、これも投資します。)500万円

という状態で運用した場合の積立後の金額を計算してみます。

積立後の金額の計算式は

FV(0.065/12,12,10000,5000000)*-1
となります。

今回は毎月定額を積立するというシミュレーションです。
そのため、あらゆる数値は月ベースに変換する必要があります。

年利は月利に直します。6.5%/12=0.065/12
積立期間は年を月に直します。1年×12=12
後はそのまま入力です。

最後に-1をかけてます。
値がマイナスになるためです。

過程のシミュレーションもしたい!マクロを使おう

上記の計算で
積立後の結果はわかりました。

でも、過程も見たくないですか?
10年積立するとして、1年後いくらたまっているのか見たいですよね!!

よく楽天証券とかでシミュレーションやってますが、
あれはエクセルでも簡単に作れます。

Sub 積立金額()

Application.ScreenUpdating = False '画面更新停止(スピードアップ)
Dim table As ListObject 'テーブルの変数
Dim Rate As Double '利率
Dim Nper As Long '積立回数
Dim Pmt As Long '毎月の積立金額
Dim Pv As Long '頭金
Dim sttday As Date '開始日
Dim i As Long 'For文のための変数
Dim FV As Long '積立後の金額

'入力値を代入
With ThisWorkbook.Worksheets("Sheet2")
    Rate = .Range("C4").Value '利率
    Nper = .Range("C5").Value * 12 + .Range("C6").Value '積立回数=積立する月数
    Pmt = .Range("C9").Value '1回あたりの積立金額
    Pv = .Range("C10").Value '手元にある頭金
    FV = .Range("C11").Value '積立後の最終的な金額
    sttday = .Range("C7").Value & "/" & .Range("C8").Value & "/01" '開始月を作成
    
    Set table = .ListObjects(1)
End With

'テーブルのデータを消します。
On Error Resume Next
    table.DataBodyRange.Delete
On Error GoTo 0

'For文で積立回数分まわします。
For i = 1 To Nper

    'テーブルのデータ(レコード)を追加します。
    With table.ListRows.Add
    
        .Range(1).Value = Format(sttday, "yyyy""年""m""月""") '年月
        .Range(2).Value = Pmt * i '積立金額の合計
        
        '利回り後の評価額です。小数点以下を切り捨てるために関数を重ねています。
        .Range(3).Value = WorksheetFunction.Round(WorksheetFunction.FV(Rate / 12, i, Pmt, Pv) * -1, 0)
        .Range(4).Value = .Range(3).Value - (.Range(2).Value + Pv) '評価額-(積立額+頭金)
        
        '評価額/最終的な積立金額で達成率を出しています。
        .Range(5).Value = Format((.Range(3).Value / FV) * 100, "#.#")
    End With
    sttday = DateAdd("m", 1, sttday) '年月を一月プラスして次の月を代入します。
Next i

Application.ScreenUpdating = True
End Sub

表はこんな感じにしています。

マクロを動かしてみました。
とりあえず、短めの1年6ヶ月でシミュレーションしていました。

まとめ

いかがでしょうか。
表まで作ることができました。

この後は、
グラフをとったり、条件を二つ比較してみたり、
幅広く展開できると思います。
これらを付け加えればウェブサイトなんかよりもはるかに使い勝手のいいものができると思います。

ご自身のエクセルでもできると思います。
あると便利ですよ。
よかったら作ってみてください。

ではでは。