Home » Excel(エクセル)の関数・数式の使い方 » 財務関数 » ローン返済予定表・償還表の作成方法−PMT関数

ローン返済予定表・償還表の作成方法−PMT関数

対象：Excel2002, Excel2003, Excel2007

元利均等返済の、元金分を計算するPPMT（Principal PayMenT）関数と、利息分を計算するIPMT（Interest PayMenT）関数を組み合わせて、ローンの返済予定表・償還表を作成する方法をご紹介しました。

ローン返済予定表・償還表を作成する計算式はいくつか考えれますので、別の方法もご紹介しましょう。

前回使ったPPMT関数やIPMT関数よりもメジャーな財務関数である、PMT（PayMenT）関数を使った方法です。

何が違うかというと、
　返済額
　返済額の元金該当部分
　返済額の利息額部分
を、どう求めるかです。

先日ご紹介した方法は、返済額のうち元金に該当する部分と、利息に該当する部分をそれぞれ関数を使って計算して、返済額はそれらの計算結果を合計して求めていました。

今回ご紹介する方法は、返済額をPMT関数を使って計算して、返済額のうち利息に該当する部分は関数を使わずに計算し、返済額のうち元金に該当する部分は返済額から利息該当額をマイナスして求めます。

考え方が違うのですから、計算式はもちろん異なります。

［スポンサードリンク］

ですが、ローン返済表・償還表を作成する操作手順的な面は、前回ご紹介したのと基本的に同じです。

▼操作概要：ローン返済表・償還表を作成する
※年利：3.5%、20年（240回払い）、2500万円のローンを組んだとき、A列に返済回数、B列に日付、C列に年利、D列に返済額、E列に返済額のうち元金分、F列に返済額のうち利息分、G列に借入残高を表示するローン返済表・償還表を作成する例

A2:A242セルに「0」から「240」の数値を入力
　↓
B2セルに返済開始日の1か月前の日付を入力
　↓
B3セルに
「=DATE(YEAR(B2),MONTH(B2)+1,DAY(B2))」
という数式を入力
　↓
C2セルに「3.5%」と入力
　↓
C3セルに
「=C2」
という数式を入力
　↓
G2セルに「25000000」と入力
　↓
D3セルに
「=-PMT(C3/12,MAX(A:A),$G$2)」
という数式を入力
　↓
F3セルに
「=G2*C3/12」
という数式を入力
　↓
E3セルに
「=D3-F3」
という数式を入力
　↓
G3セルに
「=G2-E3」
という数式を入力
　↓
B3:G3セルをB242:G242セルまでオートフィル

先日ご紹介したのと違うのは、
　D3セル（返済額）
　E3セル（返済額の元金該当部分）
　F3セル（返済額の利息額部分）
に入力する数式だけです。

先日ご紹介した方法では、
　D3セルに「=E3+F3」
　E3セルに「=-PPMT(C3/12,A3,MAX(A:A),$G$2)」
　F3セルに「=-IPMT(C3/12,A3,MAX(A:A),$G$2)」
という数式を入力していました。

今回は、
　D3セルに「=-PMT(C3/12,MAX(A:A),$G$2)」
　E3セルに「=D3-F3」
　F3セルに「=G2*C3/12」
という数式を入力しています。

D3セルに入力した返済額を求める
「=-PMT(C3/12,MAX(A:A),$G$2)」
という数式は、PMT関数をご存じの方なら難しくないはずです。

PMT関数も引数に正の数を指定すると負の数が返されます。

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

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

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

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

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

PMT関数をまったくご存じないまま、上記の説明を読んでも理解するのは、大変かもしれません。その場合、PMT関数を単体で理解することをおすすめします。

F3セルの「=G2*C3/12」という数式は、利息計算の基本をそのままExcelの数式にしただけです。

利息は借入額の残金（G2）に、その期間の金利を掛け算したものですから、残金（最初の返済時には借入額とイコールです）C3セルに、その期間の金利である、年利を1年の月数「12」で割った「C3/12」を掛けています。

PMT関数を使ってD3セルに求めた返済額から、利息に該当する額であるF3をマイナスすれば、返済額のうち元金該当部分が計算できます。これがE3セルに入力した「=D3-F3」の意味です。

以下の手順は前回ご紹介したのと同じですが、念のために上記の数式を除く部分の操作について記述しておきます。（先日のPPMT関数とIPMT関数を使う方法を理解している方は読んでいただく必要はありません。）

ローン残額がいくらなのかを知るために、G3セルに入力した「=G2-E3」という数式を入力しています。うっかり「=G2-D3」としてしいがちですが、あくまでも、返済額の元金分である「E3」をマイナスしなければいけません。

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」が、ローンを組んだ額・借り入れ額です。