- ベストアンサー
エクセルで住宅ローン期間短縮計算法
- エクセルを使った住宅ローン期間短縮計算の方法について教えてください。
- 繰り上げ返済額を入力した場合、その影響で返済期間が短縮されるため、短縮期間を自動で計算する方法を知りたいです。
- 元利均等返済のローンで繰り上げ返済をする場合、どのように期間短縮を計算すれば良いか分かりません。アドバイスをお願いします。
- OKGold
- お礼率83% (10/12)
- その他MS Office製品
- 回答数1
- ありがとう数1
- みんなの回答 （1）
- 専門家の回答
質問者が選んだベストアンサー
「月毎の返済元金が変わってしまい」とは、毎月の返済に含まれる元金充当分が変わってしまうということでしょうか？ そうだとしたら多少、誤解されていると思います。 返済額中に含まれる元金充当分は繰上げ返済がなくても毎月変わります。 最初、その割合は低く、返済が進むにつれて、その割合は上がってゆきます。 なぜならば、利息はその時点での元金残高に月利（年利の12分の1）をかけて算出しますが、元金残高は毎月減ってゆくので、利息もそれに応じて減ってゆきます。 利息が減りますので、返済額が一定であれば、元金充当分は増えてゆきます。 繰上げ返済すると、元金残高がいっきに減りますので、それ後の元金充当分の割合も大きく増えます。 具体的に元金充当分は、(1+月利)倍で毎月増えてゆきます。 それはさておき、一定期間以降に金利が変更となる住宅ローンで、金利変更前に期間短縮型で繰上げ返済した場合、どれくらい期間が短縮されるかは、金融機関とその部分においてどのような条件で契約しているかが分からないと計算ができません。 短縮できる期間は、金利、元本残高、毎月の返済額が決まっていないと算出できません。 またその際に必要な毎月の返済額は、金利、元本残高、残期間が決まっていないと計算できません。 現状、質問者さんが想定している金利変更後の毎月の返済額は、繰上げ返済していない場合の元本残高を元に、変更後の金利と残期間（20年）で計算しているのではないでしょうか？ でも金利変更前に繰上げ返済すると元本残高が減りますので、その返済額は使えないのではないかと思います。 かといって繰上げ返済した場合の元本残高で、残期間20年として、変更後の金利で毎月の返済額を計算してしまっては、期間短縮型ではなく、返済額軽減型になってしまいます。 元利均等返済のローンでは、『借入額』、『金利』、『返済期間』、『毎月の返済額』の４つの項目のうち３っつが確定すると、残りの一つの項目が確定する関係なのですが、ご質問のケースの場合、金利変更前に返済繰上げ返済してしまうと、金利変更時に借入額（元本残高）と金利が決まっても、返済期間と毎月の返済額のどちらも決まらない、決められない状況なってしまうのです。 返済額軽減型で繰上げ返済するのであるならば、返済期間は残り20年と確定できるので、返済額も計算できると思いますが、ご質問のケースのように金利変更前に期間短縮型で繰上げ返済した場合は、金融機関側でどう取り扱うのか、どのような契約になっているのか聞いてみないと分からないと思います。 この部分は金融機関でどう処理するのか私も以前から疑問に思っているところです。 仮の計算として、繰上げ返済無しの場合の金利変更後の毎月の返済額を使って、短縮期間を計算するのは比較的簡単です。 Excelには返済回数を計算してくれるNPERという関数が用意されています。 使い方は、 =NPER(利率, 定期支払額, 現在価値, 将来価値<省略可>, 支払期日<省略可>) です。 利率には月利（年利の12分の1）を、定期支払額には先頭にマイナスの符号を付けて毎月の返済額を、現在価値には借入額（元本残高）を入れます。 仮に年利2％、定期支払額101,177円、元本残高2000万円の場合は次のようになります。 =NPER(0.02/12, -101177,20000000) これを使って、現在価値のところに繰上げ返済後の元本残高を入れれば、その場合の返済回数が求まりますので、繰上げ返済なしの場合の回数から引けば、短縮回数が求まります。 ※Excelの画面を拡大されてもまだよく見えませんね。
お礼
ありがとうございます。金利変更期間をまたぐ繰り上げ返済については一定の条件を設定しないとシミュレーションできないということですね。よくわかりました。どうもありがとうございました。