Help us understand the problem. What is going on with this article?

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

背景

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

ツール本体

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

使い方

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

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

こちらがマイカーローン用のシート。
スクリーンショット 2019-11-06 23.39.28.png

こちらが住宅ローン用のシート。
スクリーンショット 2019-11-06 23.39.14.png

使い方は下記の通り。マイカーローン・住宅ローンどちらも同じ手順で使用できます。
①白色のセル(購入費用、返済期間、年利、ボーナス返済額)にそれぞれに金額・割合を入力
ここでは例として、購入費用35,000,000円、頭金なし、返済期間35年、固定金利3.5%で計算しています。
スクリーンショット 2019-11-06 23.45.20.png

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

③算出結果を一度クリアしたいときは、「算出結果クリア」のボタンを押下する。
名称未設定2.png

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

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

まとめ

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away