住宅ローンの返済予定表をエクセルで作る方法｜関数の意味から繰り上げ返済の反映まで

結論:返済予定表は、5つの列と4つの式で作れます

• 必要な列は5つだけです。回数・毎月の返済額・うち利息・うち元金・返済後の残高。これで銀行から届く償還予定表と同じものになります。
• 毎月の返済額はPMT関数で求めます。=PMT(年利/100/12, 年数*12, -残高) の1行です。3,000万円・年1.0%・30年なら96,492円と出ます。
• 残りの3つは四則演算だけです。利息=前月の残高×年利÷100÷12、元金=返済額−利息、残高=前月の残高−元金。関数は要りません。
• 自分で作る意味は、変動金利に追従できることです。銀行から届く償還予定表は、いまの金利で作られた予定にすぎません。金利が変われば実態と合わなくなりますが、自分の表なら金利のセルを書き換えるだけで全部が計算し直されます。
• 繰り上げ返済も同じ表に組み込めます。途中の残高から繰り上げ額を引き、そこから先を同じ式で伸ばすだけです。

この記事では、式の入れ方だけでなくなぜその式になるのかを説明します。仕組みが分かれば、自分の条件に合わせて表を作り替えられます。

自分の数字で計算した結果を、数式が入ったExcelファイルとして受け取ることもできます。記事末尾の計算ツールで試算し、そのままダウンロードしてください。

なぜ自分で作るのか

住宅ローンを借りると、金融機関から償還予定表(返済予定表)が届きます。すでに手元にあるのに、なぜ自分で作るのか。

理由は3つあります。

1. 変動金利では、届いた表がすぐに実態と合わなくなります。償還予定表は「いまの金利がずっと続いたら」という前提で作られた予定表です。金利が上がれば、そこに書かれた利息も元金の減り方も変わります。自分の表なら、金利のセルを書き換えるだけで全部が計算し直されます。

2. 繰り上げ返済の効果を、実行前に自分で確かめられます。「100万円入れたら何か月縮むか」を、申し込む前に確認できます。

3. 銀行はこの作り方を積極的には教えません。各行が自社サイトにシミュレーターを用意しているためです。それ自体は自然なことですが、結果として「自分の手元で管理する方法」の解説は少なくなります。

準備する列は5つです

新しいシートに、次の5列を作ります。

これとは別に、上部に入力欄を3つ作ります。残高・年利(%)・残りの返済年数です。ここを書き換えると表全体が変わる、という作りにします。仮に次の位置に置いたとして説明します。

• B1:残高(例 30000000)
• B2:年利% (例 1)
• B3:残りの返済年数(例 30)

毎月の返済額をPMT関数で求める

PMT関数は、毎回の返済額を求めるExcelの関数です(Payment の略)。次のように入れます。

=PMT(B2/100/12, B3*12, -B1)

3,000万円・年1.0%・30年なら 96,492円 と表示されます。

なぜこの書き方になるのか

3つの引数の意味を押さえると、間違えなくなります。

第1引数は「1回あたりの利率」です。年利ではありません。毎月返すなら1か月分の利率が必要なので、年利 ÷ 100 ÷ 12 とします。年1.0%なら 0.01÷12 です。

ここが最も多い間違いです。年利のまま =PMT(1%, 360, -30000000) と入れると 308,584円 と出ます。正解の3倍以上なので、この間違いは気づけます。

本当に危ないのは、気づけない間違いのほうです。金利のセルに 1(パーセント表記)ではなく 0.01(小数)を入れておきながら、式では /100/12 としてしまうと、=PMT(0.01/100/12, 360, -30000000) となり 83,459円 と出ます。正解の96,492円と桁が同じで、それらしく見えてしまいます。

金利のセルに何を入れたかで、式の書き方が変わります。

この記事では、入力欄に 1 と入れる前提で /100/12 としています。

第2引数は「返済の回数」です。年数ではありません。毎月返すなら 年数 × 12 です。30年なら360です。

第3引数は「借入額」で、マイナスを付けます。Excelの財務関数は「受け取るお金はプラス、支払うお金はマイナス」で扱います。借入額をそのまま入れると返済額がマイナス表示になるため、頭にマイナスを付けて符号を反転させます。

残りの3列は四則演算だけです

関数は要りません。表の1行目(1回目の返済)から順に見ていきます。

C列(利息) — その月の利息は、返済前の残高に1か月分の利率をかけたものです。

1回目の利息 = 30,000,000 × 1.0 ÷ 100 ÷ 12 = 25,000円

D列(元金) — 返済額のうち、利息でない部分がすべて元金の返済に回ります。

1回目の元金 = 96,492 − 25,000 = 71,492円

E列(残高) — 元金の分だけ残高が減ります。

1回目の返済後の残高 = 30,000,000 − 71,492 = 29,928,508円

2回目以降は、前の行の残高を使って同じ計算を繰り返します。

返済額は毎回同じなのに、利息が少しずつ減り、元金に回る額が増えていきます。残高が減るからです。この形が元利均等返済です。

セルの式にすると、1行目(6行目に置いたとして)は次のようになります。

B6: =$B$4              (毎月の返済額。B4にPMTの結果を置いた場合)
C6: =$B$1*$B$2/100/12  (1回目だけ、返済前の残高はB1)
D6: =B6-C6
E6: =$B$1-D6

2行目(7行目)からは、前の行の残高を参照します。

B7: =$B$4
C7: =E6*$B$2/100/12
D7: =B7-C7
E7: =E6-D7

あとは7行目を下にコピーするだけです。360回分(30年)なら366行目まで伸ばします。

$ を付けているのは、下にコピーしても参照先がずれないようにするためです(絶対参照)。前の行の残高を指す E6 には付けません。こちらはコピーとともにずれてほしいためです。

検算のしかた

作った表が正しいか、3か所で確認できます。

1. 最終行の残高が0になること。丸め処理を入れずに式だけで作った場合、最終行はぴったり0になります(実際に検証しました)。ROUND関数などで円未満を丸めている場合は数円〜数十円ずれますが、それ以上大きく残っていれば式が間違っています。

2. 総返済額。B列の合計が 3,474万円(3,000万円・1.0%・30年の場合)。

3. 総利息。C列の合計が 474万円。総返済額から借入額を引いた値と一致します。

元金均等返済で作る場合

元金均等返済は、毎月返す元金を一定にして、そこに利息を足す返し方です。取り扱う金融機関は限られますが、作り方は元利均等より簡単です。PMT関数を使いません。

• 毎月の元金 = 借入額 ÷ 返済回数 = 30,000,000 ÷ 360 = 83,333円(固定)
• 利息 = 前月の残高 × 年利 ÷ 100 ÷ 12
• 毎月の返済額 = 元金 + 利息

総利息は 451万円 で、元利均等(474万円)より約22万円少なくなります。ただし返済開始当初の負担は月々約1.2万円重くなります。

なお、この記事で扱っている5年ルール・125%ルールは元利均等返済にのみ適用されるものです。詳しくは住宅ローンの5年ルール・125%ルールとはで扱っています。

繰り上げ返済を表に組み込む

繰り上げ返済は、途中の残高から繰り上げ額を引くだけで表現できます。難しい式は要りません。

12回目の返済が終わった時点(残高29,138,155円)で、100万円を繰り上げ返済するとします。残高を28,138,155円に書き換えて、そこから先を同じ式で伸ばします。

ここから先は、選ぶ方法によって伸ばし方が変わります。

期間短縮型(毎月の返済額を変えない)

• B列の返済額は96,492円のまま
• 残高が0を下回った行で終わり
• この例では残り335回で完済します。繰り上げなしなら348回だったので、表の行が13行減ります(最終回は端数の返済になります)

回数だけを先に知りたい場合は、NPER関数で確かめられます。=NPER(年利/100/12, -毎月の返済額, 繰り上げ後の残高) と入れると、この例では 334.2 と返ります。334回では返しきれず、335回目で終わるという意味です。

返済額軽減型(返済期間を変えない)

• 残りの回数(348回)を変えずに、返済額を計算し直します
• =PMT(年利/100/12, 348, -28138155) → 93,180円
• 毎月の返済が 3,312円 軽くなります

同じ100万円でも、受け取るものが違います。期間短縮型は将来の利息、返済額軽減型は毎月の余力です。どちらがいくら得かは住宅ローンの繰り上げ返済は得かで数字にしています。

変動金利の人が1列足すなら:未払利息のライン

ここは、市販のテンプレートにはまず入っていない項目です。変動金利で借りている人には、作っておく価値があります。

金利が上がったとき、毎月の返済額で利息をまかないきれなくなると、不足分が未払利息として残高に上乗せされます。返済しているのに残高が増える状態です。

これが発生し始める金利は、次の式で求められます。

未払利息が発生する金利 = 毎月の返済額 × 12 ÷ 残高 × 100

3,000万円・1.0%・30年の場合:

96,492 × 12 ÷ 30,000,000 × 100 = 3.86%

セルの式にすると =B6*12/E5*100 のような形です(前の行の残高を参照)。この列を作っておくと、返済が進むにつれてラインが上がっていくのが見えます。残高が減るほど、同じ返済額でカバーできる利息の割合が増えるためです。

つまり危険なのは、借りたばかりで残高が大きい人です。この構造は住宅ローンの5年ルール・125%ルールとはで詳しく扱っています。

つまずきやすい3点

金利の入れ方と式が噛み合っていない — 前述の表のとおりです。0.01 と入れて /100/12 にすると 83,459円 という、それらしく見えるのに間違った数字が出ます。桁が同じなので気づけません。金利のセルに何を入れたかを必ず確認してください。

返済回数を年数のまま入れてしまう — B3 に30と入れて =PMT(..., B3, ...) としてしまうケースです。この場合 1,012,969円 と出ます。B3*12 が必要です。

銀行の償還予定表と1円まで合わせようとする — 実際の金融機関は円未満を丸めており、丸め方も一律ではありません。自分の表と数十円〜数百円ずれるのは正常です。判断には影響しないので、合わせようとしないでください。

自分の数字で計算する

ここまでの表は代表的なケースです。実際の判断は、あなたの残高・残り年数・現在の金利で計算する必要があります。

入力するのは、残高・残り年数・現在の金利・金利タイプの4つだけです。年収・資産・生活費はお聞きしません。

計算した結果は、数式が入ったExcelファイルとしてダウンロードできます。値だけを書き出したものではないので、ファイルを開いてから残高や金利を書き換えれば、返済予定表がその場で計算し直されます。手元で管理を続けたい方はこちらをお使いください。

なお、ツールを「繰り上げ返済」に切り替えて計算すると、繰り上げ額を入れる欄が増え、繰り上げ返済の効果を計算するシートもファイルに追加されます。この記事の「繰り上げ返済を表に組み込む」で説明した内容を、自分の数字で確かめられます。