こんにちは、ふじみやです。
以前、ローンの返済額を算出したり、ローンの返済額から借入額を算出したりできるシミュレーターを作成しました。今回はそういった機能も利用して、Excelでローンの返済予定表を作成します。
実際にお借入れをした際に銀行等より発行される返済予定表とは一致しないことがありますので、ご注意ください。
返済予定表の作成方針
元利均等返済における元本返済額の計算方法は、Excelの関数を使用する方法とゴールシーク機能を使用する方法の2つがありますが、今回は簡単に作成可能なExcelの関数を使用する方法を採用します。
（参考）返済方法の違い
ローンの返済方法には、元利均等返済と元本均等返済、そしてマイカーローンや住宅ローンでは利用されませんが満期一括返済の3つの返済方法があります。
元利均等返済
元利均等返済は、毎回の支払額（元本と利息の合計額）が均等になるように返済をする方法です。
例えば毎月5万円の支払いといったように、借り手からすれば支払額が変わらないという点がメリットですが、返済開始当初は支払額のうち利息が占める割合が多く元本はなかなか減らないという点がデメリットとなります。
元本均等返済
元本均等返済は、毎回の「元本の」支払額が一定であるという返済方法です。
そのため、元利均等返済に比べると借入残高の一定のペースで減少し、利息の総支払額は少なくなります。他方で、返済開始当初は利息負担分だけ支払額が大きくなるため、家計的な負担が大きくなってしまいます。
満期一括返済
期日一括返済とも言いますが、その名の通り借入期間中は元本の返済がなく、満期日に全ての借入元本を返済する方法のことです。通常は、借入人は一定期間毎に利息を支払うだけで済むため、借入期間中の資金繰り的には余裕が生まれますが、しっかりと返済用の資金を蓄えておかないと債務不履行に陥ってしまう危険性があるものです。
返済予定表に使用するExcel関数と使い方
元利均等返済における元本支払額を計算する PPMT関数 と、同じく利息支払額を計算する IPMT関数 を使用します。
PPMT関数
PPMT (利率, 期, 期間, 現在価値, 将来価値, 支払期日)
| 利率 | 借入元本に対して課される金利 |
| 期 | この返済が何回目のものか |
| 期日 | 借入元本を完済するまでの回数 |
| 現在価値 | 当初の借入額 |
| 将来価値 | 最終回の返済後に借入元本がいくら残るか。0となる場合には省略可 |
| 支払期日 | 0または省略で期末の支払。それ以外なら期初の支払 |
例えば、200万円の借入元本（年利2.000%）を5年（全60回）で元利均等返済にて返済をする場合、第１回目の元本返済額は次にように入力することで計算がされます。なお、計算結果はマイナス値となりますので、必要に応じてマイナスの符号をPPMT関数の前に入力しましょう。
=PPMT(2%/12,1,5*12,2000000)
前提条件より借入利率は2%ですがこれは年利となっているため、毎月返済を行う場合には12で割り月利を算出します。同様に借入期間は5年ですので、返済回数にするため12を掛けています。
IPMT関数
IPMT (利率, 期, 期間, 現在価値, 将来価値, 支払期日)
IPMT関数はPPMT関数と使い方が全く同じですので、それぞれの引数の入力方法については上記のPPMT関数をご覧ください。
返済予定表の作成
それでは作成に必要な関数が確認できましたので、Excelで作成していきましょう。
完成形のイメージ
上記の前提を踏まえた返済予定表のイメージは次の通りとなりますので、作成時の参考としていただければと思います。以前作成した返済希望額から借入額を算出するマクロを使用していますが、この機能が必要ないという方は無視していただいて問題ありません。
前提条件表の作成
まずは、計算のための引数を入力する前提条件表を作成しましょう。
Excel上部に以下を入力する項目を作りましょう。
- 借入金額：200万円（うちボーナス返済30万円）
- 借入期間：5年（全60回払）
- 借入利率：年率2.000%
- 返済方法：元利均等返済
借入金額（200万円）とボーナス返済分（30万円）から、毎月返済分（170万円）を計算するセルを忘れずに作っておきましょう。
結果を表示する表を作成
返済予定表は毎月返済分の返済予定表とボーナス返済分の返済予定表を合算したものとなります。
そのため、まずは返済予定表のひな型を作っておきましょう。記載項目は以下があれば十分です。
- 返済回数（第何回目の返済かを示す箇所）
- 返済予定月
- 支払利息
- 支払元本
- 利息と元本の支払合計額
- 月末残高
毎月返済分の予定表とボーナス返済分の予定表の合計ですので、もっと記載項目を省略することも可能ですが、前提条件表で使用している列数と同じくらいにするのと見栄えがよくなります。
毎月返済分の返済予定表作成
上記のひな型と同じような表を作成しますが、「返済回数」や「返済予定月」については省略可能です。
IPMT関数やPPMT関数等の前に入力しているIFERROR関数を入力することで見栄えを改善することができます。
IFERROR (値, エラーの場合の値)
これは値がエラー値である場合、代わりにどういった値を返すかということを指定できるものです。
PPMT関数/IPMT関数では例えば指定された期間を超過した期が引数として入力された場合に「#NUM!」とエラーを返してきますが、IFERROR関数を利用することで「#NUM!」の代わりに「0」であったり空白を表示するように指定することができます。
ボーナス返済分の返済予定表作成
基本的な作り方は毎月返済分と変わりませんが、ボーナス返済分ということで追加の関数を検討することが必要となります。例えば、私の例ではセルN22（利息の初回支払額）に以下のような関数を入力しています。
=+IF($B22>$F$9*12,"",IF(MOD($B22,6)=0,IFERROR(-IPMT($F$10/2,$B22/6,$F$92,$Q$21),""),0))この関数を読み解くと次のような手順で計算をするようになっています。
IPMT関数内の引数は、ボーナス返済（年２回を想定）ということで年利を2で割るとともに、返済回数の進み方が毎月返済分の1/6となっている点にご注意ください。
毎月返済分とボーナス返済分を合算
ここは2つの返済予定表を足し合わせているだけです。
関数の入力が全て完了したら表の一番下（住宅ローンに対応するなら#420）まで数式を貼付して完成です。
まとめ
少し見慣れない関数も出てきたかと思いますが、家計の将来予測であったり企業の財務モデリングを作成する際には必須の考え方となりますので、そういったことをやってみたいという方は是非試してみてください。
最後に、今回作成したExcelファイル（.xlsm形式）をダウンロードできるようにしていますが、マクロを利用しているためセキュリティ的に気になるという方は上記の方法でご自身でシミュレーターを作成してみてください。
一般的に、他人が作成したマクロには悪意のあるプログラムが仕込まれている可能性もありますので、コードを確認せずに利用することはオススメできません。
また、こういったExcelの表の見せ方をもっと知りたいという方は 外資系金融のＥｘｃｅｌ作成術―表の見せ方＆財務モデルの組み方 という本がオススメですので是非手に取ってみてください。
それではまた。
コメント