住宅ローンの利息の計算方法!エクセルとシミュレーションツールで、住宅ローンを比較

住宅ローン利息計算
じぶん銀行
  • このエントリーをはてなブックマークに追加

住宅ローンを利用する際に気になるのは、利息だと思います。

利息は借入金額や金利、借入期間によって異なりますが、金利は金融機関や金利タイプ(変動金利型や固定金利型)だけでなく、融資率(購入費や建築費に対する借入金の割合)によっても差があります。

果たして利息はどのように計算されているのでしょうか。今回は、住宅ローンの利息の計算方法を中心に、比較の仕方を解説していきます。

今回は、エクセルを使う方法と、Webでのシミュレーションツールを使う方法の2パターンを紹介していきます。

エクセルに慣れている方は、一度関数を組んでしまえば長く使えるものになるのでおすすめなのですが、エクセルに不慣れな人はWebの住宅ローンシミュレーションツールを使用しつつも、エクセルにもチャレンジしてみてください。

では、実際に見ていきましょう!

住宅ローンの利用で気になる「収入と借入金額」の条件

住宅の建築費や購入資金を借りたい場合、住宅ローンの審査に通る必要があります。審査の条件で一番気になるのは、希望する金額を借りられるかどうかではないでしょうか。

住宅ローンの借り入れ条件で「返済負担率」と呼ばれるものがあります。返済負担率は、年収に対する総借入金額の割合のことで、たとえば年収400万円の人が年間120万円の返済をしている場合の返済負担率は30%(120万円÷400万円)となります。

総借入金額には住宅ローンだけでなく、他の借入金すべて含まれます

たとえば住宅ローン以外の返済額が年間30万円あると、年収400万円の人で返済負担率を30%以内におさえるためには、住宅ローンの年間返済額は90万円(120万円―30万円)以内にする必要があります。

これから住宅ローンを利用したいと考えている人は、重要な条件の一つですので覚えておきましょう。

返済負担率の考え方

  1. 返済負担率を範囲内におさえたい場合の、住宅ローンの年間返済額の求め方
    年収×返済負担率-他の年間返済額=住宅ローンの年間返済額
    例)400万円×30%-30万円=90万円  ※返済負担率30%以内におさえる
  2. 住宅ローンの年間返済額が決まっている場合の、返済負担率の求め方
    (住宅ローンの年間返済額+他の年間返済額)÷年収=返済負担率
    例)(90万円+30万円)÷400万円=30%

エクセルで住宅ローンの利息を計算する方法

住宅ローンは借入金額が大きくなるのが一般的です。金額が大きく、借入期間も長いため、利息の負担が大きくなります。そのため利息がどのくらいになるか気になるのではないでしょうか。ここでは利息の計算方法について解説していきます。

エクセルで計算する

エクセルなどの表計算ソフトを使う場合の手順について解説します。(元利返済方式
まず、完成後のエクセルの関数をお見せします。こちらの画像のエクセル関数の組み方を、下記で詳しく説明していきます。

完成後は、「借入金額」「金利」「借入期間」(下記のC列)を、ご自身のシミュレーションに合わせて入力するだけで、金額が自動的に反映されます。

エクセル関数

最初に下記のように、利息計算に最低限必要な、借入金額金利借入期間を入力します。

借入金額 30,000,000円
金利 1.00%(=0.01)
借入期間 35年

次に、毎月の返済額、元金(借入金部分)、利息を計算します。それぞれ、次のエクセル関数を使うと求めることができます。

毎月返済額 = -pmt(金利/12,返済期間*12,借入金額)

元金 = -ppmt(金利/12,返済回数,返済期間*12,借入金額)

利息 = -ipmt(金利/12,返済回数,返済期間*12,借入金額)

金利を1%、借入期間を35年、借入金額を3,000万円とした場合、それぞれ次のような計算式になることに注意します。

  • 利率=金利 0.01÷12ヵ月 ※金利は年利率なので月利率にするため12で割る。
  • 期間=返済期間 35年×12ヵ月 ※1ヵ月の返済額を求めるため、12で掛ける。
  • 借入金額 30,000,000円(そのまま)
  • 返済回数=支払回数 全420回のうち、何回目の支払いか

これらを参考に、エクセルシートに関数を入力します。まず、回数を420回(35年×12ヵ月)分入力し、1回目のところに関数を入れていきます。

回数 毎月返済額 元金 利息
1 ①=-pmt() ②=-ppmt() ③=-ipmt()
2 =-pmt() =-ppmt() =-ipmt()
3 =-pmt() =-ppmt() =-ipmt()

上記の例の場合、F,G,H列に関数を入れますが、実際に入力した関数は次のようになります。
※便宜上、①~③の表記をしておりますが、実際での入力は不要です。

  • ①=-PMT($C$4/12,$C$5*12,$C$3)
  • ②=-PPMT($C$4/12,E4,$C$5*12,$C$3)
  • ③=-IPMT($C$4/12,E4,$C$5*12,$C$3)

この関数を①~③の場所に入力し、返済期間に合わせた回数を入力(最初の完成図ではE列のこと。)し、その該当回数の箇所までコピーをすることで、完済するまでに必要な月々の支払や、利息の推移が一目瞭然です。

ちなみに正しく入力できていれば、下記の金額になります。

回数 毎月返済額 元金 利息
1 ¥84,686 ¥59,686 ¥25,000
2 ¥84,686 ¥59,735 ¥24,950
3 ¥84,686 ¥59,785 ¥24,900

3,000万円を金利1%、借入期間35年で借りると、1回目で25,000円の利息を支払うことが分かります。

エクセルや関数に慣れていないと準備に手間取るかもしれませんが、一度作ってしまえば、あとは借入金額や金利、借入期間を変更するだけで自動的に計算できるので、簡単にシミュレーションすることができます。

なお、今回は3つの関数を使いましたが、実際には、「毎月の返済額=元金+利息」になりますので、2つだけ今回の関数を使い、残りの一つは下記のように四則計算で求めても構いません。

回数 毎月返済額 元金 利息
1 =G2+H2 =-ppmt() =-ipmt()
2 =-pmt() =F3-H3 =-ipmt()
3 =-pmt() =-ppmt() =F4-G4

※回数1~3、いずれのパターンでも可。

シミュレーションツールで利息を計算する方法

エクセルによる計算方法を紹介しましたが、ネットにアクセスする必要がなく、カスタマイズ可能ですので、利便性は高いですが、エクセルや関数を使い慣れていない人にとってはハードルが高いかもしれません。

各金融機関のサイトにはシミュレーションツールが用意されていますので、シミュレーションツールでの計算方法について解説していきます。

なお、借入先の候補になりそうな金融機関のシミュレーションツールを使用すればいいと思いますが、ここではみずほ銀行を例にします。

みずほ銀行 返済シミュレーションツール

出典:みずほ銀行 シミュレーションツール

エクセルの項目でも解説しましたが、住宅ローンの返済額は、借入金額と借入期間、金利を決めれば計算できます。みずほ銀行のシミュレーションツールでも「必須」という項目だけ入力すれば簡単に求めることができます。

  • いくら借りますか?-3000(万円)
  • 何年ローンにしますか?-35(年)
  • 金利方式はどうしますか?-自由に入力 1(%)

また、こちらのサイトでもかんたんなシミュレーションツールをご用意しておりますので、こちらも参考にしてみてください。

では、エクセルのシミュレーションと数字を同じにして試算してみましょう。

▼ 試算結果

毎月の返済額 ボーナス月のご返済額 年間のご返済額 総返済額
お借入期間35年 84,685円 84,685円 1,016,220円 35,567,700円

金融機関によって表示のされ方が異なります。また端数処理の違いによりエクセルの結果とズレが生じることがありますが、毎月の返済額を見ると、ほぼ同じ金額だと分かります。

また金融機関のシミュレーションツールの場合、諸費用の概算も試算してもらえるため、金融機関同士を比較する際に役立ちます。

住宅ローンシミュレーションツールをお探しの際は「返済シミュレーションを有効活用!住宅ローンの必須ツール」を参考にして下さい。

金融機関同士の利息を比較する場合は、エクセルにまとめるのがおすすめ

金融機関同士で住宅ローンを比較する場合は、諸費用も含めた金額を比べた方が良いのでエクセルにまとめていくことで見やすく比較することができます。
エクセルでまとめる場合には、最低限、次のような項目で比べてみましょう。

毎月の返済額 総返済額
(元金+利息)
諸費用 総支払額
(総返済額+諸費用)
金融機関A 〇〇〇〇〇円 〇〇〇〇〇円 〇〇〇〇〇円 〇〇〇〇〇円
金融機関B △△△△△円 △△△△△円 △△△△△円 △△△△△円

シミュレーションは何度も行いますので、利息や総返済額の計算をするだけならエクセルの方が効率的よく計算できるでしょう。
※諸費用は、金融機関や借入金額によって計算方法に違いがあるため、金融機関のサイトで確認が必要です。

また、上記の表にある項目は最低限のものですので、必要な項目を追加してオリジナルの比較表を作成してください。

銀行の住宅ローンを比較する際のポイントについて、もっと詳しく知りたい場合はおすすめ住宅ローンの金利比較!選び方からシミュレーション方法まで徹底解説の記事で解説していますので、参考にしてみてくださいね。

まとめ

いかがだったでしょうか。エクセルを使い慣れていないと難しいかもしれませんが、最初はそのまま関数を利用して、計算結果が正しいことを確認するだけでも十分です。

今回紹介した関数自体は使ったことがない人が多いでしょう。一度入力しておけば、あとは数値を変えるだけでシミュレーションできますので、興味のある人はチャレンジしてみてください。

執筆者情報

F&J FP事務所

F&J FP事務所 ファイナンシャル・プランナー

ファイナンシャルプランナー・住宅ローンアドバイザーとして、住宅ローンや保険の相談・アドバイスをしております。これから住宅ローンを利用しようとしている方々に向けて、公正中立な立場で役に立つ情報を発信できればと考えております。

詳細はコチラ
スポンサーリンク

  • このエントリーをはてなブックマークに追加
住宅ローン シミュレーション
じぶん銀行
おすすめ住宅ローン
住信SBIネット銀行
満足度
4.5
住信SBIネット銀行
最低金利
0.418%

変動金利

2019年7月適用金利

借り換え金利

総合人気ランキング
1位 住信SBIネット銀行
満足度
4.5
住信SBIネット銀行
最低金利
0.418%

変動金利

2019年7月適用金利

借り換え金利

総合人気ランキングを全て見る

人気の記事

新着記事

たった1分 住宅ローン シミュレーション
■当サイトに関する注意事項
  • 当サイトで提供する商品の情報にあたっては、十分な注意を払って提供しておりますが、情報の正確性その他一切の事項についてを保証をするものではありません。
  • お申込みにあたっては、提携事業者のサイトや、利用規約をご確認の上、ご自身でご判断ください。
  • 当社では各商品のサービス内容及びキャンペーン等に関するご質問にはお答えできかねます。提携事業者に直接お問い合わせください。
  • 本ページのいかなる情報により生じた損失に対しても当社は責任を負いません。
  • なお、本注意事項に定めがない事項は当社が定める「利用規約」 が適用されるものとします。

「ナビナビ住宅ローン」は、エイチームフィナジーが運営するサービスです。

株式会社エイチームフィナジーは、株式会社エイチーム(東証一部上場)のグループ企業です。
証券コード:3662