銀行からの借入金の利息や返済額をエクセルでかんたんに計算

計算

銀行や信用金庫から資金調達をする場合、事前に事業計画の提出を求められることがあります。

売上や利益については事業主の思惑が入りますが、借入金の返済計画も含めて事業計画を練らなくてはなりません。

しかし、借入金の利息や返済額の計算はとにかくややこしく、銀行からもらう「返済予定表」を頼りにしている方も多いと思います。

借入金の利息計算は電卓を使うと面倒なのですが、エクセルを使うと簡単に結果を算出できます

もちろん、普段エクセルを使い慣れていない人でもこれから解説する通りに実践すれば、かんたんに将来の返済計画が立てられ、資金繰りの改善や事業計画の作成に役立てることができます。

今回は、借入金の利息や返済総額を掲載する◯つの方法を、エクセルを使って誰でも使えるように解説します。

元金均等返済と元利均等返済

元金均等返済と元利均等返済

借金の返し方には「元金均等返済」と「元利金等返済」の2種類がありますが、私の経験上「元金均等返済」が選択されることが多いように思います。

▶元金均等返済
銀行や日本政策金融公庫からの借入では、元金均等返済が選択されることが多いと思います。
元金均等返済方式は、毎月返済する「元金が固定されている」のが特徴で、返済額に占める元金部分は完済まで変わりません。

つまり、初期の頃の返済額は多くなりますが、返済回数を重ねるごとに毎月の返済額が減っていきます。結果的に、返済総額が少なくなるため「初期の返済は多いが、返済総額は小さくなる」のが特徴です。

▶元利金等返済
元利金等方式は、「毎月の返済金額が固定されている」のが特徴です。

初期の返済は少なくて済むが、返済総額は大きくなる」のが元利均等返済となります。

返済方法によって毎月のキャッシュフローや返済総額が変わってくるので、どちらを選択しているか確認することをおすすめします。

毎月のキャッシュフローを安定させたい場合は「元利均等方式」を選択(ただし返済総額は多くなる)し、返済総額(利息)を少なくしたい場合は「元金均等方式」を選択(ただし最初の頃は返済額が増える)します。

日割り計算が基本

カレンダー

銀行や信用金庫からの借入は、基本的に「日割り計算」で行われます。

また、借入の契約をした場合は、返済開始を1~2ヶ月待ち、その間は利息だけを支払うという形式を取ることも少なくありません。

これらの仕組みに柔軟に対応できる日割り計算の方法がこちらです。

▶日割り計算の方法
返済金額 = 借入残高 × 利率 ÷ 365 × 日数

※エクセルで入力する場合は
= 借入残高 * 利率 / 365 * 日数

借入利率が年3%なら「3% ÷ 365日」をして利率を1日単位にします。その後、1ヶ月の借入日数(多くの場合30日または31日)を掛け、これを借入残高に掛けることで利息計算ができます。

元金均等方式の場合は、「毎月の返済元金+利息」が返済額となります。元利均等方式なら月々の返済額は固定です。

PMT関数で毎月の支払額を計算する

PMT関数

返済方式が「元利均等返済」の場合、エクセルのPMT関数を使うことで毎月の支払額を計算できます。

▶PMT関数の使い方
=PMT(利率,期間,現在価値,[将来価値],[支払期日])

[ ]で囲われている「将来価値」と「支払期日」は省略可能です。

利率
借入金利のことです。利率は12ヶ月で割り算して使います
期間
返済回数のことです。1年だったら12回、10年なら120回です
現在価値
現在の借入金残高を入力します
将来価値(省略可能
最後に残る金額を入力しますが、多くの場合最後は0円になりますので、省略可能です
支払期日(省略可能
0を入力すると月末、1を入力すると月初で計算しますが、省略可能です

PMT関数は、元利均等返済のケースを想定しているので、毎月の返済額は同じです。

この金利で24回払いだったら毎月の返済額はいくらになるのだろうか…」という疑問に対する答えを知りたい時に使えます。

まず最初に、エクセルに必要な数値を入力します。
今回は、金利2.5%、期間は60回(5年)、借入残高500万円としました。金利の部分に「%」という単位を入れるのを忘れないでください。
PMT関数の説明1

エクセルの画面上部にある「数式」タブを選択すると、一番左に「関数の挿入」というボタンが表示されます。計算結果を表示させたい位置(今回はD2セル)にカーソルを置いて「関数の挿入」ボタンを押します。
PMT関数の説明2

「関数の挿入」を押すとダイアログが立ち上がります。関数名がたくさん並んでいるリストの中から「PMT」を選択し、「OK」をクリック。
PMT関数の説明3

「関数の引数」の入力画面です。画像の通り、セル番号をベタ打ちしても良いのですが、「利率にカーソルを合わせる → A2セルを選択 → 期間にカーソルを合わせる → B2セルを選択…」とマウスでクリックしていくだけで、自動的にセル番号が入力されます。

ただし、利率の部分だけは「/12」を入れることを忘れないよう注意してください。「A2/12」は「A2 ÷ 12」を意味します。(金利を12ヶ月で割り算するため)

PMT関数では「将来価値」と「支払期日」は省略できるので、今回は空白にしておきます。入力が完了したら「OK」をクリックします。
PMT関数の説明4

すると、月々の返済額(D2セル)に計算結果が表示されます。金利2.5%、期間60回(5年)、借入残高500万円の場合、毎月88,737円の返済が必要だとわかりました。
PMT関数の説明5

ちなみに、元利均等返済は毎月の返済額が固定されているので、「月々の返済額88,737円 × 60回 = 5,324,220円」が返済総額となります。

利率、期間、現在価値(借入残高)の部分は「変数」になっているので、自由に変更可能です。

例えば、借入金額を600万円にしたら月々の返済額はどれくらい増えるのだろう、期間を60回(5年)から84回(7年)に伸ばすと毎月の返済額はどれくらい減るのだろうといった「感応度分析」ができ、事業計画の策定に活かせます。

PPMT関数で元金部分だけを計算

PPMT関数

PMT関数を使うと、元利均等方式を選択した場合の毎月の返済額を知ることができます。

しかし、毎月の返済額の内訳(元金がいくらで利息がいくらなのか?)についてはPMT関数では計算できません。

元利均等方式で「元金部分」を知るには「PPMT関数」が使えます。

▶PPMT関数の使い方
=PPMT(利率,期,期間,現在価値,[将来価値],[支払期日])

[ ]で囲われている「将来価値」と「支払期日」は省略可能です。

利率
借入金利のことです。利率は12ヶ月で割り算して使います
何回目の返済を計算するか
期間
返済回数のことです。1年だったら12回、10年だったら120回です
現在価値
現在の借入金残高を入力します
将来価値(省略可能
最後に残る金額を入力しますが、多くの場合最後は0円になりますので、省略可能です
支払期日(省略可能
0を入力すると月末、1を入力すると月初で計算しますが、省略可能です

具体的な計算方法は先ほどとほとんど同じです。

PPMT関数では「期(何回目の返済か?)」を指定する必要があるため、新しく「期」という項目を追加しました。
PPMT関数の使い方1

カーソルを「月々の返済額(E2セル)」に合わせ、エクセルの上部の「数式」タブを選びます。一番左の「関数の挿入」をクリックします。
PPMT関数の使い方2

ダイアログが立ち上がりますので、「関数の挿入」で「PPMT」を選択します。候補に表示されていない場合は、「関数の検索」で「PPMT」と入力すれば見つかるはずです。
PPMT関数の使い方3

「関数の引数」を設定します。利率の部分には「/12」を入れることを忘れないように注意します。
PPMT関数の使い方4

E2セルに計算結果が表示されます。利率2.5%、期間60回(5年)、借入金額500万円の場合、1回目の返済金額の元金は78,320円であることがわかりました。
PPMT関数の計算結果

IPMT関数で利息部分だけを計算

IPMT関数

PPMT関数は、元利均等方式の「元金」部分を計算する関数でしたが、同じく「利息」部分を計算する関数に「IPMT関数」があります。

使い方はPPMT関数と全く同じです。先ほどの計算結果を表示した「E2セル」の「PPMT」を「IPMT」と書き換えるだけで、利息がいくらか算出できます。

▶IPMT関数の使い方
=IPMT(利率,期,期間,現在価値,[将来価値],[支払期日])

[ ]で囲われている「将来価値」と「支払期日」は省略可能です。

ここまでのまとめ。

PMT関数
元利均等方式の月々の返済額(元金+利息)を計算できる。元利均等返済は月々の返済額は固定なので「期」は指定しない。
PPMT関数
元利均等方式の特定の期(返済回数)における返済額の「元金部分」が計算できる。
IPMT関数
元利均等方式の特定の期(返済回数)における返済額の「利息部分」が計算できる。

元金均等方式を計算するISPMT関数

ISPMT関数

私の経験上、銀行からの借入では「元金均等返済」が多く使われているように思います。

元利均等返済は毎月の「元金+利息」の返済額が固定でしたが、元金均等方式は元金部分が固定となっているのが特徴です。

つまり、当初の返済額は増えますが、返済回数をこなしていくに従い、月々の返済額が減っていくのが特徴です。

元金均等方式の「利息部分」を計算するエクセル関数に「ISPMT関数」があります。

▶ISPMT関数の使い方
=ISPMT(利率,期,期間,現在価値)

利率
借入金利のことです。利率は12ヶ月で割り算して使います
何回目の返済を計算するか
期間
返済回数のことです。1年だったら12回、10年なら120回です
現在価値
現在の借入金残高を入力します

使い方は前述した「PPMT関数」と同じです。
ISPPMT関数の使い方1

カーソルを「月々の返済額(E2セル)」に合わせ、エクセルの上部の「数式」タブを選びます。一番左の「関数の挿入」をクリックします。
ISPMT関数の使い方2

ダイアログ「関数の挿入」で「ISPMT」を選択します。候補に表示されていない場合は、「関数の検索」で「ISPMT」と入力します。
ISPMT関数の使い方3

「関数の引数」を設定します。利率の部分には「/12」を入れることを忘れないように注意します。
ISPMT関数の使い方4

E2セルに計算結果が表示されます。利率2.5%、期間60回(5年)、借入金額500万円の場合、1回目の返済金額の利息は10,243円であることがわかりました。
ISPMT関数の計算結果

元金均等方式の元金を計算

元金均等方式はその名の通り、元金部分は固定です。

500万円を60回(5年)で返済する場合は単純に「500万円 ÷ 60回 = 83,333円」が月々の返済元金となります。

つまり、ISPMT関数で算出した「利息部分」+「固定されている元金部分」の合計が、毎月の返済額となります。

元金均等方式の場合、初回の返済額が最も大きく、その後返済額は減っていきます。

データテーブルを使った感応度分析

データ

エクセルには「データテーブル」という強力な機能が付いています。

データテーブルを活用することで、感応度分析(シミュレーション)ができます。

やや上級編となりますが、先ほどのPMT関数(元利均等方式の月々の返済額(元金+利息)を計算)を使ってデータテーブルの使い方を解説します。

先ほどと同じく、基本となる数値を、金利2.5%、期間は60回(5年)、借入残高500万円とします。
PMT関数の説明1

こちらがPMT関数の計算結果です。上記の条件で元利均等方式を選択した場合、毎月の返済額は88,737円になることがわかりました。
PMT関数の説明5

このように基本となる計画を作った後に、

といった様々なシミュレーションが作れると、より事業計画を具体的に検討できます。

今回は「月々の返済額を10万円以内に抑える」という条件を置いた上で、借入金額を最大化するシミュレーションを行います。

まずはじめに、利率と借入額の予測を作成します。
列の部分に想定借入額として400万円~1,000万円までを入力、行の部分に想定借入利率として1.0%~3.5%を入力しました。
データテーブルの使い方

次に、想定借入額と想定借入利率のクロスする部分(A5セル)に「=D2」と入力し、D2セルで算出したPMT関数の計算結果(月々の返済額)を表示させます。
データテーブルの使い方2

先ほど想定数値を入力したエリアをマウスで選択して囲います。その後、エクセル上部の「データ」タブを選び、「What-IF分析」から「データテーブル」を選択します。
データテーブルの使い方3

ダイアログが立ち上がるので「行の代入セル」にカーソルを合わせて「A2セル」をクリック、「列の代入セル」にカーソルを合わせて「C2セル」をクリックします。
データテーブルの使い方4

すると、行・列で想定していた数値のあらゆる組み合わせで、月々の返済額がいくらになるかをエクセルが自動計算してくれます。
データテーブルの使い方5

これだと少し見にくいので、計算結果の表示部分を選択し「ホーム」タブにある「条件付き書式」から、「セルの強調表示ルール」を選びます。セルの強調表示ルールは「指定の値より大きい」を選択します。
データテーブルの使い方6

今回は「月々の返済額を10万円以内に抑える」ことが条件でしたので、「-100000」と入力して「OK」をクリック。
データテーブルの使い方7

すると、データテーブルのシミュレーションの中から、月々の返済額が10万円以内の枠だけが強調表示され見やすくなります。
データテーブルの計算結果

今回の計算結果では、借入金額を600万円にしてしまうと、仮に借入利率を1.0%まで引き下げても月々の返済額が10万円を超えてしまうことがわかりました。

月々の返済額を10万円以下にするという資金繰りで事業計画を作る場合、返済期間が5年(60回返済)なら500万円が借入の限界のようです。

逆に、当初予想していた2.5%の金利で審査が通らなくても、他の金融機関なら金利を引き上げることで融資の審査に通してくれるかもしれません。

今回のシミュレーションでは、借入金額が500万円なら、金利が3.5%まで上がっても月々の返済額は10万円以下となり、計画通りに事業を進めることが可能です。

信用保証料に気をつける

打ち合わせ

最後に、信用保証料について触れておきます。

銀行や信用金庫から融資を受ける場合、金融機関がリスクを追って貸し出す「プロパー融資」と、信用保証協会を通す「信用保証協会付き融資」があります。

創業当初の企業などは大抵、プロパー融資は受けられず、まずは保証協会付き融資になるケースが多いです。

しかし、保証協会を通す場合は別途「信用保証料」を負担する必要があります。

信用保証料は融資の契約を結ぶ時に払い込むことが多いのですが、これは実際の借入金利には含まれていません。

つまり、年1.2%の利率で借りられると思っていても、信用保証料が0.8%の利息相当額になっている場合は、実質的な借入利率は2.0%として考える必要があります。

低金利だと思って借りたは良いものの、実際の支払額は「利息+信用保証料」となっており、金利換算すると実は高利で借りていたということにならないよう注意が必要です。

信用保証協会付きの融資を受ける場合は、金融機関の担当者に「信用保証料を含んだ実質金利を教えて欲しい」と言えば、計算してくれます。

ちなみに、日本政策金融公庫についてはすべてプロパー融資ですので、信用保証料などが加算されることはありません。

続いての記事は「開業まもない事業者が資金調達先として検討できる5つの窓口」です。創業期の資金調達先をまとめています。

関連リンク

「いいね」でビジネスに役立つ情報をお届けします

購読
RSSで購読
feedlyで購読
法人口座開設ができるネット銀行一覧