今回は、Excelを使って、住宅ローンの毎月の返済額を求めてみたいと思います。
元利均等返済における毎月の支払額をExcelの関数（PMT関数）を使って求めてみましょう。
はじめに、元利均等返済と元金均等返済について説明したいと思います。
元利均等返済と元金均等返済
元利均等返済
元利均等返済とは、毎回の返済額（元金＋利息）が一定になる返済方法です。（図１）
図１：元利均等返済のイメージ
返済期間の初期は、利息部分の返済が多くなり、返済期間が経過するとともに元金部分の返済が多くなっていきます。
元金均等返済
元金均等返済とは、毎回の返済額のうち、元金が一定になる返済方法です。（図２）
図２：元利均等返済のイメー
元利均等返済では、返済期間の初期は元金が多いため、返済額も多くなりますが、返済期間が進むにつれて、元金も小さくなるため、毎回の返済額も少なくなっていきます。
返済期間が同一の場合、総支払額は元利均等返済の方が多くなります。
今回は、元利均等返済における、毎月の返済額について見ていきます。元金均等返済の毎月の返済額は、（元金 ÷ 返済回数）＋（元金 ÷ 返済回数）× 利率で求められるため、ここでは省略させていただきます。
元利均等返済における毎月の返済額をPMT関数を使って求める
元利均等返済における毎月の返済額はPMT関数という関数を使って求めます。
今回は、例として、利率1.5%、借入額3,000万円、35年返済という設定で、毎月の支払額がいくらになるかPMT関数を使って計算してみます。
例１）利率1.5%、借入額3,000万円、35年返済の毎月の返済額
図３のようにB6セルに毎月の返済額を表示させることにします。借入額はB2セル、利率はB3セル、年数はB4セルに入力しておきます。
図３：PMT関数（使用例）
それではPMT関数を使っていきましょう。
PMT関数（PayMenT）
一定の利率の支払が定期的に行われる場合のローンの定期支払額を求める関数です。
利率
第一引数の利率は、金利を入力します。
今回はB3セルが利率なので、B3を入力しますが、月単位の支払のため、12で割る必要があり、B3/12を入力します。
期間
第二引数の期間は、借入の期間を入力します。ここでは、支払回数を入力します。
今回は35年で毎月支払いを行います。そのため、1年あたり12回支払を行うため、35*12となります。今回B5セルに年数が入力されているため、引数にはB5*12を入力します。
現在価値
第三引数の現在価値は、借入額を入力します。
今回は借入額の30,000,000がB2に入力されていますので、引数にはB2を入力します。
将来価値（省略可能）
第四引数の将来価値は、最後の支払を終えた後に残っている金額（価値）を指定します。
今回は使用しないので省略します。
支払期日（省略可能）
第五引数の支払期日は支払が行われる時期を、0または1で指定します。0または省略の場合、期末支払、1を指定したときは、期首支払となります。
今回は省略します。
以上をまとめると次のようになります。
今回のポイントは、第一引数の利率と第二引数の期間の単位をそろえることです。住宅ローンにおいては、月単位の支払となるため、月単位でそろえる必要があります。第一引数の利率は12で割り、第二引数の期間の35年については、支払回数となるため、12を掛けるという感じになります。このことを忘れてしまうと、正しく出力されないため注意しましょう。
また、PMT関数の結果であるB6セル（図３）がマイナスとなっています。PMT関数は返済額の場合、マイナスになります。マイナスが気持ち悪いよという方は、PMT関数の引数の現在価値にマイナスで入力するといいかもしれません。
PMT関数でボーナス払い分を求める
最後に、PMT関数を使ってボーナス払い分をどのように求めるか、考えてみましょう。
例2）利率1.5%、借入額3,300万円、内ボーナス分300万円、35年返済の毎月の返済額
例２のような場合を考えてみます。
ボーナス分は通常、夏と冬の年2回の返済となります。ということは、第一引数の利率は利率を2で割り、第二引数の期間は返済回数が1年あたり2回、35年返済のため、35に2をかけることで求めることができます。
これで、ボーナス時の返済額を求めることができます。
-55,244と出ると思います。毎月の返済額は例１で求めたので、ボーナス月については、例１で求めた額に55,244分の返済額が必要になるということになります。
補足
PMT関数で求めた結果については、小数点部分がありますので、小数点以下の取り扱いには注意しましょう。
住宅ローンに関係する関数はPMT関数の他にもあり、元利均等返済における毎回の返済額の元金部分を求めるPPMT関数や毎月の返済額の利息部分を求めるIPMT関数、元金均等返済の利息部分を求めるISPMT関数という関数もあります。
さらに、元金部分の累計額を求めるCUMPRINC関数や利息部分の累計額を求めるCUMIPMT関数という関数も存在します。これらの関数は別記事で紹介予定です。
コメント