こちらに「繰上返済を考慮したローンシミュレーション」のVBAサンプルを整理しました。
通常の返済シミュレーションに「繰上返済（期間短縮型／返済額軽減型）」を組み込んだ例です。
繰上返済シミュレーションの基本構造
1. 前提条件
- 借入額：3,000万円
- 年利：3.0%（固定）
- 返済期間：35年（420回）
- 繰上返済：5年後に100万円を一括返済
2. コード例（期間短縮型）
Sub LoanWithPrepayment_ShorterTerm()
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
Dim monthly As Double
Set ws = Sheets("返済表")
ws.Cells.Clear
ws.Range("A1:F1").Value = Array("回数", "返済額", "利息", "元金", "残高", "備考")
loan = 30000000
rate = 0.03 / 12
nper = 35 * 12
balance = loan
monthly = Pmt(rate, nper, -loan)
For i = 1 To nper
interest = IPmt(rate, i, nper, -loan)
principal = PPmt(rate, i, nper, -loan)
' 繰上返済（60回目＝5年後）
If i = 60 Then
balance = balance - 1000000
ws.Cells(i + 1, 6).Value = "繰上返済 -100万円"
End If
balance = balance - principal
ws.Cells(i + 1, 1).Value = i
ws.Cells(i + 1, 2).Value = monthly
ws.Cells(i + 1, 3).Value = interest
ws.Cells(i + 1, 4).Value = principal
ws.Cells(i + 1, 5).Value = balance
If balance <= 0 Then Exit For
Next i
End Sub
👉 期間短縮型：繰上返済で残高を減らし、返済期間を短縮。毎月の返済額は変わらない。
3. コード例（返済額軽減型）
Sub LoanWithPrepayment_LowerPayment()
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
Dim monthly As Double
Set ws = Sheets("返済表")
ws.Cells.Clear
ws.Range("A1:F1").Value = Array("回数", "返済額", "利息", "元金", "残高", "備考")
loan = 30000000
rate = 0.03 / 12
nper = 35 * 12
balance = loan
monthly = Pmt(rate, nper, -loan)
For i = 1 To nper
interest = balance * rate
principal = monthly - interest
' 繰上返済（60回目＝5年後）
If i = 60 Then
balance = balance - 1000000
' 残高に基づき再計算（返済額軽減）
monthly = Pmt(rate, nper - i, -balance)
ws.Cells(i + 1, 6).Value = "繰上返済 -100万円（返済額再計算）"
End If
balance = balance - principal
ws.Cells(i + 1, 1).Value = i
ws.Cells(i + 1, 2).Value = monthly
ws.Cells(i + 1, 3).Value = interest
ws.Cells(i + 1, 4).Value = principal
ws.Cells(i + 1, 5).Value = balance
If balance <= 0 Then Exit For
Next i
End Sub
👉 返済額軽減型：繰上返済後に毎月の返済額を再計算し、負担を軽減。
✅ まとめ
- 期間短縮型 → 毎月の返済額は変えず、返済期間を短縮
- 返済額軽減型 → 返済期間は変えず、毎月の返済額を減らす
- VBAで返済表を自動生成すれば、繰上返済の効果（利息軽減額・期間短縮効果）を可視化できる
💡 この仕組みを応用すれば、複数回の繰上返済やボーナス併用返済にも対応可能です。