住宅ローンの金利上昇が気になる??金利上昇の影響を自分で計算してみましょう!
住宅ローンの金利上昇の返済額への影響は、住宅ローンシュミレータ等でも確認できますが、Excel等のスプレッドシートを使って自分で確認することもできます。今回は、Excelを使った住宅ローン簡易シミュレータを紹介します。
ポイント
- 約8割の住宅ローンは変動金利型になっている
- 元利均等方式は月々の返済額を一定にする方式で多くの住宅ローンは元利均等方式で利用されている
- Excelなどのスプレッドシートにはローン計算を行う関数が用意されている
目次
1. 住宅ローンの種類
住宅ローンには主に3種類の金利タイプがあります。
変動金利型: 金利情勢の変化に伴い借入金利が変動するタイプ
全期間固定金利型: 借入時に借入金利が確定しそのまま一定であるタイプ
固定金利期間選択型: 一定期間固定金利が適用され、その後変動金利に移行するタイプ
一般に、固定金利タイプは変動金利タイプより金利が高く設定されています。2016年にマイナス金利が導入されて以降、変動金利型を選択する人が増え、現在では約8割の人が変動金利型を選択しています。
金利タイプとは別に、金利のかかり方の違いによって、元利均等方式と元金均等方式という二種類の方式があります。元利均等方式は返済額を一定にする方式であり、毎月の返済額を一定にすることで返済計画が立てやすい利点があります。元金均等方式は、毎月の元金返済額を一定にし、利息分を元金に上乗せして返済する方式であり、返済当初は返済額が高く、利息分が減るとともに毎月の返済額も減っていくタイプです。金利が一定であれば、元金均等方式の方が総返済額は少なくなりますが、圧倒的に多くの人が元利均等方式を選択しています。
2. 元利均等方式の計算
それでは、元利均等方式における毎月の計算額はどのように計算すればよいのでしょうか。まず結論から言うと、以下の式で求められます。
毎月の返済額
= 借入金額x利率x(1+利率)^返済回数 / {(1+利率)^返済回数-1}
ほとんどの方は月額返済でしょうから、利率は月利、返済回数は返済月数になります。借入金額をL、利率(月利)をr、返済回数(月数)をnとすると、毎月の返済額xは以下になります。
x = Lr(1+r)^n / {(1+r)^n - 1} 式1
この式を導くには、「等比数列の和の公式」という公式を使います。等比数列とは、初項がa、公比がrの数列、a, ar, ar^2, ar^3, ...の和のことです。
初項から第n項までの和をSnとすると、
Sn=a+ar+ar^2+...+ar^(n-1) 式2
になります。ここで両辺にrをかけると以下のようになります。
rSn=ar+ar^2+ar^3+...ar^n 式3
ここで、式2から式3を引くと、以下のようになります。
Sn-rSn=a-ar^n
∴ (1-r)Sn=a(1-r^n)
∴ Sn=a(1-r^n)/(1-r)
=a(r^n-1)/(r-1)
この式を「等比数列の和の公式」と言います。
さて、元利均等方式の毎月の返済額をx、借入額をL、月利をr、返済回数をn月とします。その場合、nヶ月後の残高は以下のようになります。
1ヶ月後の残高 = (1+r)L-x
2ヶ月後の残高 = (1+r){(1+r)L-x}-x
= L(1+r)^2-x(1+r)-x
3ヶ月後の残高 = L(1+r)^3-x(1+r)^2-x(1+r)-x
...
nヶ月後の残高 = L(1+r)^n-{x(1+r)^(n-1)+x(1+r)^(n-2)+...x(1+r)+x}
ここで、黄色でハイライトした中括弧の中は、xを初項、(1+r)を公比とする等比数列なので、以下のように変換することができます。
nヶ月後の残高 = L(1+r)^n-(x(1+r)^n-1)/{(1+r)-1}
= L(1+r)^n-(x(1+r)^n-1)/r
nヶ月後には残高が0になるはずなので、以下の式が成り立ちます。
L(1+r)^n=(x(1+r)^n-1)/r
これを変形すると、以下になります。
x = Lr(1+r)^n / {x(1+r)^n - 1}
この式は式1と等しいことがわかります。
さて式1をExcelで計算させても良いのですが、Excelにはこの計算を行なってくれる関数が用意されています。この関数はPMT関数と呼ばれ、以下の引数をとります。ちなみにPMTはPayment(支払い)の略のようです。
PMT(利率, 期間, 現在価値, [将来価値], [支払期日])
ここで、現在価値はローンの現在価値(元金)を指定します。将来価値は支払い後の収支でローンの場合は0です。また支払い期日は支払いが期末か期首かを表します。将来価値、支払い期日は省略可能で、省略した場合はそれぞれ0(支払い後収支ゼロ、期末支払い)になります。
ここで、現在価値はローンの現在価値(元金)を指定します。将来価値は支払い後の収支でローンの場合は0です。また支払い期日は支払いが期末か期首かを表します。将来価値、支払い期日は省略可能で、省略した場合はそれぞれ0(支払い後収支ゼロ、期末支払い)になります。
3. 元金均等方式の計算
次に元金均等方式の場合を考えてみます。
元利均等方式の時と同じように、借入額をL、月利をr、返済回数をn月とすると、nヶ月後の返済額は以下のようになります。
1ヶ月後の返済額 = L/n+Lr
2ヶ月後の返済額 = L/n+(L-L/n)r
= L(1/n+r-r/n)
3ヶ月後の返済額 = L/n+(L-2L/n)r
= L(1/n+r+2r/n)
...
nヶ月後の返済額 = L(1/n+r+(n-1)r/n)
元利均等方式よりわかりやすいですよね。
このnヶ月後の返済額を計算するのに便利な関数がExcelに用意されています。ISPMT関数です。この"IS"の意味は正直よくわかりませんが、この関数は元金均等払いで指定した期に支払う利子を計算してくれます。
ここで、2番目の引数である「期」は最初の期が0ですので注意してください。4番目の引数は現在価値はローンの現在価値(元金)を指定します。
4. Excelによるシミュレーション
さて、実際にExcelでローンのシミュレーションをしてみましょう。私が作成したExcelファイルをここに置いておきます。
このシートは元利均等方式と元金均等方式で、金利が変動した場合にどの程度支払い総額に差が出るのかを計算するものです。
シートは以下のような形式になっています。
青い部分に月額払いとボーナス払いの元金の残高、残月数、現在および変動後の年利を入力します。上記の例は、元利金等返済で残月数240ヶ月(20年)、残高1,500万円(内ボーナス分500万円)、金利が0.7%から1.2%に変動した場合を計算しています。月額返済額が2,000円強増えて、返済総額は80万円ほど増加することがわかります。
ちなみにボーナス払いは年2回、6ヶ月に一度を想定しています。
元金均等方式の場合、月々の支払額が一定でないので総額計算はちょっと厄介です。Excelで総額を計算してくれる関数が見つからなかったので、このシートでは総額を計算するためのシートを別に用意しています。WorkとWork2というシート内で月々、およびボーナス毎の支払額を計算し、それを集計しています。同じ金利条件で計算してみると、元金均等方式の方が返済総額が少なく、また金利上昇の影響がくも少なくなっているのがわかります。
銀行によって端数処理が異なっていたりするので、1円単位では合わない可能性がありますが、概算を掴むには十分だと思います。ご自分の用途に従って、Excelシートを修正して使っていただければと思います。
5. まとめ
金利上昇で気になる住宅ローンへの影響を自分で計算する方法を紹介しました。参考にしていただければと思います。
最後までお読みいただき、ありがとうございます。
1級ファイナンシャルプランニング技能士
CFP®️認定者
1級DCプランナー
コメント
コメントを投稿