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

4861 ワード
VBA VBAマクロ ExcelVBA VBA テキストリンク

背景

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

ツール本体

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

使い方

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

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

こちらがマイカーローン用のシート。

こちらが住宅ローン用のシート。

使い方は下記の通り。マイカーローン・住宅ローンどちらも同じ手順で使用できます。
①白色のセル（購入費用、返済期間、年利、ボーナス返済額）にそれぞれに金額・割合を入力
ここでは例として、購入費用３５，０００，０００円、頭金なし、返済期間３５年、固定金利３.5%で計算しています。

②「一括算出」ボタンを押下すると、「毎月の返済額」、「ボーナス時返済額」、「ローン支払総額」、「ローン支払総額と借入額の差額」がそれぞれ赤字で自動算出される。

③算出結果を一度クリアしたいときは、「算出結果クリア」のボタンを押下する。

ソースコードと使用した関数

ローン返済額をボタン１つで一括算出する処理(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/

まとめ

ローンのご利用は計画的に。。。