ローン返済表・償還表を作成する−PPMT関数・IPMT関数

「ローン 返済予定表 作成方法 エクセル」
「住宅ローン 償還表 作成 エクセル」
といった検索がこのサイトで行われていることがあります。

呼び方はいろいろあるようですが、ローンの返済予定表・返済早見表・償還表の類を、Excelを使って自分で作成したいという方の検索です。

このサイトではローン返済のシミュレーションに利用できるテンプレートをご紹介しましたが、Excelに慣れている方の場合、テンプレートに頼らず、自分で自分の使いやすいローン返済表・償還表を作りたいという気持ちになることは理解できます。

Excelでローン返済表・償還表を作成する方法は、いくつか考えられます。

今回は元利均等返済時の、元金分を計算するPPMT関数、利息分を計算するIPMT関数を組み合わせた方法をご紹介しましょう。

どうしても手順はちょっと長くなってしまいますが、以下のような手順でローン返済表・償還表を作成できます。

▼サンプルファイル（003432.xls 107KByte）ダウンロード

サンプルファイルは、上記の手順で作成したExcelで1行目に見出しの文字を入力したり、いくつかの書式設定を行ったものです。

A2:A242セルに「0」から「240」の数値を入力しているのは、今回の例の前提を20年の240回払いとしているためです。

もし30年の360回払いのローン返済表・償還表を作成するのなら「0」から「360」まで、35年420回払いなら「0」から「420」まで入力してください。

「0」から「240」まで入力するのはオートフィルしてもいいですが、［連続データの作成］コマンドを利用するほうが便利だと思います。

2003までのExcelならメニュー［編集］−［フィル］−［連続データの作成］、Excel 2007なら［ホーム］タブ−［編集］グループ−［フィル］−［連続データの作成］から［連続データの作成］ダイアログを表示できます。

B3セルに「=DATE(YEAR(B2),MONTH(B2)+1,DAY(B2))」という数式を入力するのは、基準となるB2セルの日付を変更したときに、表示されている1か月ごとの日付を自動的に更新させるためです。

G2セルに入力した「25000000」が、ローンを組んだ額・借り入れ額です。

E3セルの「=-PPMT(C3/12,A3,MAX(A:A),$G$2)」という数式で、1回目の返済のうち元金分がいくらなのかを計算できます。

PPMT関数では引数に正の数を指定すると負の数が返されます。

ローン返済表・償還表としては正の数で表示されているほうがわかりやすいのでPPMT関数の前に「-」をつけて正の数で表示されるようにしてあります。

第1引数は金利を指定するわけですが、1か月あたりの返済額の元金分計算をするのですから、月利で指定しなければならないので年利を1年の月数「12」で割った「C3/12」と指定しています。

第2引数は何回目の返済なのかを指定するので「A3」を指定しています。

総返済回数を指定する第3引数には、「MAX(A:A)」と指定することでA列の最大の値、今回の例なら「240」が取得できます。

借入額を指定する第4引数は、オートフィルすることを考えて「$G$2」と絶対参照にしています。

「=-PPMT(3.5%/12,A3,240,25000000)」
と数式の中で直接値を指定しても、同じ結果が得られますが、Excelで返済早見表・償還表を作成したいという場合、金利が変わったらどうなるのか、ローンを組む額を変更したらどうなるのかをシミュレーションしたいという要望が出てくるはずですから、数式内で値を指定するよりセル参照の形にしておくほうがいいと思います。

F3セルの「=-IPMT(C3/12,A3,MAX(A:A),$G$2)」という数式については、E3セルの「=-PPMT(C3/12,A3,MAX(A:A),$G$2)」という数式がしっかり理解できれば、同様の考えで理解できるはずです。

返済額のうち元金分と金利分が計算できたので、この2つを「=E3+F3」と合計すれば、1回あたりの返済額が計算できます。

ローン残額がいくらなのかを知るために、G3セルに「=G2-E3」という数式を入力していいます。

ここは、うっかり「=G2-D3」としてしまう方がいらっしゃるかもしれませんが、あくまでも、返済額の元金分である「E3」をマイナスしなければいけません。

B2:G2セルを選択しておいて右下のフィルハンドルをダブルクリックすれば、B2:G2セルに入力した数式が242行目まで一気にコピーできます。

自力でローン返済表・償還表を作成したいという方は、是非チャレンジしてください。