ローン返済シミュレーションを行うためのVBAサンプル集を整理しました。
借入額・金利・返済期間などを入力すると、毎月の返済額や利息・元金の内訳を自動計算できます。
ローン返済シミュレーション VBAサンプル集
1. 毎月の返済額を計算（Pmt関数）
Sub LoanMonthlyPayment()
Dim loan As Double, rate As Double, nper As Long, monthly As Double
loan = 30000000 ' 借入額（3000万円）
rate = 0.035 / 12 ' 年利3.5% → 月利
nper = 35 * 12 ' 返済期間35年（420回）
monthly = Pmt(rate, nper, -loan)
MsgBox "毎月の返済額: " & Format(monthly, "#,##0") & "円"
End Sub
👉 Pmt関数で「毎月の返済額」を算出。
2. 各期の利息・元金内訳（IPmt / PPmt）
Sub LoanBreakdown()
Dim loan As Double, rate As Double, nper As Long
Dim i As Long, interest As Double, principal As Double
loan = 30000000
rate = 0.035 / 12
nper = 35 * 12
For i = 1 To 12 ' 最初の1年分を表示
interest = IPmt(rate, i, nper, -loan)
principal = PPmt(rate, i, nper, -loan)
Debug.Print i & "回目: 利息=" & Format(interest, "#,##0") & " 元金=" & Format(principal, "#,##0")
Next i
End Sub
👉 IPmt / PPmt関数で「利息部分」と「元金部分」を分解。
3. 総返済額と利息総額
Sub LoanTotal()
Dim loan As Double, rate As Double, nper As Long
Dim monthly As Double, totalPay As Double, totalInterest As Double
loan = 30000000
rate = 0.035 / 12
nper = 35 * 12
monthly = Pmt(rate, nper, -loan)
totalPay = monthly * nper
totalInterest = totalPay - loan
MsgBox "総返済額: " & Format(totalPay, "#,##0") & "円" & vbCrLf & _
"利息総額: " & Format(totalInterest, "#,##0") & "円"
End Sub
👉 総返済額と利息総額を一括算出。
4. 返済予定表をシートに出力
Sub LoanSchedule()
Dim ws As Worksheet
Dim loan As Double, rate As Double, nper As Long
Dim i As Long, interest As Double, principal As Double, balance As Double
Set ws = Sheets("返済表")
ws.Cells.Clear
ws.Range("A1:E1").Value = Array("回数", "返済額", "利息", "元金", "残高")
loan = 30000000
rate = 0.035 / 12
nper = 35 * 12
balance = loan
For i = 1 To nper
interest = IPmt(rate, i, nper, -loan)
principal = PPmt(rate, i, nper, -loan)
ws.Cells(i + 1, 1).Value = i
ws.Cells(i + 1, 2).Value = Pmt(rate, nper, -loan)
ws.Cells(i + 1, 3).Value = interest
ws.Cells(i + 1, 4).Value = principal
balance = balance - principal
ws.Cells(i + 1, 5).Value = balance
Next i
End Sub
👉 返済予定表を自動生成し、各回の返済額・利息・元金・残高を一覧化。
✅ まとめ
- Pmt → 毎月の返済額
- IPmt / PPmt → 利息・元金の内訳
- 総返済額・利息総額 → 支払総コストを把握
- 返済予定表 → 実務でそのまま使えるシミュレーション
💡 この仕組みを応用すれば、ボーナス併用返済や繰上返済シミュレーションも作成可能です。