VBA

【ウェブサイトと同じことマクロVBAで】ひと月あたりの積立金額をエクセル上で調べる方法(PMT関数)

このページでわかること

PMT関数の使い方と
ひと月いくら投資すれば目標金額に到達するかシミュレーションアプリを作ることが出来ます。

こんにちは、hokkyokunです。
目標に向けて投資を頑張ろうって方いらっしゃると思いますが、
目標額から月々の積立金額って案外わからないものです。
複利が付きますからね。

銀行とか証券会社とかのウェブサイトに計算してくれるサイトあると思いますが、
いちいち見に行くの面倒ですよね。
しかもほとんどの場合、結果を記録できないから
毎回調べるのは不便ですよね。

エクセル上で簡単に作れます。
マクロを使えばシミュレーションもできるので
やってみましょう。

1回あたりの積立金額はPMT関数を使って計算

エクセル上で使える関数でPMT関数があるのですが、
1回あたりの積立金額を計算することができます。

構文:Pmt(rate,nper pv[,fv[,type]])

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

具体的に計算してみよう

具体的に計算してみましょう。
利率6.5%、積立年数20年、現在の貯蓄額500万円とし、目標金額6000万円に向けて、一月あたりの積立金額を出してみましょう。

PMT(0.065/12 , 20*12 , 5000000 , 60000000)となります。
Rate(利率)は年利を月利に変換するため0.065/12
積立期間は月数に変換するので20*12
となります。

計算結果は約85,000円ですね。

過程をマクロでシミュレーションしてみよう

マクロを使って積立の過程も計算してみます。
証券会社等のホームページであるようなやつマクロで作ってみました。

Sub 一回当たりの積立金額計算()

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


Set ws = ThisWorkbook.Worksheets("一回あたりの積立金額")
Set table = ws.ListObjects(1)

'入力値を代入
With ws
    Rate = .Range("C4").Value '利率
    Nper = .Range("C5").Value * 12 + .Range("C6").Value '積立回数=積立する月数
    Pv = .Range("C9").Value '手元にある金額
    FV = .Range("C10").Value '目標金額
    Pmt = .Range("C11").Value '1回当たりの積立金額
    sttday = .Range("C7").Value & "/" & .Range("C8").Value & "/01" '開始月を作成
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
End Sub

こんな感じに表をつくります。

ボタンを押すとこんな感じでシミュレーションができます。