住宅ローンを借りようと思ったときに、なんといっても気になるのは金利ですよね。
金額も大きいし、借入期間も長いので、0.1％の違いでも総支払金額が大きく変わってきます。
エクセルの関数を使って金利と借入残高の推移を確認できます
銀行のサイトなどで金利シミュレーションをよくやっていますが、PCに入っているエクセルでも簡単に計算することができます。
エクセルなら総支払額だけでなく、金利や元金の推移も一覧で見られるので、返済期間中の状態が確認しやすくなります。
残高推移を見てみると、どのあたりで一括で返してしまおうか？
繰り上げ返済をここでしようか？といった検討もできますので、これから借りる人も、現在返済中の方も一度シミュレーションしてみてください。
ローン返済用の関数を使って返済額を計算
エクセルにはローン返済用の関数(PMT関数）が用意されていますので、複雑な計算式は必要ありません。
以下のような表を作成します。
まずはセルA5の月返済額（元利均等返済の1回分の額）をPMT関数を使って計算します。
B1 元金＝借り入れる金額
B2 利率＝借入金利
B3 借入期間＝借り入れる年数
B4 返済回数＝借り入れる年数×１２か月
B5 月返済額＝元利均等返済での毎月の返済額→これを計算式で求めます。
B1～B4は条件を入力します。
B5の月返済額を計算式で求めます。
=–PMT(B2/12,B4,B1)
PMT関数は元利均等返済の返済額を計算する計算式です。そのままだと－（マイナス）表示になりますので、わかりやすいように頭に－を付けます。
PMT(利率,期間,現在価値）となりますので、上の例で説明すると
利率＝0.7％÷12か月 利率は年利なので1か月分に修正しますので B2/12
期間＝240 B4
現在価値＝借り入れ金額 この場合はB1となります
これで1000万円を借入期間20年、金利0.7％で借りた場合の毎月の返済額44,663円が計算されます。
毎月の返済推移をシミュレーション
まず全240回の返済ですので、返済回数をA列に1～240まで作成します。
B8の支払利子の欄に以下のように入力します。
=ROUND(B1*B2/12,0)
元金*利子÷12で1か月分の利子を計算します。
C8は月返済額から1回目の利子を引いた額＝1回目の返済元金を計算します。
=$B$5-B8
D8の借入残高は元金から1回目の返済元金額を引いた額を計算します。
=B1-C8
2回目の支払利息は1回目の元金が減っているので元金から返済額を引いた額(借入残高）で計算します。
=ROUND(D8*$B$2/12,0)
返済額は1回目と同じなのでそのままコピーします。
=$B$5-B9
借入残高も、1回目の借入残高から2回目の返済額を引いた額を計算します。
=D8-C9
このまま最後の240回までコピーすると、240回目には返済が終了する表ができます。
端数があるので、ぴったり0円にはなりませんが、これでだいたいの推移がシミュレートできます。
240回目はこんな感じになります。
最初は高かった利子も最後のほうはずいぶんと安くなります。
残高も少なくなるので、一括返済をしてしまう人が多いでしょうね。
まとめ
住宅ローンに限らず元利均等返済の返済額をエクセル計算式で簡単に計算できます。
元金均等の返済は単純にすぐ理解できますが、元利均等返済ってどうやって計算したらいいんだろうと思いますが、エクセルの関数なら最初からちゃんと用意されているんですね。
長い期間の生活にかかわることですので、借入何年目にどんな出費があるか、どれくらい貯金できるかなどライフイベントの予測と返済額のシミュレーションをすり合わせて、どのようになるか十分に確認してから借入をしたいですね。
