金融機関からの借入れがあると返済予定表をもらいます。
Excelにまとめておくと管理しやすくなります。
返済予定表といえば「紙」
銀行などの金融機関からお金を借りて資金繰りを手当てするということがあります。
それで新しいことに投資したりお金を増やせるわけですが、それでいいというものでもなく、それ以降は毎月返済していかなければなりません。
毎月いくらお金が出ていくのかということも考えて資金繰りをチェックしていく必要があります。
返済予定表は金融機関からもらえますが、ほぼ紙でもらうことになります。ピロっと剥がして中身が見えるハガキだったりもするわけです。
たいていの借入れは長期にもなりますし、複数の金融機関から借入れがあるとなると、いつ・いくら・いつまで、というのがわかりにくくなりますし、毎回電卓で計算しないといけません…。年数がたてば返済予定表がヨレヨレになったりということも…。
なのでExcelでデータ化しておくことがおすすめです。
スマホでスキャンしたPDFをExcelに変換することもできます（加工の手間はかかりますが）。
で、今回はPDF→Excelという流れではなく、Excelに最低限必要な情報だけを入力し、関数で返済予定表をつくってみるという話です。
Excel関数で返済予定表をつくる流れ
返済方法には元金均等返済の場合と元利均等返済があります。今回は前者（元金均等返済）の場合を前提にしてみます。
イメージとしてはこのようなものです。
元金均等返済は、毎月同額の元本を返済する方法です。なので利息込みの返済額は毎月変動します。
これに対して元利均等返済は、毎月の返済額を同額とする返済。最初は利息ばかり払っている感じですが、やがて元本の返済割合が多くなっていきます。
で、今回は前者（元金均等返済）の場合についてExcelでつくる流れをまとめてみました。
DATED関数にEOMONTH関数を組み合わせて月数（回数）を算出する
まず返済予定表からA1セル～D4セルのように契約条件などを入力しておきます。
B4セルの返済月数は返済予定表から入力してもいいのですが、借入日から返済期日までの月数（回数）を関数で計算することもできます。
月数を計算するときに考えられるのはDATEDIF関数です。
借入日（2023/9/20）がB2セル、返済期日（2029/9/15）はB3セルなので、
=DATEDIF(B2,B3,”M”)
“M”は月数で表示。”Y”にすると年数で表示できます。
で、これで月数を求めると71ヶ月となり、返済予定表の72ヶ月に1足りていません。
これは、DATEDIF関数が求めるのは満月数で1ヶ月に満たないと「0ヶ月」とカウントされるためです。
なので、
=DATEDIF(B2,B3,”M”)+1
としてもいいのですが、違う方法としてDATEDIF関数にEOMONTH関数を組み合わせる方法を試みてみます。
EOMONTH関数は月末日を返す関数。
たとえば、=EOMONTH（B2,0,”M”）とすると、
B2セル（2023/9/20）を「,」カンマで区切って「0」とすることで当月の末日（2023/9/30）の日付を返します。
翌月末にしたいときは「”+1″」、前月末なら「”-1″」という感じです。
これを踏まえて、
=DATEDIF(EOMONTH(B2,0),EOMONTH(B3,0),”M”) とすると、
借入日の2023/9/20の月末日から返済期日2029/9/15の月末日までを72ヶ月と表示できます（やり方はいろいろありますが）
元金均等返済の場合の返済予定表
次に、返済予定表を。
とはいえ元金均等返済の場合は、元金は毎月同額になるのでとくに難しい計算は必要ありません。
基本は借入額を月数で割ればいいのでしょうが、銀行の返済元本は割り切れない金額を最終回で調整したりもするため、それに合わせたければ、そこを合わせるように計算する必要があります。
D8セルに元本返済額（=$D$2/&$B$4）を入力し、G8セルに「=G7-D8」と入力します。
で、D8セルを選択した状態から、右下隅にカーソルを合わせてダブルクリックすると、72回目（最下行）までD8セルと同じ金額が反映されます。G8セルも同様にやってみましょう。
DATEIF関数で日数を算出する
今度は毎月の利息を計算するための日数を算出します。
前述のDATEIF関数でこのように日数を。
=DATEIF（B7,B8,”D”）
「”D”」とすることでDAY（日）を表示することができます。
これも前述のように72回目の行までコピーしましょう。
利息を計算する
利息の計算は、
=+ROUNDDOWN(G13*$D$3*F13/365,0) で計算することができます（利率のD3セルは絶対参照にするのを忘れずに）。ちなみに、INT関数を使ってでもいいでしょう。
利息を計算する（ISPMT関数）
前述の利息は、ISPMT関数で計算することもできます。
=ISPMT(年利率*期,期間,,現在価値) という計算式です。
期は利息を求めたい期を指定します。最初の期は「0」、次の期は「1」という感じです。
ここでは借入金額（G列の現在価値）を毎月変動させるので「0」にしておきました。
数式にするとこうなります。
=ISPMT($D$3*F8/365,0,$B$4,G8)
ただ、ISPMTは支払額を返す関数。
利息はマイナス表示になって、C列の返済額がおかしくなってしまいます。
なので、数式にマイナスをつけておきましょう。
あと、このままでは、前述のROUNDDOWN関数で計算した利息の合計と微妙に合わなくなってしまうので、気になる用であればINT関数やROUNDDOWN関数を使っておきましょう。
INTだとこういう式です。
=INT(-ISPMT($D$3*F8/365,0,$B$4,G8))
Excelで返済予定表をまとめておくメリット
Excelで返済予定表を管理しておくと、
・繰上返済するとしたら、利息の負担がどのくらい減るのかすぐにわかる
・貸借対照表（B/S）の1年以内返済予定長期借入金、長期借入金の残高のチェックがすぐできる
・複数の金融機関から借入れがあっても、毎月の返済額の合計がいくらなのかを返済が終わるまでひと目でわかる
といったことができます。
たとえば、10回目を返済したところで100万円を繰上返済するとした場合、対応する利息のセルを範囲選択するだけで、
Excelの右下の合計を見ればすぐにわかります。
電卓を使っても計算できるのでそれはそれでいいわけですが、やはりデータで管理していたほうがいろいろと便利です。
Excelの勉強にもなりますし、「こういうときって、こんなことはできないかな？」と調べながらやってみるとスキルも磨けますし応用もききやすくなります。
紙の返済予定表でそのまま管理していて、Excelでデータ管理することに興味があれば参考にしていただければうれしいです。
■編集後記
昨日は朝の習慣のあとは会計士業を中心に。夕方にケーキ屋へ行ってお土産を買って（じぶん用も）、SWITHを直してくれた親戚にお礼を渡しに行きました。外は5月とは思えないほどの寒さ。道内、雪だったところもあったようで体調管理に気をつけたいところです。
■息子（10歳）
SWITHのコントローラー、前日に直してもらえて快適に使えているようです（8番出口も操作しやすくなったようでますます夢中に…）。
■昨日の1日1新
・とある手続き
