エクセルで借入金の返済額をシュミレーション計算！返済予定表も活用しよう
「いくら返済すれば完済できるのか分からない」というのも、借金が返済できない理由のひとつではないでしょうか。
今回は、エクセルを使って、借入残高や返済金額の計算をする方法を解説します。
計算の方法を読むのも嫌だという人は、こちらのファイルなら数値を入力するだけで利用できますのでダウンロードしてください。
※ウイルスではありません。ファイルを開いた際に表示される画面を使い方の説明で掲載していますので不安な方は確認してください。
ダウンロードしたファイルを開いて数値を入力するだけで自動的に計算してくれるようになっています。
もし使い方が分からない場合は使い方の説明までジャンプしてください。
この記事はこんな方におすすめ
今回の記事は以下の人におすすめの内容です。
- 借入の計算をエクセルでシミュレーションしたい
- 返済シミュレーションサイト以外での計算方法が知りたい
- 借入計算時のエクセル関数の使い方がよく分からない
エクセルで管理すれば借金は返済可能
借金苦になる人の特徴として、そもそも借金を返すのが難しく、借金で借金を返してその場しのぎをするという人が多いです。
また、返済する意思はあるものの、自分の返済能力を把握できておらず、借入と返済能力のバランスが取れていないというパターンもあります。
これでは返済が思うようにすすまないでしょう。
- 元金がいくら減っているのか分からない
- 完済がいつになるのか分からない
- 返済総額がいくらになるのか
上記のような項目も、返済に苦労する主な原因ではないでしょうか。
これらを解決するためには、利息や毎月の返済額を、しっかり計算して管理する必要があります。
借金の項目を計算する上では、エクセルで借金の早見表を作成すると管理が非常に楽です。
事前にエクセル内に、関数や利息などの数字を入力していれば、オートフィルで返済期間などを確認できます。
設定さえできてしまえば、簡単なボタン操作で活用できるツールなので、見ていきましょう。
エクセルの関数を使用して計算
消費者金融や銀行では、公式ホームページ内に返済シミュレーションという名前で毎月の返済額や返済期間、利息を計算できるフォームがあります。
しかし、金額は10万円単位でしか入力できなかったり、利率は決められた値から選択したり、制限されている場合があり、少し使いにくい部分があります。
金融機関に電話で聞けば教えてもらえますが、本人確認が行われるなど、非常に面倒です。
利息がなければ単純な割り算で問題ありませんが、毎月の残高によって利息も変動するため、電卓で計算するのは時間がかかってしまうでしょう。
そこで便利なのがエクセルの関数を利用するという方法です。
エクセルの関数なら金額や金利、返済期間を入力するだけで知りたい情報を表示してくれるため、各社の返済シミュレーションを利用するよりも、知りたい情報を細かく計算することが可能です。
返済額や利息をシミュレーション
借入前や返済中に知っておくべきことは主に以下の4つです。
- 返済期間
- 毎月の返済額
- 利息
- 返済総額
例えば2年間(24か月)で借金を完済するためには毎月いくらずつ返済すれば良いのか、逆に、毎月○○円ずつ返済していくといつ返済が終わるのかといったことを、すぐに調べられれば返済計画は非常に立てやすくなるでしょう。
利用するエクセル内の関数や、数字を入力するときに気を付ける項目など、シミュレーションに必要な知識を確認していきましょう。
毎月の返済額はPMT関数で計算
｢2年後に住宅ローンを組みたいからそれまでにカードローンの返済を終わらせたい｣など、完済時期の目標がある人の場合はそれを元にして毎月の返済額をシミュレーションしなければなりません。
詳しい説明は省略しますがエクセルのPMT関数というものを利用すれば金利、返済回数、借入金額という項目から返済金額を知ることができるのです。
エクセルで入力する項目は下の画像の通り5項目あります。
PPMTとIPMTとISPMTの違い
エクセルで毎月の返済額を計算するときにはPMT関数を用いますが、似たような関数にPPMTやIMPT、ISPMTというものがあります。
これらの関数は借金の返済方法や、知りたい情報によって活用方法が変わります。
例えば、PMT関数は元利均等返済の返済額を計算しますが、PPMTは返済額の中のいくらが返済の元金の金額かを計算可能です。
逆に元利均等返済の利息部分を、計算したい人はIMPTを活用しましょう。
また、住宅ローンなどの返済をするときに元金均等返済を行いたい人は、ISPMT関数を活用すれば計算できます。
それぞれの返済方法や計算したい項目に合わせて、様々な関数を利用してください。
利率は1か月ごとの値
エクセルで金利を入力するときには、年率を入れるのではなく、1返済期間当たりの利率を入力しなければなりません。
例えば、金利が年率12%の場合、返済は1か月ごとですので12%÷12=1%となります。
ですので1%あるいは0.01と入力しましょう。
期間には返済回数を入れる
PMT関数における期間というのは返済回数のことです。
2年間で返済を終えたいのであれば12×2=24となります。
現在価値=借入金額
現在価値は簡単に言えば借入金額と同じものです。
現時点での借入金額もしくは借入を計画している金額を入力してください。
将来価値と支払期日は入力不要
将来価値は特に入力する必要はありません。
将来価値というのは借金をいくらまで減らしたいのかという値ですが、ほとんどの人は借金完済のために計算を行うでしょうから、ここは0となります。
支払期日に1を入力すれば借入した日から返済が始まり、0を入力すれば1か月後から返済が始まるものとして計算されます。 しかし、借入日が一回目の返済日という貸金業者はないでしょうからここも0です。
どちらの欄も、何も入力しなければ自動的に0として判断されるので入力しなくても問題ないのです。
PMT関数にはこれらの値を入力すれば、返済金額が簡単に計算でき、画像の例で行くと毎月4,707円の返済を行えば良いということが簡単に分かります。
返済回数はNPER関数で計算
逆に、毎月2万円までなら返済に回せるという、自分の返済能力がはっきりしている人であればそれを元にしていつになれば返済が終わるのかも確認できますよね。
この場合はNPER関数を使用しましょう。
PMT関数では支払い回数から毎月の返済額を計算していましたが、NPER関数では毎月の返済額を計算してくれます。
このため、定期支払額の欄には金額をマイナスにして入力しましょう。
それ以外の項目はPMT関数の場合と同じ様に入力して問題ありません。
この条件だと22.4257・・・回、小数点以下を切り上げた23回で返済は終了します。
CUMIPMT関数で利息を計算
最終的に利息をいくら支払うかが気になる人は、CUMIPMT関数を活用すれば一発で計算が完了します。
COMIPMTでの入力項目は、これまで紹介してきたPMT関数とほぼ同じですが、支払期日の入力は必ず行わなければなりません。
利息の計算結果はマイナスとして反映されるため、赤字で表示される点には注意しましょう。
計算で気を付けること
PMTなどの関数を利用すれば、計算できますがローンによっては元金と利息以外の返済項目が追加されるので注意してください。
特に住宅ローンは元金と利息以外にも、様々な支払い項目や減額項目があるので注意しなければなりません。
例えば、住宅ローンの返済が始まってから、最初の10年は住宅ローン控除が受けられます。
逆に毎月の利息に信用保証料が別途請求されるケースや、事務手数料の支払いが購入時に請求される可能性がでてきます。
他にも元金据置期間が設定されているため、返済を一定期間、待ってもらえたり、ボーナス併用支払で返済金額が変動するケースもあったりします。 その場合は、計算が変わるため、減税と合わせて注意してください。
借入返済を計算できるエクセル
ここまでエクセルを使って借入の返済内容を計算する方法を解説しましたが、エクセルをふだんから使い慣れていない方にとってはこの作業すら面倒でよく分からないかと思います。
そこで、
- 金利
- 返済回数or返済金額
- 借入金額
この3項目を指定した欄に入力するだけで返済は全部でいくらになるのか、何か月後に借入はいくらまで減るのかを表示できるようにエクセルを作成しました。
こちらからエクセルをダウンロードできますので利用してください。
もしかしたらお使いのパソコンやスマホのセキュリティ状況によっては「ウイルスの危険性があります」や「お使いの危機に悪影響を及ぼす恐れがあります」などの警告がでるかも知れません。
飽くまでもただのエクセルファイルですので安心して使用してください。
使い方
ダウンロードしたファイルを開いてもらえば、左側にこのような欄が表示されます。
この時点では何も数値を入力していませんので、全て空欄になっています。
実際には「毎月の返済額を計算」と上に書いてある通り、金利、返済回数、借入金額を入力すれば、自動的にそれ以外の部分が埋まるように計算式を入れてあります。
実際に入力してみましょう。
返済が可能な条件であれば問題ありませんので、年率18%で借入した10万円を30回(2年半)で返済するという設定で計算してみます。
A3に金利を入力します。
先ほどは年率12%のときは1か月当たり12%÷12=1%なので0.01と入力すると説明しました。 しかし、このファイルでは年率を入力すれば問題ないように設定していますので、そのまま18と入力してください。
次は借入金額です。
ここは特に気にする点はありませんので、借入する金額、現在返済中の人は残高を入力してください。
今回は100,000です。
最後に返済回数の欄に30を入力すればその他の欄が埋まって自動的に返済計画表が作成されます。
このように、年率18%で借入した10万円を30回で返済する場合には毎月4,152円ずつ返済していけば、最終的な返済総額が124,533円、利息は24,533円となることまで分かります。
事前にしっかり計算しておかなければ10万円を30回で返すわけですから、単純に考えて33,000円ずつ返していけばいいと勘違いしてしまいますよね。
それでは完済までに、40か月かかってしまいますので、やはりあらかじめ計算しておくのは大事なことです。
今回は金利、借入金額、返済回数、の順番で入力しました。 しかし、皆さんが実際に使用する際には特に入力の順番を気にする必要はなく、返済回数を最初に入力しても問題はありません。
ただし、毎月返済額、返済総額、利息合計の欄には計算式が入っていますので触らないようにしてください。
返済回数も計算可能
シートの右側の表を使えば、毎月の返済金額から返済回数を計算することもできるようになっています。
先ほど説明した方法と使い方はほとんど同じですが、念のため確認しておきましょう。
今度は少し条件を変えて、金利15%、借入金額は50万円で毎月1万円ずつ返済するという条件に設定して、この三項目を入力します。
先ほどと異なるのは、今回は返済回数を調べるため、返済回数ではなく毎月の返済額を入力するという点だけです。
この条件では返済回数は79回も必要となり、残念ながら画像内に収まりませんでした。
36回(3年間)返済を続けても借入はまだ30万円以上残っており、まだまだ完済までは遠いことがよく分かるのではないでしょうか。
利息が借入の半分以上
返済総額を見てみると781,979円、最終的には借入の半分以上の利息が発生するということです。
特に最初の二年間は支払う金額の半分が利息ですから、これでは元金が全く減らないのも当然ですね。
50万÷1万=50か月くらいで返し終わると甘く考えて借入を行った人からすれば、いつまで経っても返済が終わらず、精神的に追い込まれてしまいます。
借入前に返済期間を計算しておくことも重要なことなのです。
既に借入している人も現在の残高から完済までにどのくらいかかるのかを調べることもできます。
元金均等返済方式と元利均等返済方式
借入をして分割返済するときは、元金均等返済方式と元利均等返済方式という返済方法があります。
聞いたことはあるけど、具体的にはどのような違いがあるのかまでは分からないという人も多いのではないでしょうか。
分かりやすく解説していきますので、ふたつの返済方式の違いをしっかりと把握しておきましょう。
元金均等返済方式とは
元金均等返済方式は、借入金額を均等に分割して、毎回の返済においては元金部分が均等になっている返済のことを言います。
しかし、利息は残高に利率を乗じて計算しているため、返済する残高が変われば利息も変わります。
簡単に言うと元金の返済金額は変わらず、利息を合わせた返済額が毎回変わる返済方式のことです。
毎月返済を行う都合上、利息の金額は減っていくため、最初は返済額が大きいのですが、徐々に少なくなっていくため、だんだんと返済が楽になっていきます。
また、決まった元金が必ず返済されていくため、元利均等返済方式に比べて総支払利息が少なくて済むことがメリットとして挙げられます。
元利均等返済方式とは
元利均等返済方式は借入金の元金部分と利息部分の合計が、毎回同じ金額になるようにして返済をしていきます。
毎回の返済額が決まっていると、家計への負担も一定なので生活費を管理しやすいですよね。
そのため、個人の場合は元利均等返済方式を利用することが一般的です。
なお、総支払利息は元金均等返済よりも多くなることがデメリットと言えるでしょう。
借入前に返済予定表を作って返済シミュレーション！
借金をする前に毎月の返済額を知っておくと、月々の生活の計画を立てやすいのではないでしょうか。
仮の返済予定表を自分で作成するなど、生活に負担にならないように計画をしてください。
計画的にお金を借りるには、いくらずつ返済をすると、いつまで返済が終わるのかを把握しておくことが大事です。
特に、住宅ローンを組むときなどは、多額の借入をして何年にもわたって返済します。
現時点での自分の給料がいくらで、何年後には給料がいくらになるかだけでなく、何年後かには子供が大学に入る年齢になる、自分が定年になるなど、人生には様々なことが起きます。 それを考慮して月々の返済金額と返済期間を決めていかなければなりません。
そして将来の返済計画が順調にすすむということを計算した上で、実際に借入ができるのです。
なお、消費者金融などの公式サイトでは、簡単なシミュレーションができるようになっています。
エクセルなどを利用するのが面倒な人は、シミュレーションを利用するのもいいでしょう。
他にも、銀行から借入返済予定表を作ってもらうこともできます。
返済予定表はどんなことに役立つの？
銀行で借入をするときは返済方法をあらかじめ取り決めて、契約書に記載します。
そして、契約書に基づいて、返済予定表というものが銀行から渡されます。
返済予定表は「借入返済明細表」とも言いますが、毎月の返済日時点での元金と利息の返済額が把握でき、合計してこのぐらいですよという明細が書かれたものです。
そのため、長年にわたって返済する場合は、何ページにもなることがあります。
返済予定表は借入金額や口座番号、返済方法や金利、その他の様々な情報も記載されていて、借入に関する情報が分かるようになっていますので大変便利な表と言えるでしょう。
ローンを組み直すときにも役に立つ
銀行で借入をしてしばらく返済を続けたあとで、他行の金利が低いからという理由で、借り換えることもできます。
金利が安くなるというメリットがある反面、手続きに時間や手間がかかるなどのデメリットもあります。
借入返済は、初めのうちは利息部分の返済が多いので、元利均等返済方式では元金がなかなか減りません。
そのため、途中で返済計画を変更したり、他の銀行に借り変えをしたりして、ローンを組み直すときはこれまでの返済計画表が無駄になってしまうというデメリットもあります。
返済方式によって月々の返済金額や残高も変わってきますので、返済予定表を利用して、借り換えの検討をするのもいいでしょう。
なお、借り換えにはいままで借りていた借金の利息清算と、借り換えにかかる手数料が必要になることがあります。 したがって、これらの費用を含めて、最終的にお得であるのか比較することが大事です。
繰上げ返済をしたときの返済予定表
繰上返済をすることで元金が減るため、繰上げ返済をしたあとの返済予定表は「うち利息」の部分が少なくなります。
自作したエクセルファイルなどでは正確な金額を算出することは難しいかも知れません。 しかし、ボーナス払いをしたときのように繰上返済をした月の金額について操作をすると、返済予定表の金額が修正されます。
繰上返済に回す金額がそれほど高額ではなかったとしても、残りの返済期間の利息が少しずつ減ることになるため、トータルでは思った以上に返済金額が変わります。
返済予定表を作成したら、繰上返済を想定して金額を入力して見ると実感が湧くでしょう。
特に元利均等返済方式の場合は、元金均等返済方式に比べると元金の返済額が少なくなります。
そのため、返済期間の途中で繰り上げ返済をすると、残高を効率的に減らすことができて、総返済額を減らすことができます。
繰上げ返済する金額にもよりますが、その後の返済が非常に楽になりますので、積極的な繰上げ返済を考えましょう。
また、繰上げ返済をすることで、返済期間を短くすることもできますし、元金均等返済の場合には月々の返済金額を安くすることもできます。
なお、繰上げ返済するときは、早いうちにする方がお得です。
早いうちに繰り上げ返済をすると、元金を大きく減らすことになります。
元利均等方式で返済し始めたばかりの時期は、利息の返済ばかりで元金がなかなか減らないと感じてしまいます。
しかし、繰り上げ返済をすると、利息分の支払いが少なくなって総支払額が減るメリットがあります。
なお、繰上げ返済には手数料が必要なこともありますので、事前に金融機関に問い合わせて確認しておくことをおすすめします。
変動金利の場合の返済予定表
金利タイプによって、返済予定表は修正される可能性があるため注意が必要です。
固定金利で借入した場合は金利が頻繁に変動するわけではないので、返済予定表は長年にわたって利用できます。
しかし、借り入れに変動金利を利用した場合は、金利が見直しされた都度に再作成されます。
また、金利が上がれば、月々の元金と金利を合わせた返済金額も変更されることもあります。
固定金利と比べると、変動金利の方が計画を立てにくいことはデメリットと言えるでしょう。
例えば、りそな銀行の住宅ローンを変動金利による返済をした場合は、年2回の金利見直しがあり、5年ごとに125%を上限として返済金額の見直しがあります。
このようなルールは銀行によって異なりますので、変動金利で借入をする場合は、銀行によく確認しておくことが大事です。
そして、借入をするときは金利も重要な問題となります。
特に固定金利にするか変動金利にするかということで、どちらが得かをよく考えましょう。
借入をする時点で変動金利は金利が低くて得だと思っていても、将来的に金利が高くなることもあるのが変動金利です。
そのため、将来の世界情勢や経済状況まで考えて借入しなければなりません。
しかしながら、将来のことはなかなか予想することはできないでしょう。
円とドルのレートや、円とユーロのレートがどうなっていくか、平均株価がどうなっていくかなどを予想するのはプロでも容易ではありません。
そのため、万一に備えて固定金利を選択する人も多いようです。
計算して管理することが重要
エクセルを使って計算するだけではなく、返済の計画を立てるために使用してください。
まだ借入していない人は返済予定時期や返済金額から借入金額を適切な金額に抑えておくようにしましょう。
特に住宅ローンなどの高額な借入れを行うときには、自分の貯金などの資金がいくらあるかが重要ですので、頭金との兼ね合いをよく計算してください。
既に借入を行って返済中の方は現在の返済額で大丈夫なのかを確認し、もし返済がすすまないようであれば返済額を少し増やすなど、完済のために計算することが大切です。
借入の返済予定表に関するQ&A
元利均等返済方式と元金均等返済方式によって返済予定表の作り方が違うことや、役立つ場面などについて様々な角度から、返済予定表を解説してきました。
ここからは、本編では触れることがなかった返済予定表に関係する質問に答えていきます。
簡潔にまとめていきますので、悩みの解決などに役立ててください。
利用することはできません。
返済予定表は飽くまで予定であって、繰上げ返済や金利の変動によって変わってしまう書類ですので、残高証明書としては使うことができないのです。
ローン残高だけでなく、金利や返済方式などを確認できる返済予定表は、必須と言えるでしょう。
金融機関が発行する返済予定表は、個人がシミュレーションのために作成するものとは違い、返済額や返済回数などの作成に必要な項目が決まらなければ作成できません。
つまり、必要事項さえ決まれば、証書貸付ではなくとも返済予定表はもらえると言うことになります。
返済期日を記入し、返済方法を「期日一括返済」としてください。
また、利息に関しても、利息の支払い方法を「貸付日に一括前払」もしくは、「元金返済時に一括後払い」など適宜、記入してください。
借入金は短期借入金や長期借入金として処理します。
また、利息は支払利息として経費の扱いになります。
まとめ
もしエクセルを使わずに借入の計画を立てようとすれば、1か月ごとの利息を計算して、借入残高を算出するという作業を完済まで手作業で行わなければなりません。
エクセルを使って計算する方法は他のページでも紹介していますが、正直自分でエクセルに計算式を入力するのも面倒なのではないでしょうか。
このページに掲載しているエクセルのファイルを使えば数値を入力するだけなので、借り入れの管理に役立ててください。
タグ：お金の知識
※₁お申込み時間や審査状況によりご希望にそえない場合があります。
※お借入れ総額により収入証明書（源泉徴収票等）が必要です。