住宅ローンは自分で計算できる！エクセルを使った計算方法とは？

住宅ローンの返済額は、利子の計算もあってわかりづらいと感じる人が多いのではないでしょうか。しかし、実は固定金利であれば、エクセルを使って簡単に計算する方法があるのです。

今回は、エクセルを使った住宅ローンの計算方法をご紹介します。

住宅ローンの返済方法には2種類ある！

住宅ローンの金利を計算する前に、住宅ローンの返済方法について確認しておきましょう。返済方法には、元利均等返済・元金均等返済という2種類があります。


元利均等返済とは

元利均等返済とは、毎月の返済額が常に一定となる返済方法のことです。一方で、上図を見るとわかる通り、返済期間が進むにつれて返済額に占める元金部分の割合が大きくなっていることがわかります。元金部分とは、毎月の返済額のうち、借入額の返済に充てられる金額のこと。

図中で具体的に見ると、返済期間初期の（A）と後期の（B）は返済額こそ等しいですが、元金部分は（B）の方が大きくなっています。

毎月の返済額が等しく返済計画を立てやすい点は、元利均等返済のメリットと言えるでしょう。


元金均等返済とは

対する元金均等返済とは、毎月の返済額における元金部分の金額が一定になる返済方法のこと。上図を見ると、返済期間を通じて元金部分の金額が一定になっています。ただし、利息部分は返済期間が進むにつれて減っていくため、合計の返済額も減少していくのが特徴です。

図中で考えると、返済期間初期の（A）よりも後期の（B）の方が、利息部分が少ない分だけ返済額合計も少ないことがわかりますね。

元金部分を一定で返済していくので、同じ期間でローンを組んだ場合、元利金等返済よりも総返済額が少なくて済む点がメリットです。ただ、返済当初の月返済額が高くなるというデメリットもあります。

元利均等返済における金利の計算方法

住宅ローンの返済方法について確認したところで、さっそく金利の計算方法を見ていきましょう。
なお、金利タイプには固定金利と変動金利があります。変動金利では、市中金利の状況によって金利が常に変動するため、シミュレーションするのは困難です。そこで、今回は固定金利で借り入れる場合を想定します。

所定の公式に数値を当てはめれば、手計算でも金利を求めることは可能です。しかし、実際には計算内容が複雑になってしまいます。

こうした問題を解決してくれるのが、表計算ソフトのエクセルを使った計算方法です。元利均等返済における金利を計算する際には、「IPMT関数」という関数を使用します。入力する数式は以下の通りです。

　●IPMT（利率,期,期間（借入期間）,現在価値（借入額）,将来価値,支払期日）

上記の式のうち、「将来価値」は「0」と入力します。また、支払期日については入力しなくても問題ありません。この時、利率は月利（＝年利÷12）、期間は月単位で計算する点に注意。この式を入力すれば、毎月の返済額のうち、金利額だけを簡単に計算できるのです。

例えば、借入額3,000万円・固定金利1.0％・借入期間35年の住宅ローンを元利均等返済で組むとしましょう。このローンにおける「10年目最終月（120ヶ月目）の利息返済額」は、次のように計算できます。

　●IPMT（1.0％/12,120,35*12,3000万円,0）＝ 18,780円
 

元金均等返済における金利の計算方法

次に、元金均等返済における金利の計算方法も見ていきましょう。元利均等返済と同様、公式から手計算するには、なかなかの計算力が必要となります。

そのため、おすすめなのはエクセルを用いた方法です。元金均等返済における金利計算で使用するのが、「ISPMT関数」という関数です。入力する数式は以下の通りです。

　●ISPMT（利率,期,期間（借入期間）,現在価値（借入金額））

元利均等返済の場合と同様、借入額3,000万円・固定金利1.0％・借入期間35年の住宅ローンを元金金等返済で組む場合を想定してみましょう。「10年目最終月（120ヶ月目）の利息返済額」は、次のように計算できます。

　●ISPMT（1.0％/12,120,35*12,3000万円）＝ 17,857円

こうやって実際に計算してみると、元金均等返済の方が利息返済額が少なくなるということがよくわかりますね。

元利均等返済の計算方法

今回は、固定金利型で組んでいる場合を想定しています。変動金利型は、金利によって返済額が変化するので、今回ご紹介する計算方法は当てはまりません。

計算方法を分かりやすく伝えるため、次のような住宅ローンを借り入れることを想定してシミュレーションしていきます。

・借入額　　：3,000万円
・借入期間　：35年
・金利　　　：1.0％

まず、返済方法を元利均等返済としている場合の計算方法を見ていきましょう。


月返済額の計算方法

ローンのシミュレーションをするにあたって、最も気になるのが月の返済額ですよね。元利均等返済の場合、月返済額が期間中一定になります。
 
元利均等返済の月返済額を求めるのに使うのが「PMT関数」です。PMTとはpaymentの略であり、次のように値を指定することで、月返済額を求めることができます。

「=PMT(利率,期間,現在価値,将来価値,支払い期日)」

 利率：月ごとの利率（年利を12ヶ月で割ったもの）
・期間：返済回数（月でカウント）
・現在価値：借入金額
・将来価値：最終的に借入金額をいくらにしたいかを設定（この場合は「0」もしくは入力なし）
・支払期日：入力なし

上記の画像は、実際に数値を入れたエクセル画面。今回の想定だと、利率＝1.0％÷12、期間＝35年×12ヶ月、現在価値＝3,000万円となります。

 

設定通り入力すると、月返済額は84,686円と計算することが可能。数値を変更するだけで、月返済額を簡単に求めることができるのです。

 
月返済額のうち利息支払い分の計算方法

元利均等返済は月返済額が一定ですが、利息支払い分と元金支払い分の割合は変化します。そこで、返済期間中のある月における利息支払い分の計算方法もご紹介しましょう。

ある月の返済額のうち、利息支払い分を計算するのに使うのが「IPMT関数」です。

「=IPMT(利率,期,期間,現在価値,将来価値,支払い期日)」

 ・期：利息支払い分を求めたい月（10年目末であれば120ヶ月）
・その他はPMT関数と同様

 

計算結果からわかるそれぞれの特徴

上記の画像が、10年目末の返済額に占める利息支払い分を求める際の画面。返済額を求めたい月を、支払い始めからの月数で入力するのがポイントです。

 

先ほどの式を入力すると、10年目末の返済額に占める利息支払い分は18,780円ということがわかりました。

 
なお、元金支払い分は月支払い額から利息支払い分を引けば求められますが、「PPMT関数」という関数を用いても計算可能です。使い方は、先ほどのIPMT関数をPPMT関数に置き換えるだけです。
 

「PMT関数」「IPMT関数」「PPMT関数」を使用すれば、元利均等返済の返済額はすぐに求めることができます。

 

元金均等返済の計算方法

続いては、元金均等返済で住宅ローンを組んでいる場合の計算方法を見ていきましょう。

 元金均等返済の場合、月返済額のうち元金返済分が一定になります。そのため、月返済額は利息返済分によって変化し、完済時に向けて徐々に返済額が小さくなっていくのが特徴です。

今回も、元利均等返済時に用いたローンを組む想定で計算していきます。

 
ある月の利息支払い分の計算方法

元金均等返済の月支払い額を求めるには、月々で変化する利息支払い分を計算する必要があります。ある月における利息支払い分を求めるのに使うのが「ISPMT関数」です。エクセルでは、次のような式で指定します。

 
「=ISPMT(利率,期,期間,現在価値)」

 

実際にISPMT関数を入力したのが、上の画像。IPMT関数と同様、支払い額を求めたい月を、支払い始めからの月数で入力するのが重要です。

 

計算結果は上記の画像の通りで、10年目末の月には17,857円の利息を支払うことがわかりますね。

元金均等返済の場合元金支払い分は一定ですので、今回の想定では3,000万円 ÷（35年×12ヶ月）≒71,428円となります。よって、10年目末の月支払い額は17,857＋71,428＝89,285円ほど、と計算できるわけです。


総返済額の計算方法

元金均等返済において総返済額を求めたい場合、一発で計算できる関数というのは存在しません。ただ、ISPMT関数を用いればすべての月の返済額を求めることは可能ですから、エクセル上で足し込んでいけば総返済額を求めることもできます。

 

計算結果からわかるそれぞれの特徴

元利均等返済と元金均等返済それぞれの月返済額の求め方がわかったところで、計算結果から返済方法の特徴が浮かび上がってきます。

 

取り上げた方法で毎月の返済額を計算してみると、上の画像の通り、支払い開始当初は元利均等返済の方が、月返済額が小さくなっていますね。

 

ところが、元金均等返済は月返済額が徐々に小さくなっていき、支払い開始から16〜17年のところで元利均等返済の月支払い額を下回っていることがわかります。

 

最終的な総支払い額で比較しても、元金均等返済の方が、支払い利息が小さく、総支払い額も少なくなるのです。


このことからも、初期の支払い余力があるならば、元金均等返済の方が、最終的に支払い額が少なくて済むということが、よくお分かりいただけるのではないでしょうか。

 

銀行の返済シミュレーションを有効活用しよう

ここまでエクセルを用いた金利計算の方法をご紹介してきましたが、より簡単に返済額を計算する方法として、各銀行が提供している返済シミュレーションがあります。計算結果には返済額しか表示されないため、利息部分だけを求めたい場合には、エクセルの計算式を併用するといいでしょう。

　●みずほ銀行「住宅ローン返済額シミュレーション」

　●楽天銀行「住宅ローンシミュレーション」

　●フラット35「クイック・シミュレーション」

まとめ

今回は、元利均等返済・元金均等返済それぞれについて、住宅ローンの支払い額計算方法をご紹介してきました。エクセルを使えば簡単に返済額を求められます。

 

ぜひこの記事の内容を参考に、自身の住宅ローンについて検討してみてはいかがでしょうか。