こんにちは、はな夫（@logicalifer）です。
今回は家計簿に必須ではない内容ですが、住宅ローンの把握に便利な機能をGoogleスプレッドシートで作成した家計簿に取り込もうと思います。
住宅ローンは毎月定額を支払っていくことが多いと思いますが、今回ご紹介する機能を使えば、あといくらの元本が残っているのか、利息との割合、住宅ローン控除の控除額の見込み等が簡単に分かります。
また、住宅ローンの例でご紹介しますが、その他のローンにも応用できると思います。
住宅ローンの情報の整理
今回は既に住宅ローンを払っている前提で記載しています。
整理するべきデータは下記になります。
- 借入金額
- 期間
- 支払い開始日
- 金利
今回は例として、下記の条件で作成しようと思います。なお、金利は2021年1月時点の三菱UFJ銀行の変動金利の値を採用しています。
| 借入金額 | 4,000万円 |
| 期間 | 35年 |
| 支払い開始日 | 2021/1/28 |
| 金利 | 0.475% |
この値をスプレッドシートに入力していきます。A列からH列は後で使用するため、基本情報としてJ列、K列に入力しておきます。
ローンの計算式の入力
A列からH列に計算式を入力していきます。
A列：月数
A列には、月数を入力していきます。
A2セルに0を入力し、A3以降は一つ上のセル+1を入力していき、借入年数×12まで入力していきます。35年であれば、420になります。
B列：年月日
B列には、支払い月がわかる日付を表示していきます。計算式を簡単にするために、実際の支払日ではなく、月末の日付を表示します。
例えば、B2セルには下記を入力します。
=EOMONTH($K$3,A2-1)ここで使用するEOMONTH関数の詳細は下記にあります。
この関数を使用することで、支払い開始日の月からA列の分ずらした月の月末を表示することができます。
なお、2行目は支払い開始前の月、3行目が支払い初回の月になります。
C列：元本
C列には、毎月のローン支払い額のうち、元本に該当する金額を表示していきます。
例えば、C3セルには下記を入力します。
=-PPMT($K$5/12,$A3,$K$2*12,$K$1,0,0)ここで使用するPPMT関数の詳細は下記にあります。
この関数を使用するときは、月なのか年なのか難しいですが、今回は毎月の値を算出していきたいため、下記にしていきます。
- rate：銀行の金利は年のため、12で割る必要があります。例ではK5セルの0.475%を12で割って使用します。
- period：今回の支払い回数のため、A列の値を使用します。
- number_of_period：合計の支払い回数のため、年数×12となります。例ではK2セルを12でかけて使用します。
- present_value：ローンの借り入れ額を使用します。例ではK1セルの値を使用します。
- future_value：住宅ローンの返済後は残高0になります。例では0を入力しています。
- end_or_beginning：支払いが期末か期初かという選択肢ですが、月末支払いのため例では0を入力しています。
D列：利率
D列には、毎月のローン支払い額のうち、利息に該当する金額を表示していきます。
例えば、D3セルには下記を入力します。
=-IPMT($K$5/12,$A3,$K$2*12,$K$1,0,0)ここで使用するIPMT関数の詳細は下記にあります。
この関数はPPMT関数と対になる関数のため、引数はPPMT関数と同じものにしています。
- rate：銀行の金利は年のため、12で割る必要があります。例ではK5セルの0.475%を12で割って使用します。
- period：今回の支払い回数のため、A列の値を使用します。
- number_of_period：合計の支払い回数のため、年数×12となります。例ではK2セルを12でかけて使用します。
- present_value：ローンの借り入れ額を使用します。例ではK1セルの値を使用します。
- future_value：住宅ローンの返済後は残高0になります。例では0を入力しています。
- end_or_beginning：支払いが期末か期初かという選択肢ですが、月末支払いのため例では0を入力しています。
E列：ローン残高
E列には、ローンの残高を表示していきます。
E2セルはローン支払い開始前の金額として、借入額であるK1セルとします。
E3セル以降は先月の残高からC列の元本を引いた金額にします。
例えばE3セルは下記となります。
=E2-C3F列：支払い金額合計
F列は該当の月までに支払った金額の合計を表していきます。
F2セルはローン支払い開始前の状態のため、0とします。
F3セル以降は先月の支払い額からC列とD列を足した金額にします。
例えばF3セルは下記となります。
=F2+C3+D3G列：控除
G列には、住宅ローン控除の金額を表していきます。
2021年1月現在では、住宅ローン控除の制度が存在し、毎年12月の住宅ローン残高の1%(最大40万円)が控除されます。年数は入居開始日によって変わりますが、現在は10年もしくは13年のどちらかになります。詳細は、下記の参考リンクから当てはまる条件をみていただければと思います。
今回は、2021年１月に入居し、13年間控除を受けることができる例で計算します。借入額が4,000万円のため、控除される金額は毎年1%以下になります。
G3セル以降に、12月のローン残高の1%を算出することにします。
例えばG3セルは下記となります。
=IF(A3<=13*12,IF(month(B3)=12,FLOOR(E3*0.01,1),0),0)H列：支払い金額合計-控除
H列には、支払金額の合計から住宅ローン控除で控除された金額を引いた、実質の支払い額を表すことにします。
例えばH3セルは下記となります。
=H2+C3+D3-G3まとめ
以上で終わりです。シートは以下のようになりました。
これで、銀行から送られてくる資料を待たなくても、ローン残高がわかったり、住宅ローン控除がいくらになるのか事前に分かりますので、家計簿で状況の把握が簡単になります。
なお、念の為C列とD列の合計が月々の返済額と一致しているかを確認してくださいね。
また、PPMT関数とIPMT関数の計算結果は、銀行の算出結果と端数が異なる可能性もありますので、あくまで目安として見ていただければ幸いです。
次の記事では、家計簿に必須ではない内容ですが、確定申告の医療費控除を申請するための計算シートを追加する方法についてご紹介します。
コメント