「毎月いくら元本を返していて、残高があといくらか、すぐ答えられますか？」——複数の借入がある会社で、この質問に即答できる経理は多くありません。返済予定表は金融機関からもらったきり、ファイルの奥で眠っているのが実態です。
この記事では、PMT・IPMT・PPMT関数を使った返済予定表のExcelでの作り方と、複数借入の一覧管理、資金繰り表への連携までを解説します。
前提知識：元利均等と元金均等の違い
| 方式 | 毎月の返済額 | 特徴 |
|---|---|---|
| 元利均等返済 | 毎月一定（元本＋利息の合計が固定） | 資金繰りが読みやすい。初期は利息の割合が大きい |
| 元金均等返済 | 逓減（元本固定＋利息は残高に応じ減少） | 総支払利息は少ない。初期の返済負担が重い |
もう1つ重要な整理は、費用になるのは利息だけで、元本返済は費用にならない（その代わり借入時の入金も収益でない）ことです。損益と資金繰りのズレの典型がここにあります。（関連記事：No.16 資金繰り表の作り方）
Excelで返済予定表を作る
元利均等：PMT・IPMT・PPMT関数で一発計算
毎月返済額 =-PMT(年利率/12, 返済回数, 借入額)
n回目の利息 =-IPMT(年利率/12, n, 返済回数, 借入額)
n回目の元本 =-PPMT(年利率/12, n, 返済回数, 借入額)先頭にマイナスを付けるのは、Excelの財務関数が支払を負数で返すためです。1行1回の返済スケジュール表（回数／返済日／返済額／元本／利息／残高）を作り、残高は「前回残高−今回元本」でリレーさせます。
元金均等：数式は単純
毎回の元本 =借入額/返済回数
n回目の利息 =前回残高×年利率/12
n回目の返済額 =元本+利息据置期間（最初の6ヶ月は利息のみ等）がある場合は、据置回数分は元本0・利息のみの行にし、以降の回数で元本を割ります。コロナ関連融資などで据置付きの借入は多く、据置明けに返済額が跳ね上がるタイミングを可視化できるのがExcel自作の利点です。
返済日はEOMONTHとWORKDAYで自動生成
翌月末返済 =EOMONTH(前回返済日,1)
（銀行休業日調整が必要なら =WORKDAY(EOMONTH(基準日,1)+1,-1) 等で前営業日に）複数借入の一覧管理シート
借入ごとの予定表シートとは別に、全借入を横断するサマリーシートを作ります。持たせる列は次のとおりです。
- 金融機関／借入日／当初額／利率／返済方式／最終返済日
- 現在残高（各予定表から参照）／月間返済額（元本・利息別）
- 保証協会付きか否か／担保・経営者保証の有無
月間元本返済合計 =SUMIF(各借入の当月元本) →資金繰り表の財務収支へ
年間支払利息合計 =SUMIF(各借入の当期利息) →損益計画の支払利息へこのサマリーがあると、金融機関との面談で求められる「借入金一覧表（銀行別残高明細）」がいつでも出せる状態になり、借換えや一本化の検討材料にもなります。（関連記事：No.80 銀行融資に通る数値計画）
実務での活用ポイント
- 決算チェック：予定表の期末残高と試算表の借入金残高を突合する。ズレていれば記帳ミス（元本と利息の按分誤り）が濃厚
- 債務償還年数の自動計算：借入残高合計÷（税引後利益＋減価償却費）をサマリーに表示。10年超なら金融機関の評価が厳しくなる目安
- 繰上返済の検討：支払利息の削減額と手元資金の減少を並べて比較。金利より資金繰りの安全性を優先するのが中小企業の原則
返済予定表テンプレートのご案内
元利均等・元金均等・据置対応の予定表と、複数借入サマリー（債務償還年数の自動計算付き）をセットにした借入金返済予定表テンプレート（Excel）を用意しています。資金繰り表テンプレートと連携する設計です。
まとめ
- 元利均等はPMT・IPMT・PPMT関数、元金均等は単純な数式で予定表が作れる
- 据置期間明けの返済額増加を可視化できるのが自作の最大の利点
- 複数借入はサマリーシートで横断管理し、資金繰り表・損益計画へ数式連携させる
- 予定表の残高と試算表の残高の突合を決算チェックに組み込む
コメント