住宅ローンを返済していると、
「毎月の返済額のうち、元金はいくら？」
「利息はいくら払っている？」
「ローン残高はどのように減っていく？」
と気になることがありますよね。
今回は、Googleスプレッドシートを使って住宅ローンの返済表を作る方法をご紹介します。
返済回数ごとに、
- 返済日
- 毎月返済額
- 元金
- 利息
- 返済後の残高
が分かる返済表を作っていきます。
毎月返済額が同じでも、その内訳はずっと同じではありません。
返済が進んで住宅ローン残高が減ると、支払う利息は少なくなり、その分、元金に充てられる金額が増えていきます。
返済表を作っておくと、こうした元金・利息・残高の変化を確認できるようになります。
※この記事では、返済期間中に金利が変わらない場合の基本的な返済表を作成します。
今回作成する住宅ローン返済表
今回作成するのはこちらのような返済表です。
項目は次の7つです。
| 項目 | 内容 |
|---|---|
| 返済回数 | 何回目の返済か |
| 返済日 | 各回の返済日 |
| 年利 | 住宅ローンの年利 |
| 毎月返済額 | 毎月支払う金額 |
| 元金 | 返済額のうち借入残高の返済に充てられる金額 |
| 利息 | 返済額のうち利息として支払う金額 |
| 残高 | 返済後の住宅ローン残高 |
特にポイントになるのが、「元金」と「利息」を分けて表示することです。
完成したスプレッドシートを無料でダウンロードできます
今回作成する住宅ローン返済表は、こちらから無料でダウンロードできます。
自分で最初から作ってみたい方は、ここから一緒に作成していきましょう。
1．住宅ローンの借入情報を入力する
まずは、計算に使用する住宅ローンの情報を用意します。
今回は、
- 借入額
- 年利
- 返済回数
- 借入日
を入力しています。
例として、
借入額：3,500万円
年利：0.60％
返済回数：420回（35年）
借入日：2026年7月20日
として計算します。
2．返済回数を表示する
A列には返済回数を表示します。
A2セルに、
1と入力します。
A3セルには、1つ上のセルに1を足す式を入力します。
=A2+1すると「2」と表示されます。
この式を下へコピーすると、
1、2、3、4、5……
と返済回数を連続して表示できます。
35年返済なら、
35年 × 12か月＝420回
なので、420回目までコピーします。
3．返済日を自動表示する
次にB列へ返済日を表示します。
今回は、借入日の1か月後を最初の返済日として計算します。
VLOOKUP関数で入力シートから借入日を取得し、EDATE関数で返済回数分の月数を加えます。
=EDATE(VLOOKUP(“借入日”,’入力：借入情報’!$A$1:$B$4,2,0),A2)
A2セルが「1」なら借入日の1か月後、A3セルが「2」なら2か月後……というように返済日が表示されます。
式を必要な返済回数までコピーします。
4．年利を表示する
C列には、入力シートに設定した年利を表示します。
=VLOOKUP(“年利”,’入力：借入情報’!$A$1:$B$4,2,0)
今回は、返済期間中は同じ金利が続くものとして計算します。
そのため、C2セルの式を必要な行までコピーします。
変動金利の場合は？
実際の変動金利型住宅ローンでは、返済途中で適用金利が変わることがあります。
今回作成する返済表では、途中の金利変更は反映しません。
変動金利の変更を反映させる場合は別の仕組みが必要になるため、今回は「金利が変わらない場合の基本的な返済表」として作成します。
5．毎月返済額をPMT関数で計算する
D列では、PMT関数を使って毎月返済額を求めます。
PMT関数には、
月利・返済回数・借入額
を指定します。
年利をそのまま使うのではなく、
年利 ÷ 12
として1か月分の金利にするのがポイントです。
=-PMT(C2/12,VLOOKUP(“返済回数”,’入力：借入情報’!$A$1:$B$4,2,0),VLOOKUP(“借入額”,’入力：借入情報’!$A$1:$B$4,2,0)
PMT関数で求めた返済額はマイナスになるため、式の先頭に「-」を付けて、返済額をプラスで表示しています。
今回の条件では、
毎月返済額：92,410円
となりました。
毎月返済額は基本的に同じなので、式を下へコピーします。
6．1回目の利息を計算する
ここから、毎月返済額を「元金」と「利息」に分けていきます。
先に利息を計算します。
1回目の利息は、
借入額 × 年利 ÷ 12
で求められます。
今回の場合は、
3,500万円 × 0.60％ ÷ 12
なので、
17,500円
です。
=IF(B2=””,”-“,VLOOKUP(“借入額”,’入力：借入情報’!$A$1:$B$4,2,0)*C2/12)
7．1回目の元金を計算する
元金は、
毎月返済額 − 利息
で求めます。
今回の場合、
92,410円 − 17,500円
なので、
74,910円
が元金です。
=IF(B2=””,”-“,MAX(0,D2-F2))
つまり92,410円を返済していても、92,410円すべてが住宅ローン残高を減らしているわけではありません。
74,910円が元金の返済、17,500円が利息の支払いという内訳になっています。
8．1回目の返済後残高を計算する
続いてG列で、返済後の住宅ローン残高を計算します。
計算方法は、
借入額 − 元金
です。
今回なら、
3,500万円 − 74,910円
なので、
34,925,090円
となります。
=IF(B2=””,”-“,MAX(0,VLOOKUP(“借入額”,’入力：借入情報’!$A$1:$B$4,2,0)-E2))
ここでも重要なのは、毎月返済額の全額を残高から引かないことです。
利息は金融機関に支払う費用なので、住宅ローンの残高を減らすのは「元金」だけです。
9．2回目以降の利息を計算する
2回目以降は少し計算方法が変わります。
1回目の利息は「最初の借入額」から計算しましたが、2回目の時点ではすでに元金を一部返済しています。
そのため、
前回返済後の残高 × 年利 ÷ 12
で利息を求めます。
=IF(B3=””,”-“,G2*C3/12)
1回目の利息は17,500円でしたが、2回目は17,463円になります。
住宅ローン残高が減ったため、利息も少し減りました。
10．2回目以降の元金と残高を計算する
元金の計算方法は1回目と同じです。
毎月返済額 − 利息
で計算します。
=IF(B3=””,”-“,MAX(0,D3-F3))
毎月返済額が同じで利息が減るため、返済が進むほど元金に充てられる金額は増えていきます。
残高は、
前回の残高 − 今回の元金
で計算します。
=IF(B3=””,”-“,MAX(0,G2-E3))
2回目の数式ができたら、E3～G3セルを選択して、必要な返済回数までコピーすれば完成です。
住宅ローン返済表の計算方法をまとめると
今回の計算で特に重要なのは、この3つです。
利息
返済前の残高 × 年利 ÷ 12
元金
毎月返済額 − 利息
返済後残高
前回の残高 − 元金
ただし、最初の返済だけは「前回の残高」がないため、最初の借入額を使って計算します。
この仕組みが分かると、
残高が減る
→ 利息が減る
→ 元金に充てられる金額が増える
→ さらに残高が減る
という住宅ローン返済の流れも分かりやすくなります。
実際の住宅ローン返済額とは差が出る場合があります
今回作成した返済表は、住宅ローンの返済計画を確認するためのシミュレーションです。
実際の住宅ローンでは、金融機関ごとの端数処理や返済条件などによって計算結果が異なったり、最後の返済額が調整されたりすることがあります。
実際の返済予定については、金融機関が発行する返済予定表などもあわせてご確認ください。
動画でも作り方を解説しています
今回の住宅ローン返済表の作り方は、YouTubeでも操作画面を見ながら解説しています。
「数式をどのセルに入力するのか分からない」という方は、動画を見ながら一緒に作成してみてください。
以上、お役に立てれば幸いです。
