【VBA】VBA×投資　PMT関数で目標金額からひと月あたりの積立金額を計算

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 '毎月の積立金額
    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(2).Value + Pv) * 100
'        .Range(4).Value = .Range(3).Value - (.Range(2).Value + Pv) '評価額-（積立額+頭金）
        
        '評価額/最終的な積立金額で達成率を出しています。
        .Range(5).Value = .Range(3).Value / FV * 100
        
'        .Range(5).Value = Format((.Range(3).Value / FV) * 100, "#.#")
    End With
    sttday = DateAdd("m", 1, sttday) '年月を一月プラスして次の月を代入します。
Next i
End Sub

Sub clear()

Dim table As ListObject

Set table = ThisWorkbook.Worksheets("毎月の積立金額").ListObjects(1)

table.DataBodyRange.Delete


End Sub