Excelで住宅ローンの損益分岐点分析をマスター|借り換え判断に役立つ手順をわかりやすく解説

損益分岐点分析(ブレイクイーブン分析)は、個人や組織がすべてのコストやローンをいつ回収できるかを把握するための最も効果的なツールの一つです。住宅ローンの場合、損益分岐点分析では、借り換えコストを利息の節約額で回収できるまでの月数を計算します。この記事では、Excelで住宅ローンの損益分岐点分析を行うための手順を、わかりやすく順を追ってご紹介します。

住宅ローンの損益分岐点分析とは?

住宅ローンの損益分岐点分析とは、主に元の返済条件(アモーチゼーション条件)と借り換え後の返済条件を比較・分析する手法です。両方の条件を詳細に検討し、損益分岐期間を算出することで、借り手にとって借り換えが得かどうかを判断します。この分析を理解するには、まず以下の用語を正しく把握しておきましょう。

住宅ローン(モーゲージ): 住宅ローンとは、家や土地などの不動産を購入するために利用されるローンの一種です。この取引は主に貸し手と借り手の間で行われ、貸し手は借り手に多額の資金を貸し付け、返済期間中の利息という形で収益を得ます。また、借り手が期日通りに返済できない場合、貸し手は当該不動産を差し押さえる権利を持ちます。

住宅ローンの借り換え(リファイナンス): 借り換えとは、実行中の住宅ローン契約に対して新たなローン契約を結び直すことです。借り手は利息負担を軽減するために借り換えを行うことが多く、この取引では現在の元本残高や金利が変更されます。ただし、借り換えには追加の手数料が必要となる点に注意しましょう。

損益分岐点: 損益分岐点とは、借り換えにかかったコストが、利息削減による節約額によってちょうど埋まる時点のことです。

住宅ローン損益分岐点分析の実例

内容をより深く理解するために、具体的な例を見てみましょう。ある人が年利5%で40万ドルを借入れ、不動産を購入したとします。返済期間は30年、月々の返済方式であり、この場合の毎月の返済額は約2,147ドルになります。

次に、この人が年利3.5%の新しい条件へ借り換えたいと考えたとします。ただし、借り換えには4,000ドルの追加費用が必要です。新しい金利での毎月の返済額は約1,796ドルとなり、これが借り換え後の住宅ローンです。

新しい条件に切り替えれば、毎月351ドルの節約になります。そして、4,000ドルの借り換え費用をこの節約額で回収するには、約12か月かかる計算です。つまり、この場合の借り手にとっての損益分岐期間は12か月ということになります。

Excelで住宅ローンの損益分岐点分析を行う方法:簡単ステップ

ここでは、「Dataset」ワークシートのセルC4:C7に元の返済条件、セルF4:F8に借り換え後の返済条件のデータが入力されているものとします。このシナリオについて損益分岐点分析を行いましょう。以下の手順に従って進めてください。

📌 ステップ1:PMT関数で1回あたりの返済額を計算する

まず最初に、住宅ローンの損益分岐点分析を行うために、1回あたりの返済額を計算する必要があります。これにはPMT関数を使用します。

• まず、「Payment per Installment」シートのセルC9をクリックし、元の返済条件における1回あたりの返済額を計算する数式を入力します。
• 続いて、Enterキーを押します。

• 次に、同じシートのセルF9をクリックし、借り換え後の住宅ローンの1回あたりの返済額を計算する数式を入力します。
• その後、Enterキーを押します。

これで、両方の条件における1回あたりの返済額の計算が完了しました。

ポイント:

数式にはマイナス符号を使用しています。これはローン返済が現金の流出とみなされ、デフォルトでは結果がマイナスになるためです。マイナス表示を避けるために、数式の前にマイナス符号を付けています。

📌 ステップ2:損益分岐期間を計算する

返済額が求まったら、次は損益分岐期間を計算します。

• まず、「Break-Even Period」シートのセルC4をクリックし、数式バーに次の数式を入力します。

='Payment per Installment'!C9

• 続いて、Enterキーを押します。

• 同様に、セルC5をクリックして以下の数式を適用します。

='Payment per Installment'!F9

• 次に、セルC6をクリックし、2つの返済額の差額(毎月の節約額)を計算する数式を入力します。
• 続いて、Enterキーを押します。

• 次に、セルC7をクリックし、借り換え費用の値を抽出する数式を適用します。

• 最後に、セルC8をクリックし、このシナリオの損益分岐期間を計算する数式を入力します。
• 続いて、Enterキーを押します。

これで、住宅ローンの損益分岐点分析を正常に完了でき、目的の出力結果が得られます。

ポイント:

このシナリオでは返済が月単位で設定されているため、損益分岐期間も「か月」単位で表示されます。

練習用ワークブックのダウンロード

練習用ワークブックはこちらから無料でダウンロードできます!

まとめ

この記事では、Excelで住宅ローンの損益分岐点分析を行うためのすべての手順をご紹介しました。PMT関数で返済額を求め、差額と借り換え費用から損益分岐期間を算出する流れは、実際の借り換え判断にもすぐに活用できます。無料のワークブックをダウンロードして、ぜひ練習してみてください。この記事が皆さんのお役に立てば幸いです。さらに質問やご提案がある場合は、お気軽にコメント欄でお知らせください。

関連記事

• Excelで複数製品の損益分岐点分析を行う方法
• Excelのゴールシークを使った損益分岐点分析のやり方
• ExcelでNPV損益分岐点分析を行う方法
• Excelで損益分岐チャートを作成する方法

<< 損益分岐点分析Excelに戻る | Excel For Finance | Excelを学ぶ

解答付きの高度なExcel演習問題を無料で受け取る!