【Excel】ローンの返済シミュレーターを作ってみよう【VBAマクロ】

2023-01-262024-05-06

当ページのリンクには広告が含まれています。

こんにちは、ふじみやです。

皆さんは家や車の購入をご検討されたことはありませんか？

人生でもトップクラスに高額な買い物ですので住宅ローンやオートローン（マイカーローン）を利用しようと考えて、ローンの返済シミュレーションをするWebサイトやアプリを利用されたことがある方もいらっしゃることと思います。

とはいえ、これらのサービスだと微妙に使い勝手が悪いと感じることもしばしば。そこで、今回は Excel を使って自分で返済シミュレーターを作る方法についてご紹介します。

とにかく早くシミュレーションをしたいという方は今回作成した Excelファイルのダウンロードリンクを記事の最後に掲載していますのでダウンロードしてご利用ください。

Excelに関する記事一覧

Webで見つかるシミュレーター

Google 等で「住宅ローン返済シミュレーション」のように検索をすると様々な金融機関が提供する返済シミュレーターが簡単に見つかります。

中身は大体一緒で、「借入希望額」「ボーナス返済額」「返済方法」「返済期間」「借入利率」を入力すると毎月の返済額が試算されるものです。

これはこれで簡単に試算ができて便利ですが、返済額から借入額を算出しようとすると何回も試算をする必要があり面倒に感じてしまうこともあります。

シミュレーターの作成手順

そこで、今回は返済額から借入額が算出できるシミュレーターを作成しますが、もちろん借入額から返済額を算出するという機能もありますのでご安心ください。

作成するシミュレーター

シミュレーターを作成するためには、以下の条件が必要となります。

借入期間
借入利率
毎月の返済希望額
ボーナス分の返済希望額

ちなみに完成形としては以下のようなイメージです。

シミュレーションをするために手入力が必要な箇所は一見して分かるように、グレーセルに青字としています

仮の前提条件表作成

まずは仮の数値で構いませんので、前提条件表を作成していきましょう。

借入希望額
借入希望額のうち、毎月返済分（借入希望額 – ボーナス返済分）
借入希望額のうち、ボーナス返済分
返済期間
借入利率

返済方法の「元利均等」はシミュレーション上では何の影響もありませんが、このシミュレーターが元利均等返済における返済額を算出しているとわかるようにメモ的に記載をしています。

返済希望額表の作成

次に仮条件に基づく返済額を算出する数式と、実際に希望する返済額を手入力してきましょう。

元利均等返済における毎回の返済額を計算する関数として PMT関数 というものがありますので、これを活用します。

毎月返済額：　=-PMT(利率/12,返済期間*12,毎月返済分の借入額)
ボーナス返済額：=-PMT(利率/2,返済期間*2,ボーナス返済分の借入額)

ここまでの入力をまとめると以下のような形になります。

シミュレーション結果の表示箇所作成

シミュレーション用のマクロを作成する前に、シミュレーション結果を表示する箇所を作成しておきましょう。

シミュレーション用のマクロ作成

このシミュレーションのキーとなるのは Excel の「ゴールシーク」と呼ばれる機能です。

「ゴールシーク」機能は「データ」タブ内の「What-If分析」にあり、数式が入力されている計算結果セル（毎月返済額）を目標値（希望返済額）になるように、インプットデータ（借入希望額やボーナス返済額）を逆算するというものです。

とはいえ、何度もシミュレーションをしようとした場合、ゴールシークを選択して必要事項をインプットして、というのを何度も繰り返すのは手間ですよね。

そこで、ボタン一つで計算ができるようにマクロを作ろうというわけです。

マクロについての説明は割愛しますが、私と同じようにファイルを作っているならば、以下をVisual Basic内の標準モジュール（Module 1）に貼付すればすぐに使えるようになります。

Sub シミュレーション()

'シミュレーション結果の表示を削除(=初期化)
    Range("G23:G27").Select
    Selection.ClearContents

'仮の借入希望額をシミュレーション結果欄に貼付(値を貼付)    
    Range("G5:G7").Select
    Selection.Copy
    Range("G23").Select
    Selection.PasteSpecial Paste:=xlPasteValues

'毎月返済額を「借入希望額-ボーナス返済額」の計算式に置換
    Range("G24").Select
    ActiveCell.Formula = "=G23-G25"

'PMT関数を入力    
    Range("G26").Select
    ActiveCell.Formula = "=-PMT(G10/12,G9*12,G24)"   
    Range("G27").Select
    ActiveCell.Formula = "=-PMT(G10/2,G9*2,G25)"

'返済希望額より借入額を逆算(ゴールシーク機能を利用)
    Range("G27").GoalSeek Goal:=Range("G14"), ChangingCell:=Range("G25")
    Range("G26").GoalSeek Goal:=Range("G13"), ChangingCell:=Range("G23")

'シミュレーション結果について値を貼付      
    Range("G23:G27").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Range("G23").Select
        
End Sub

コードを貼り付けた後は、F5キーを押下するか ▶ をクリックしてちゃんと動作するか確認をしておきましょう。