金融機関からの借入金があると、返済予定表をもらいます。
この返済予定表をExcelでまとめるようにしています。
銀行からもらう返済予定表は常に紙・・・
事業をしていると、金融機関から融資を受けることがあります。
融資を受けたらいいというものではなく、当然その後に毎月の返済があります。
将来の資金繰りを考慮したり、毎月の返済でいくらお金が出ていくのか、といったことも把握する必要があります。
毎月の返済予定表も金融機関からもらいますが、たいてい、もらう返済表はハガキだったり、
一昔前？の印刷機で印刷したようなものだったりするわけです。^^;
青いインクのカーボンでおなじみの例のやつです。（笑）
そういった場合、複数の金融機関と取引している場合には、いつにいくら返すのかといったことを将来まで見ようと思うと、どうしてもわかりにくいのです。
その都度、電卓で計算しなければいけません・・・。
そういった悩みを解消するために思いついたのが、Excelでデータ化すること。
また、複数の金融機関から借入をしている場合には、返済予定表も別々で管理しにくいです。
そういった複数の金融機関の返済情報をまとめてつかむにもExcelで作ることはオススメです。
Excelで返済表を作ってみた
返済方法は元金均等返済の場合と元利均等返済があります。
元金均等返済とは、毎月の返済元本が同額の返済です。なので毎月の元本と利息を含めた返済額は変動します。
これに対して、元利均等返済とは、毎月の返済額が一定の返済方法です。最初は利息ばかりを返済している感じですが、返済をしていくにつれて、返済額のうちに元本の占める割合が多くなっていきます。
ここでは、元金均等返済を見てみます。
元金均等返済の場合
元金均等返済の場合には、元金はとくに難しい計算は不要です。
基本は借入金額を月数で割ればいいのでしょうが、銀行の返済元本は割り切れない金額を最後に調整したりするため、それに合わせたければ、そのように計算する必要があります。
E6に元本返済の１回目の金額を入力し、H6セルにH5-E6の算式を入力します。
まずはE6を選択し、カーソルを右下隅にあわせます。
そこでダブルクリックをすると、84回目までE6と同じ金額がコピーされます。残高も同様にやってみましょう。
利息部分もざっくり計算でいいのであれば、＝rounddown(H6*$F$2/12,0)で計算することもできますが、たいてい銀行の返済表は日数で計算しているため、若干の差額が出ます。
キッチリやろうとするなら、日数を関数を使って計算する必要があります。
まずは2017年６月という表示から、６月が何日間あるかというのを計算する必要があります。
DATE関数で月末の日付を取得する
まず、DATE関数で月末の日付を取得します。DATE（年,月,日）です。
例えば、2017年１月１日の場合、＝DATE（2017,1,1）とすると日付を数字で表示した「42736」が表示されます。
前月（12月）末日を表示する場合、＝DATE（2017,1,0）とします。
今回の場合、2017年６月であれば、2017年７月の前月末日を表現できればいいわけなので、＝DATE(B6,C6+1,0)となります。
「C6＋１」で７月を表現しているところがポイントです。
DAY関数で指定月の日数を計算
今度は、これをDAY関数で指定月の日数を計算します。日数といっていますが、実際には末日を計算しています。
先程の＝DATE(B6,C6+1,0)を＝DAY（DATE(B6,C6+1,0)）とすると６月の日数、30が計算されます。
これもオートフィルで84回目までコピーしましょう。
そして、利息は、=ROUNDDOWN(H6*$F$2*G6/365,0)で計算することができます。INT関数を使って計算してもいいでしょう。
元金均等返済の利息を計算する関数 ISPMT関数もある
上記の利息については、ISPMT関数を使って計算することもできます。
ISPMT（年利率,期,回数,借入金額（現在価値））という算式で計算することができます。
期は金利相当額を求めたい期を指定します。最初の期は「０」、次の期は「1」といった感じです。
ここでは借入金額（現在価値）を毎月変動させるので「0」にしておきます。
具体的にはこんな感じです。（セルF1とセルF2についてはF4キーで絶対参照にするのを忘れずに）
＝ISPMT（$F$2*G6/365,0,$F$1,H6）といった算式に。
ただ、計算結果は出ていくお金として認識され、マイナスで表示で返されるため、元利返済額が減ってしまいます。
算式にマイナスを付けておくといいでしょう。この計算式もオートフィルでコピーしておきます。
このままでは細かい誤差はあるかもしれませんが、気になるならINTやRounddownを使うことで解消します。
＝INT(-ISPMT（$F$2*G6/365,0,$F$1,H6））といった算式です。
細かいところよりも、まずはざっくりと流れをつかむことの方が大事です。
Excelで作成するとこんなことができる
Excelで返済表をつくっておけば、
・複数の金融機関でも毎月合計でいくら返済するかが将来まで一目でわかる
・繰上返済をする場合には、いくらの利息削減効果があるかがすぐにわかる
・事業計画を作るときにもExcelから数字を引用すればいいので簡単
といったことができます。
繰上返済をする場合、例えば７回目を返済した時点で100万円を返済する場合には、セルを囲うだけでいくらの利息が削減できるかもすぐにわかります。
この点に難しい計算式は一切必要ありません。
繰上返済による利息削減額は、右下に合計で表示されている87,874円とわかります。
電卓で計算しても出せますが、やはりデータで持っていた方がいろんな使い方ができます。
Excelの勉強にもなります。もっといいやり方があるかもしれません。
でも興味をもったことを調べながらやってみれば、Excelはスキルアップできるものです。
【編集後記】
昨日は１日オフ。自宅でブログをいじったりしていました。ブログを書くこともやっていますが、触っていろいろ試してみることも楽しく、勉強にもなります。
