金融機関各社の金利の変更ニュースもあり、
長らく続いた低金利が今後上昇した場合、返済額はどのように増加してしまうのか、懸念を持っている人も多いと思います。
また、固定金利との比較、借入・借換の検討なども、
住宅ローンの返済シミュレーションをエクセルで自作してみることで比較・検討が可能です。
本記事でわかる内容
＼既に住宅ローンを借りている人／
- 変動金利の金利変動による支払額への影響をシミュレーションできる
- 借換による支払額の影響をシミュレーションできる
＼これから住宅ローンを借り入れる人／
- 固定金利と変動金利による返済額の違いを比較できる
- 各社のローンを正しく比較、評価できる
- 変動金利の金利変動による支払額への影響をシミュレーションできる
Webで用意されているテンプレートでは満足な比較はできない
わざわざ自分で作成するよりはすでに用意されているものを使用したいと思い、
返済をシミュレーションするエクセルのテンプレートファイルについていろいろ調べてみましたが、
Webで用意されているテンプレートでは満足な比較は難しいという結論になりました。
- Microsoft officeのテンプレート：正直使いづらい
- SUUMO提供のテンプレート（Microsoft officeのテンプレートに用意）：マクロで計算するため、マクロを知らない人には扱いづらい
- 変動金利の場合の計算方法等、PMT関数も含め包括的に使い方を書いてあるサイトは見つけられなかった
- 銀行等のサイトではサイトにより条件が変わってしまったり、選択できる項目に差があったりと、完全な比較が難しい
ちなみに、SUUMOのテンプレートは、ボーナス返済額を0にした場合においても期間短縮と返済額軽減を変更した時に金額が変わる仕様になっているため、
おそらくマクロが間違っているのではと感じました。
以上より、自分でエクセルで作ってみるのが最適ではないかと思います。
住宅ローンの返済シミュレーションはPMT関数を使用して計算
さて、いよいよここからエクセルで計算する方法について説明します。
エクセルにはPMT関数というローン返済をシミュレーションする際に便利な関数が用意されています。
概要は以下で、詳細は後で解説します。
大きなポイントとして、返済方法で使用する関数が分かれます。
| 関数名 | 計算内容 |
| PMT関数 | 元利均等返済の1回の返済額（元金＋利息）を計算 |
| PPMT関数 | 元利均等返済の任意の回の返済額の元金分を計算 |
| IPMT関数 | 元利均等返済の任意の回の返済額の利息分を計算 |
| ISPMT関数 | 元金均等返済の任意の回の返済額の利息分を計算 |
元利均等返済と元金均等返済
- 元利均等返済 ： 返済額（元金＋利息）が一定の返済方法
⇒ PMT関数、PPMT関数、IPMT関数を使用 - 元金均等返済 ： 返済額のうち元金が一定の返済方法
⇒ ISPMT関数を使用
支払総額は、元利均等返済 ＞ 元金均等返済
元利均等返済は、返済当初は利息分の支払いが多く、元金がなかなか減らないためです。
元利均等返済のシミュレーション方法
まずは、元利均等返済のシミュレーションについて説明します。
シミュレーションの計算条件
計算条件
- 借入金：5,000万円
- 返済期間：35年
- 金利：0.35%で一定
下記のような返済シミュレーションの表を作成します。
シミュレーションに使用するPMT関数
使用する関数は以下です。
| 関数名 | 計算内容 | 引数 |
| PMT関数 | 元利均等返済の1回の返済額（元金＋利息）を計算 | (①利率, ③期間, ④現在価値, ⑤[将来価値], ⑥[支払期日]) |
| PPMT関数 | 元利均等返済の任意の回の返済額の元金分を計算 | (①利率, ②期, ③期間, ④現在価値, ⑤[将来価値], ⑥[支払期日]) |
| IPMT関数 | 元利均等返済の任意の回の返済額の利息分を計算 | (①利率, ②期, ③期間, ④現在価値, ⑤[将来価値], ⑥[支払期日]) |
PMT関数 = PPMT関数 + IPMT関数 の関係が成立しています。
次に引数について説明します。
なお、PMT関数類は、そのまま計算すると「-」となってしまうため、先に「-」をつけるとよいと思います。
①利率
金利を入力します。
③の期間と月や年などの単位を揃える必要があります。
(例)年利0.35%の場合で月々の返済額を計算する場合は、「0.0035/12」 とする
②期
PPMT関数およびIPMT関数の場合は入力が必要です。
通常は求めたい返済回の数値を入力します。
今回は金利が変動する計算に対応させるため、常に「1」を入力します。
③期間
返済回数を入力します。
①の利率と単位を揃える必要があります。
(例)ローン期間が35年の場合、35 x 12 = 420月 なため、「420」と入力
また、返済回数は残回数を入力（②期の1と対応）
残りの期間の残債に対して、新たな期間と金利で計算するイメージです。
④現在価値
借入金の残高を入力します。
今回の条件では最初は「50,000,000」と入力します。
2回目以降は、返済で減った残りの借入額を入力していきます。
⑤将来価値
省略可能であり、今回は入力不要。
⑥支払期日
省略可能であり、今回は入力不要。
シミュレーションの注意点
- ②期(返済期間)の分だけ行を作成します。
こうすることで、例えば5年後に金利が上昇したケースなどを計算できます。 - 今回は月々で計算していますが、年単位とすることも可能
- 変動金利でシートを作成しておけば、全ての行で金利を一定にすれば固定金利になります。（今回の前提条件と同じ）
- 今回は複雑な金利変動も可能なシートを作成していましたが、
固定金利の場合は、単純にPMT関数のみを使用しても問題ありません
元金均等返済のシミュレーション方法
元金均等返済は元利均等返済より簡単に計算できます。
元金均等返済の計算方法
- 元金：元金分は均等なため、ローン借入額を返済回数で割る
- 利息：借入残高に利率をかける or ISPMT関数を使用する
- 支払総額：毎回の返済額(元金＋利息)の総和
固定金利か変動金利かに関わらず、元金部分は一定です。
変動金利の場合は、利息分を住宅ローンの適用金利に応じて変更すれば計算できます。
ISPMT関数の使い方は他のPMT関数の使い方と同様なため、
元利均等返済のシミュレーションを参考にしていただければと思います。
固定金利と変動金利の違い
固定金利や変動金利がイマイチよくわからない、どちらを選べばよいか悩むという方は、
別記事にまとめましたのでご参照ください。
こちらもCHECK
-
-
【住宅ローン】固定金利or変動金利 の選び方
住宅を購入するにあたり借りる住宅ローンについて、 固定金利か変動金利のどちらがよいかを解説します。 金利とは？ 銀行などからお金を借りる代わりに払う利子のことです。 例えば1.5%などを見かけると思い ...
続きを見る
実質金利で各社ローンを総合評価
各社のローンの違いを、金利のみならず保証料などを含めて比較する場合、
IRR関数を使用して実質金利を評価することで、保証料などを含めた各社の比較が可能です。
IRRは、事前知識が必要な少し難しい話になってくるため、興味がある人は調べていただければと思います。
ひらたく言うと、IRRの数値が、
・投資としては大きいほうが有利
・ローンとしては低いほうが有利（実質金利が低いということ）となります。
住宅ローン一括比較サービスはモゲチェックがタイパ良し
シミュレーションはめんどくさい！そんな時間がない！よくわからない！という人は、
無料で簡単に比較できるモゲチェックを利用してみるのもおすすめです。
新規の借入およびローンの借換のどちらも対応しています。
モゲチェックとは？
モゲチェックとは
- 住宅ローンを一括比較し、「金利順」「保険充実順」など重視するポイントごとにおすすめを診断してくれるサービス
- プロのアドバイスまで無料で受けられる
モゲチェックを運営する株式会社MFSは2024年6月に東証に新規上場しており、最近勢いのある会社です。
上場していることからもこのサービスが好調なことがわかります
すでに20万人以上が利用しているそうです
モゲチェックのメリット
メリット
- 1回の入力で住宅ローンを一括比較し、横並びで検討できるため、時短になる
- 借入金額や金利差によっては例えば100万円単位でのコストダウンになる
- PayPay銀行など、公開されている金利をさらに優遇したモゲチェック限定の優遇金利を受けられる
住宅ローンの申し込みをしたことがある人なら大変さがわかると思いますが、
書類を用意するだけでも大変ですし、各社金利以外にも保障内容や手数料も異なるため、
自分で横並びで比較するのは時間も労力も多く必要です。
そんな住宅ローンの比較を無料で一括でできるのはうれしいサービスです。
また、モゲチェック限定優遇金利も見逃せないポイントです。
モゲチェックのデメリット
デメリットをあえて挙げるとすると、新規の借入は借入金額の1%、借換は減額できた金額の10%が手数料としてかかることかと思います。
（自分で比較できればかからないコスト）
ただし、住宅ローンはたくさんの会社が提供しており、いろいろなサービスとの組み合わせで優位性を出そうとしています。
そのため、メリットでも触れさせていただいたように、知識がないと自分で比較して納得するのにどうしても時間がかかります。
①時間と②住宅ローン専門家の知識を買う必要コストとして捉えるのがよいかと考えます。
それに、減額幅のほうが大きければ結果コストダウンにもなると思います。
また、成約までは無料なのでまずは使ってみて判断もよいのではないかと思います。
＼これから住宅ローンを借り入れる方／
＼住宅ローンの借換を検討している方／
まとめ
住宅ローンの返済シミュレーションをエクセルで自作しました。
皆様のご参考になれば幸いです。