エクセルで住宅ローン期間短縮計算法

このQ&Aのポイント
  • エクセルを使った住宅ローン期間短縮計算の方法について教えてください。
  • 繰り上げ返済額を入力した場合、その影響で返済期間が短縮されるため、短縮期間を自動で計算する方法を知りたいです。
  • 元利均等返済のローンで繰り上げ返済をする場合、どのように期間短縮を計算すれば良いか分かりません。アドバイスをお願いします。
回答を見る
  • ベストアンサー

エクセルによる住宅ローン期間短縮期間計算_2

前回、「エクセルによる住宅ローン期間短縮期間計算」のタイトルで下記質問を投稿させていただきました。情報不足だったので、問題のエクセルシートの画像を添付して再質問いたします。 (1)の箇所に繰り上げ返済額を入れた場合、(2)の箇所以下の返済期間には繰り上げにより期間短縮された期間を引いた値を入れる必要があります。 具体的には、 繰り上げで 1年短縮されたなら20-1=19をいれなければなりません。 この返済期間19年をもとに、K列は毎回返済額を計算する仕組みです。 ところが、(1)に繰り上げ返済額をいれてしまうとI列の元金が変化してしまうために、 入力した繰り上げ返済額では何か月分の期間短縮が可能かを自動で計算させることができません。 図の表はいじらずに、期間短縮したら別の表を作成するというやり方なら、自動でできそうだと 思うのですが、やり方としてはだいぶ力技のような気がしてます。 もうすこしスマートなやり方をご存じの方がいらっしゃいましたら教えていただけますようお願いいたします。 (2)の値を自動で計算し複数回の繰り上げ返済に対応することが目標です。 以上、どうぞよろしくお願いいたします。 ↓前回の質問の内容 元利均等返済、10年目までと、11年目から30年目までの金利が異なります。 9年目あたりで期間短縮型の繰り上げ返済をする場合の期間短縮期間をエクセルで自動計算させたいのですが、次の理由からうまくできてません。  ・繰り上げ返済額をローン残高から引いてしまうと、月毎の返済元金が変わってしまい繰り上げ返済額でどれだけの期間短縮が望めるかを表を基に計算させることができない。このジレンマを脱するには繰り上げ返済をする前の返済表と繰り上げ後の返済表を用意すればできそうだというのはおもいつくのですが、これをやってしまうと複数回の繰り上げをシミュレーションするときに手間だなと思い、ほかの方法を模索してるところです。良きアドバイスをお願いいたします。

質問者が選んだベストアンサー

  • ベストアンサー
  • xexstyle
  • ベストアンサー率72% (72/99)
回答No.1

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

OKGold
質問者

お礼

ありがとうございます。金利変更期間をまたぐ繰り上げ返済については一定の条件を設定しないとシミュレーションできないということですね。よくわかりました。どうもありがとうございました。

関連するQ&A

  • エクセルによる住宅ローン期間短縮期間計算

    元利均等返済、10年目までと、11年目から30年目までの金利が異なります。 9年目あたりで期間短縮型の繰り上げ返済をする場合の期間短縮期間をエクセルで自動計算させたいのですが、次の理由からうまくできてません。  ・繰り上げ返済額をローン残高から引いてしまうと、月毎の返済元金が変わってしまい繰り上げ返済額でどれだけの期間短縮が望めるかを表を基に計算させることができない。このジレンマを脱するには繰り上げ返済をする前の返済表と繰り上げ後の返済表を用意すればできそうだというのはおもいつくのですが、これをやってしまうと複数回の繰り上げをシミュレーションするときに手間だなと思い、ほかの方法を模索してるところです。良きアドバイスをお願いいたします。

  • 住宅ローンの支払い計算

    住宅ローンの支払い計算について質問します。 変動金利にすると返済額は5年間一定で5年後に元金残額、利率と返済残回数で 再度計算されます。 期間短縮型で繰上げ返済したとすると、短縮期間の計算はどうなるのでしょうか? 金利が一定ならば、繰上げ後の残回数はエクセル上で以下で求めれます。 NPER(利率,-返済額,元金残高) 1年後、2年後、3年後と金利がUPした場合、どのような計算式となるのでしょうか? 金利がUPするため、元金残高が一定の時より多くなります。 返済額が変わらないため、残回数が多くなるように思います。 一発で計算できる式は求めていません。 短縮期間をどのように求めているかが知りたいと思っています。 例えば、 月々の返済額が10万とした場合、100万円を繰り上げすると 単純に、10ヶ月分だけ短縮されるのか? それとも、もっと複雑な計算で求められるのか? 元金が減るということは、当然利子も減るのでその分が短縮になると 考えますが、よく分かりません。 ローンの支払いに詳しい方お願い致します。

  • 住宅ローンの繰り上げ返済の短縮期間について

    住宅ローンの繰り上げ返済の短縮期間について教えてください。 3年前に、2900万円、10年固定、1.9%で、 ボーナス併用タイプで返済中です。 期間短縮する場合、いろいろな情報からすると、返済額相当分(返済元金の累計もしくは 返済後残高の差)の月数が短縮されると理解しているのですが、間違っているのでしょうか? 実は、今年4月に、80万円余り繰り上げ返済したのですが、返済計画表からすると、 1年3ヶ月は短縮されるように思われるのですが、実際には1年の短縮でした。 聞いてみると、毎月分とボーナス分を按分してシステムで計算するとそのようになるとのことでした。 他の金融機関もそうなのでしょうか? 教えてください。よろしくお願いします。

  • 住宅ローン・繰上げ返済の計算の仕方

    住宅ローンの組み方を今検討しているところです。 借入1600万 10年固定 2.4% 期間22年 元金均等 11年目以降は変動 繰上げ返済手数料が無料になる関係で、 最初の2年間は「期間短縮型」の繰上げ返済を 行います。(3年目からは返済額変更方式) この場合、まず22年間全期間を「2.4%」で 支払った場合の期間短縮になるのでしょうか。 質問がわかりにくくて申し訳ありません。 要するに、11年目に変動になる分はいつ計算されるの でしょうか。 繰上げ返済のシミュレーションできるサイトなどあれば 教えてください。 (なかなか細かいところまで計算できるソフトがなくて 困っております。) よいアドバイスをお願いいたします。

  • 住宅ローン 期間短縮Or返済額軽減

    先輩方の知恵をお貸しください。 夢の住宅をやっと手に入れ現在ローン返済中です。 ローンの内容は以下のとおりです。 ---------------------------------------- 借入れ期間:20年 借入れ額:1500万 金利:全固定 2.6%(繰上げ返済手数料は無料) ---------------------------------------- 毎月返済額は8万円で早くローンを完済したい為に繰り上げ返済を 期間短縮型で毎月12万円行っています。 その他ボーナスや定期預金の解約等などでも繰り上げ返済を 行った結果、ローン開始から3年目にして借入日からの償還期間が 10年を切る寸前までになっております。 現在のローン残額は700万程度でこのペースで返済すると年末には 500万程度になる予定です。 皆様にお聞きしたいのは今年の住宅ローン減税を受けるか否かです。 来月期間短縮を行うと、住宅ローン減税を受けれなくなるため 選択肢として以下を考えました。 なお、来年はローン減税は考えておりません。  1.今までの期間短縮型で繰り上げ返済を続ける。  2.来月から返済額軽減型の繰り上げ返済に変更する。    (軽減された月々の返済額は繰り上げ返済分に上積み)  3.繰り上げ返済額を貯蓄しておき、来年1月に期間短縮型で繰り上げ返済 上記の案で一番どれが良いでしょうか。また、他に何か良い知恵がありましたらご教示ください。

  • 住宅ローンの繰上返済 期間短縮or返済額減どちらがお得?

    35年長期固定金利の住宅ローンを組んでおり、ローン返済2年目です。 この度繰り上げ返済をするにあたり質問させて下さい。 毎月の返済額を減らしてその分を貯金に回して次回の繰上げ返済に充てるのと(※毎月の返済額が1万円減る計算になります)、毎月の返済額は変えずに期間短縮をするのと、どちらが賢いやり方でしょうか?? また、金利の安い時期に期間短縮をするのが良いとどこかで見ましたが、それは変動金利で借り入れしている場合ですよね?

  • 期間短縮型と返済額軽減型

    繰り上げ返済には期間短縮型と返済額軽減型があり、 総返済額から考えれば、期間短縮型が得だと思いますが 例えば、一年に一度繰り上げ返済するとして はじめは返済額軽減型を選び、その後の繰上げ返済の時には 期間短縮型を選んだりと その都度返済方法を選択するのは可能なのでしょうか? 2つのローン(例えばフラットと財形)を組み合わせて 借りようと思っていて 財形は700万ほどを25年で借り、毎月の返済額を抑えるために はじめは返済額軽減型を選択しようと思っています。 25年というのは根拠はないのですが、はじめから小額のローンを 長期で借りないほうが得かな?と思っています。 毎月の返済額にもっとゆとりができれば、その後期間短縮を選ぼうと 思っています。 ちなみに、繰上げ返済のシュミレーションで 何回か分の予定表みたいななものが作れるサイトがありましたら 教えていただきたいのです。 よろしくお願い致します。

  • 住宅ローンの返済期間短縮について

    みずほ銀行の住宅ローンを利用しています。 35年ローンの変動金利。 残り25年ですが、10年短縮してあと15年で完済したいと考えています。 みずほダイレクトから手続きは可能でしょうか。 また、手続きに際して手数料は発生しますか。 色々検索しましたが、繰り上げ返済時の返済期間短縮方式しかヒットしません。 ※返済期間短縮はオススメしない等のコメントはご遠慮下さい。

  • 住宅ローン(変動)繰上げ返済 納得いかない・・・

    昨年の9月に住宅ローンを契約しました。 (変動金利・返済額5年毎見直し方式) 1800万円を30年で返済予定で、現在、毎月返済額が約6万円です。 期間短縮型の繰上げ返済を100万円行いたいと思い、貸主の銀行に試算を依頼したところ、10ヶ月の期間短縮になるとのことでした。 毎月6万を10ヶ月短縮しても60万円・・・100万円の繰上げ返済に対してそれではおかしいのではないか?と問い返しました。 最初、「機械で計算したらそうなりました」の一点張りでしたが、当然そのような回答では納得いかず、どう計算したのか説明を要求したところ・・・(返答に半日かかりましたが) 「10月に金利が上がったので、未払いの利息が発生しており、繰上げ返済してもまずその分を清算してから元金返済に当てるので、思ったより期間が短縮されないのです」 と言われました。 ・・・変動の繰上げ返済って、そういうものなのでしょうか?ご教示下さい・・・ ちなみに 100万のうちいくらが未払い清算に当てられ、いくらが元金返済に充てられるのか?と尋ねると 「機械ですべて計算した結果なので明細までは分かりません」 との回答でした。この銀行大丈夫ですかね・・?

  • 繰上返済による借入期間の短縮(住宅ローン)

    今年の2月よりフラット35にて2,000万円借入。金利は2.7%。ボーナスでの返済は200万円。35年で契約しました。 冬のボーナスが出たので1月に100万円繰上返済(期間短縮を選択)を申請すると、1,103,101円の繰上償還元金となり、短縮月数は36ヶ月となりました。 この先10年同様に約100万円ずつ繰上返済していくと何年で完済になるのでしょうか?

専門家に質問してみよう