住居：住宅ローンを比較する

こんにちは、はな夫（@logicalifer）です。

今回は、我が家が住宅を購入した際に各銀行の住宅ローンを比較した計算方法をご紹介します。

住宅ローンは金額が大きいので、金利や手数料も高額になります。

また、金利だけで最安値を探しても、実は手数料も考慮すると最安値ではなかったりしますので、我が家はトータルでいくら支払うことになるのか比較して決定しました。

Googleスプレッドシートの計算例を記載していますので、ご自身のスプレッドシートを作成して、色々比較いただければ幸いです。

Googleスプレッドシートでローンのシミュレーションをする

まずは、実際に銀行から住宅ローンを借り入れた場合の金額のシミュレーションできるようにします。

この方法は下記の記事でもご紹介していますが、今回は各社比較できるように少し変更しています。

住宅ローンを借り入れる情報を整理する

まずは、一つの銀行から借り入れると仮定して表を作成します。

借り入れる際に必要な情報は下記になります。

• 借入金額
• 支払い開始日
• 期間
• 返済方法
• 金利
• 保証料
• 事務手数料
• 印紙代

まだ具体的に物件や金額が決まっていない方は、最初の3つは仮で決めればOKです。

後半の4つが、各銀行で内容が異なっています。

銀行のシミュレーションを実行する

銀行によっては、住宅ローンのシミュレータのページがあるので、試してみます。

今回は例として下記の条件でシミュレーションを行います。

借入金額	4,000万円
支払い開始日	2021/1/27
返済期間	35年
返済方法	元利均等

例として、住信SBIネット銀行でシミュレーションしてみます。

例の条件を入力しました。

シミュレーション結果は以下になりました。

この結果をみると、毎月返済額の他に諸費用で100万円以上掛かっているのが分かります。住宅ローンの借り入れ額4,000万円と比べると、2.8%程ですが、100万円というのは大きな金額ですね。。。

これらの値をスプレッドシートにまとめていきます。

• 保証料：住信SBIネット銀行では、0円となっています。
• 事務手数料：シミュレータで諸費用（一般的な場合）に記載された印紙代以外の合計です。
• 印紙代：シミュレータで諸費用（一般的な場合）に記載された印紙代です。

支払いのシミュレーション

次に、この条件で借り入れた場合の毎月の支払いのシミュレーションを行います。

下記のようなものを目指します。

ローンの計算式の入力

A列からH列に計算式を入力していきます。

A列：月数

A列には、月数を入力していきます。

A12セルに0を入力し、A13以降は一つ上のセル+1を入力していき、借入年数×12まで入力していきます。35年であれば、420になります。

B列：年月日

B列には、支払い月がわかる日付を表示していきます。計算式を簡単にするために、実際の支払日ではなく、月末の日付を表示します。

例えば、B12セルには下記を入力します。

=eomonth($B$4,A12-1)

ここで使用するEOMONTH関数の詳細は下記にあります。

この関数を使用することで、支払い開始日の月からA列の分ずらした月の月末を表示することができます。

なお、B12セルは支払い開始前の月、B13セルが支払い初回の月になります。

C列：元本

C列には、毎月のローン支払い額のうち、元本に該当する金額を表示していきます。

例えば、C13セルには下記を入力します。

=-PPMT($E$3/12,$A13,$B$5*12,$B$3,0,0)

ここで使用するPPMT関数の詳細は下記にあります。

この関数を使用するときは、月なのか年なのか難しいですが、今回は毎月の値を算出していきたいため、下記にしていきます。

• rate：銀行の金利は年のため、12で割る必要があります。例ではE5セルの0.440%を12で割って使用します。
• period：今回の支払い回数のため、A列の値を使用します。
• number_of_period：合計の支払い回数のため、年数×12となります。例ではB5セルを12でかけて使用します。
• present_value：ローンの借り入れ額を使用します。例ではB3セルの値を使用します。
• future_value：住宅ローンの返済後は残高0になります。例では0を入力しています。
• end_or_beginning：支払いが期末か期初かという選択肢ですが、月末支払いのため例では0を入力しています。

D列：利率

D列には、毎月のローン支払い額のうち、利息に該当する金額を表示していきます。

例えば、D3セルには下記を入力します。

=-IPMT($E$3/12,$A13,$B$5*12,$B$3,0,0)

ここで使用するIPMT関数の詳細は下記にあります。

この関数はPPMT関数と対になる関数のため、引数はPPMT関数と同じものにしています。

• rate：銀行の金利は年のため、12で割る必要があります。例ではE3セルの0.440%を12で割って使用します。
• period：今回の支払い回数のため、A列の値を使用します。
• number_of_period：合計の支払い回数のため、年数×12となります。例ではB5セルを12でかけて使用します。
• present_value：ローンの借り入れ額を使用します。例ではB3セルの値を使用します。
• future_value：住宅ローンの返済後は残高0になります。例では0を入力しています。
• end_or_beginning：支払いが期末か期初かという選択肢ですが、月末支払いのため例では0を入力しています。

E列：ローン残高

E列には、ローンの残高を表示していきます。

E2セルはローン支払い開始前の金額として、借入額であるK1セルとします。

E3セル以降は先月の残高からC列の元本を引いた金額にします。

例えばE3セルは下記となります。

=E12-C13

F列：支払い金額合計

F列は該当の月までに支払った金額の合計を表していきます。

F2セルはローン支払い開始前の状態のため、0とします。

F3セル以降は先月の支払い額からC列とD列を足した金額にします。

例えばF3セルは下記となります。

=F12+C13+D13

G列：控除

G列には、住宅ローン控除の金額を表していきます。

2021年1月現在では、住宅ローン控除の制度が存在し、毎年12月の住宅ローン残高の1%(最大40万円)が控除されます。年数は入居開始日によって変わりますが、現在は10年もしくは13年のどちらかになります。詳細は、下記の参考リンクから当てはまる条件をみていただければと思います。

今回は、2021年１月に入居し、13年間控除を受けることができる例で計算します。例では借入額が4,000万円のため、控除される金額は毎年1%以下になります。

11年目～13年目は、以下の①②のうちいずれか少ない方の金額が3年間に渡り所得税の額等から控除される。

①住宅ローン残高又は住宅の取得対価（上限4,000万円）のうちいずれか少ない方の金額の1%

②建物の取得価格（上限4,000万円）の2％÷3

http://sumai-kyufu.jp/outline/ju_loan/

G3セル以降に、上記の条件を満たす控除額を算出することにします。

例えばG3セルは下記となります。

=IF(A13<=120,IF(month(B13)=12,FLOOR(E13*0.01,1),0),IF(A13<=156,IF(month(B13)=12,min(min(E13,B$3,40000000)*0.01,min(B$3,40000000)*0.02/3),0),0))

H列：支払い金額合計-控除

H列には、支払金額の合計から住宅ローン控除で控除された金額を引いた、実質の支払い額を表すことにします。

例えばH3セルは下記となります。

=H12+C13+D13-G13

以上で、毎月末の状況のシミュレーションが完成しました。

完済時期に応じた支払い総額を計算する

次に完済時期に応じた支払い総額を計算します。下記の部分です。

それぞれのセルの計算式は下記となります。

=vlookup(G4*12,A$11:H$501,8,false)+vlookup(G4*12,A$11:H$501,5,false)+SUM(E$4:E$6)

最初のvlookupは、完済時期におけるH列の支払い合計から住宅ローン控除を引いた支払額を算出しています。

2つ目のvlookupは、完済時期におけるE列のローン残高を算出しています。

最後のSUMは事務手数料などの合計です。

住宅ローン控除のおかけで、25年以内に繰越返済すると、借入額である4,000万円を下回るということが分かります。

また、繰り上げ年数を細かく変更すれば１年単位で詳細を計算することもできます。

まずはこれで1つの銀行における総支払額を算出することができました。

複数の銀行のシミュレーション結果を入力する

あとは、同じ計算式で住宅ローンを借り入れ候補の銀行の値をインプットしていきます。

次の例として三菱UFJ銀行のネットローンでシミュレーションをしてみました。

住信SBIネット銀行と比べて、金利が少し高いのと、諸費用の内訳が異なっています。

住信SBIネット銀行と同じように、スプレッドシートに値を入力しました。

35年の総額で比較すると、約26万円住信SBIネット銀行の方が安いようです。

まとめ

我が家では、このように住宅ローンを借り入れた場合の支払総額を比べて各銀行を比較しました。

なお、今回の例はネットでシミュレーションができる2社の例を載せましたが、実際に住宅ローンを借り入れる際は、対面で話をきくと、ディベロッパーとの提携、年収、物件によって割引があったり、繰り上げ返済時の事務手数料キャッシュバックがあったりと条件が様々です。

これらの話を総括して本スプレッドシートの例をカスタマイズして、比較してみると面白いです。

ちなみに我が家は諸々の割引まで考慮して地銀と住宅ローンの契約をしました。

また、住宅ローン控除はローン残高を対象に控除がされるため、控除の対象となる13年までは繰り上げ返済をしない方が控除額が増えることになります。

また、本記事は2021年1月に作成しており、変動金利が1%を割っていること、住宅ローン控除が13年という期間であることを前提にしています。将来の状況により、何がお得か変わってきます。

住宅ローンの契約で悩んでいる方の参考になれば幸いです。