VBAで作成したローン返済シミュレーションツール

# 背景
家やクルマの購入を検討されている方は誰もが検討するローン。
複数の金融機関の検討の際に役に立つのが月々の支払いのシミュレーションですね。
各金融機関が提供している無料のシミュレーションサービスを使用している方もと思いますが、これが自前で用意できれば便利だなと思ったので、Excel VBAで作ってみました。

# ツール本体
ツール本体はgithub内にあるので、こちらからダウンロードしてください。
https://github.com/Seki14/LoanSimulation

#使い方
まず、"ローン返済シミュレーション（V1.0）.xlsm"を開きます。
マイカーローン用、住宅ローン用の２つのシートを用意しています。
マイカーローンは最大５パターン
住宅ローンは最大６パターン入力できるようにしています。

※マイカーローンはライフプランの中で見直しが発生するケースがあると思うので、
 前車の返済プランの入力欄を設けて比較できるようにしています。

こちらがマイカーローン用のシート。
<img width="1144" alt="スクリーンショット 2019-11-06 23.39.28.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/197189/0e320ded-d9d7-c580-1374-cbf368609d06.png">

こちらが住宅ローン用のシート。
<img width="1173" alt="スクリーンショット 2019-11-06 23.39.14.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/197189/316eb287-cbca-6bc9-bdff-16a0f96cfcad.png">

使い方は下記の通り。マイカーローン・住宅ローンどちらも同じ手順で使用できます。
①白色のセル（購入費用、返済期間、年利、ボーナス返済額）にそれぞれに金額・割合を入力
ここでは例として、購入費用３５，０００，０００円、頭金なし、返済期間３５年、固定金利３.5%で計算しています。
<img width="742" alt="スクリーンショット 2019-11-06 23.45.20.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/197189/7a890a49-f92e-99a1-6a8c-c30e47c71eba.png">

②「一括算出」ボタンを押下すると、「毎月の返済額」、「ボーナス時返済額」、「ローン支払総額」、「ローン支払総額と借入額の差額」がそれぞれ赤字で自動算出される。
![名称未設定.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/197189/4a736136-7661-f802-1389-c73f16ca9365.png)

③算出結果を一度クリアしたいときは、「算出結果クリア」のボタンを押下する。
![名称未設定2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/197189/ddddda93-3270-bdcb-ca46-28f085c1cbfd.png)

# ソースコードと使用した関数
ローン返済額をボタン１つで一括算出する処理(Loan_Calc.bas)と
算出結果をクリアする処理(Val_Clr.bas)をVBAで実装しています。
(やってることは実行したいExcel関数をコールしているだけです）

```Loan_Calc.bas
'*****************************************************
'*** Loan_Calc():ローン返済額一括算出処理関数  ***
'*****************************************************
Sub Loan_Calc()

'パターン1算出
Range("B10") = "=B4-B5" '借入額
Range("B13") = "=ABS(PMT(B8/12, B7*12, B10-B11))" '毎月の返済額
Range("B14") = "=ABS(PMT(B8/2,B7*2,B11))" 'ボーナス時返済額
Range("B16") = "=((B13*12)+(B14*2))*B7" 'ローン支払総額
Range("B17") = "=B16-B10" 'ローン支払と実費との差額

'パターン2算出
Range("B26") = "=B20-B21" '借入額
Range("B29") = "=ABS(PMT(B24/12, B23*12, B26-B27))"  '毎月の返済額
Range("B30") = "=ABS(PMT(B24/2,B23*2,B27))"  'ボーナス時返済額
Range("B32") = "=((B29*12)+(B30*2))*B23" 'ローン支払総額
Range("B33") = "=B32-B26" 'ローン支払と実費との差額

'パターン3算出
Range("E10") = "=E4-E5" '借入額
Range("E13") = "=ABS(PMT(E8/12, E7*12, E10-E11))" '毎月の返済額
Range("E14") = "=ABS(PMT(E8/2,E7*2,E11))" 'ボーナス時返済額
Range("E16") = "=((E13*12)+(E14*2))*E7" 'ローン支払総額
Range("E17") = "=E16-E10" 'ローン支払と実費との差額

'パターン4算出
Range("E26") = "=E20-E21"  '借入額
Range("E29") = "=ABS(PMT(E24/12, E23*12, E26-E27))" '毎月の返済額
Range("E30") = "=ABS(PMT(E24/2,E23*2,E27))" 'ボーナス時返済額
Range("E32") = "=((E29*12)+(E30*2))*E23" 'ローン支払総額
Range("E33") = "=E32-E26" 'ローン支払と実費との差額

'パターン5算出
Range("H26") = "=H20-H21" '借入額
Range("H29") = "=ABS(PMT(H24/12, H23*12, H26-H27))" '毎月の返済額
Range("H30") = "=ABS(PMT(H24/2,H23*2,H27))" 'ボーナス時返済額
Range("H32") = "=((H29*12)+(H30*2))*H23" 'ローン支払総額
Range("H33") = "=H32-H26" 'ローン支払と実費との差額

'前回車ローン比較
Range("H10") = "=H4-H5" '借入額
Range("H13") = "=ABS(PMT(H8/12, H7*12, H10-H11))" '毎月の返済額
Range("H14") = "=ABS(PMT(H8/2,H7*2,H11))" 'ボーナス時返済額
Range("H16") = "=((H13*12)+(H14*2))*E7" 'ローン支払総額
Range("H17") = "=H16-H10" 'ローン支払と実費との差額

End Sub
```

```Val_Clr.bas
'*****************************************************
'*** Val_Clr():算出結果クリア処理関数  ***
'*****************************************************
Sub Val_Clr()

Range("B10, B13, B14, B16, B17").ClearContents
Range("B26, B29, B30, B32, B33").ClearContents
Range("E10, E13, E14, E16, E17").ClearContents
Range("E26, E29, E30, E32, E33").ClearContents
Range("H10, H13, H14, H16, H17").ClearContents
Range("H26, H29, H30, H32, H33").ClearContents

End Sub
```
ローンの返済額の算出には、ExcelのPMT関数を使用しています。
PMT関数の使い方は下記サイトが非常にわかりやすいです。
https://dekiru.net/article/4633/

# まとめ
ローンのご利用は計画的に。。。