ローン返済表をExcelで作る方法｜毎月の返済額・利息・残高がPMT関数で自動で出る

- 毎月の返済額だけ知っていても、利息にいくら払っているかは見えません。

- 借入額・年利・返済回数の3つを入れると、全回の返済表が出る形にします。

- 自動車・教育・事業などの借入で、利息と元金の内訳を自分で見たい人向けです。

■1. 入力する3つ

先頭のセルに次を入れます。

- B1 借入額（例：1,000,000）

- B2 年利（例：3％。0.03と入れて、表示形式をパーセンテージにします）

- B3 返済回数（月数。5年なら60）

■2. 毎月の返済額を出します（元利均等）

B4に次の式を入れます。

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

PMTはマイナスで返るので、頭にマイナスを付けて正の数にします。年利を12で割るのは、月ごとの利率にするためです。

年利が0％のときはPMTが使えないことがあります。その場合は次の式に切り替えます。

=B1/B3

■3. 返済表を作ります

6行目を見出しにして、列を次のようにします。

- A 回／B 返済額／C 利息／D 元金／E 残高

A7に1、A8に2…と回数を入れます。B7に返済額、C7とD7に次の式を入れ、下へコピーします。

=-IPMT($B$2/12,A7,$B$3,$B$1)

=-PPMT($B$2/12,A7,$B$3,$B$1)

B7は =C7+D7 とし、E7は次の式にします。

=$B$1-SUM($D$7:D7)

下へコピーすると、回ごとの残高が出ます。

■4. 総支払額と利息の合計を出します

- 総支払額は、月の返済額に回数を掛けます。

=B4*B3

- 利息の合計は、総支払額から借入額を引きます。

=B4*B3-B1

■5. 入力例で確かめます

借入100万円・年利3％の場合です。

- 36回：月の返済額 29,081円／利息の合計 46,924円／総支払額 1,046,924円

- 24回：月の返済額 42,981円／利息の合計 31,549円

- 36回の1回目：利息 2,500円・元金 26,581円

- 最終回の残高は0円

返済回数を36から24に変えるだけで、利息の合計が約15,000円減ることが一目で分かります。

表の数字が違うときは、年利が「3」ではなく「0.03」で入っているかを確かめます。

■6. 気をつけること

- 実際の契約は、金融機関の返済予定表が正です。端数の処理や初回の日数の違いで、この表と数円〜数百円ずれることがあります。

- この表は元利均等（毎月の返済額が同じ）の場合の計算です。ボーナス返済・変動金利・繰上返済は扱っていません。

- 借りるべきか、どの条件がよいかは、この記事では判断しません。計算のやり方だけを書いています。

■7. 次の一歩

- 回数・年利を変えた表を並べると、条件ごとの利息の差が見比べられます。

- 返済が終わるまでの残高の推移は、E列をグラフにすると見やすくなります。