住宅ローンの返済額の計算について。うまくできません。

質問

住宅ローンの算定については、この「教えて」の中にもありましたが、変動金利の場合の算定について教えてください。
例えば、借入額　2000万円　35年償還　元利均等償還　利率1年～3年1.0％・3年超10年まで1.5％・10年超2.0％とした場合の算定についてエクセルで教えていただけませんか。
利率が変動した翌年からの総返済回数と未償還元金を変えれば良いと思ったのですが残高・償還年数がうまくあいません。よろしくお願いします。

回答No.2

walkingdic · ベストアンサー

変動金利の場合のルールをまず知る必要があります。
まず適用される金利は半年単位で変更されます。返済額は５年毎に見直されます。またこの時の変更幅は２５％以内というルールもあります。

その条件を入れて計算する場合、

まず約定時の条件から、

返済額：５６，４５７円／月（=-PMT(金利/12,12*35,ローン金額)

が求まります。現実には小数点の問題が発生するため、多少の誤差は生じます。

３年間は金利一定の条件から、３年後の元金金額は、

元金返済額：-CUMPRINC(金利/12,12*35,元金金額,1,12*3,0) = \1,453,546

ローン残高：２０００万－元金残高 = \18,546,454

とりなります。
３年経過後から５年までは、返済額同一、金利のみ異なりますので、この期間は返済年数がそれだけ延びたとして計算するとつじつまがあいます。
まず、返済年数は、NPER(金利:1.5%/12,返済額５６４５７,-\18,546,454,0)＝４２３．２ヶ月とでてきますので、それを使って３～５年間にどれだけ元金を返済したのかを -CUMPRINC(1.5%/12,上記423.2ヶ月,元金金額,1,12*2,0) = 810722.8161　で求めて、５年後の元金額を出すと、\18,546,454 - 810,723 = \17,735,731 になります。

次に５年から１０年の期間については返済金額が変更となりますから、単純に、

-pmt(1.5%/12, 30*12, \17,735,731) = \61,210

が毎月返済額であり以前の返済額の２５％以内の増額だからリミットにかかることなくそのままです。
１０年後の元金返済額金額は、-CUMPRINC(1.5%/12,12*30,\17,735,731,1,12*5,0) =2,430,897だから、\15,304,835となります。
あとは金利一定なので単純に、残り返済年数２５年、金利２％で元金金額は上記の通り \15,304,835 なので計算出るでしょう。１０年以降もご質問の条件では変動金利の返済上限にかかることはないのでそのままです。（上限にかかると未収金利が発生しているということです）

以上ですが、書いていて疑問に思ったのは当初３年の金利が変動にしてはかなり安いですよね。
もし短期固定であれば話は違い、固定期間終了時点で返済額は変更されますので上記とは異なりますよ。
あと多分ご質問にある金利の設定はあくまでたとえだと思いますけど、１０年以降２．０％というのはきわめて楽観的過ぎるといえる設定なのでちょっと気になりました。

お礼

丁寧な回答本当にありがとうございました。
ご指摘のように、エクセルで計算化するために設定した、あくまでも仮の金利であって、実際の金利とは全く別のものです。
懇切に記入いただきありがとうございました。又お礼が遅くなり失礼しました。

回答No.1

gamigami

段階金利の計算は元本がいくらか確定しないと計算できません。
初年度　＝ＰＭＴ（金利/12,返済回数(年数×12),元金,０）
実際に　＝ＰＭＴ（1％/12,35*12,-20000000,0)
４年目以降は金利と返済年数がそのまま変えれば使えます。
元金のところにＦＶ関数を入れてやれば残債額が算出できますので、
＝ＰＭＴ(1.5%/12,(35-3)*12,=FV(1%/12,3*12,[定期支払額は先のＰＭＴ関数の数字],-20000000),0)
とすれば、４年目からの返済額が出ます。
１１年目のものも同じで、ＦＶ関数で元金が算出できれば可能です。
※ただし変動金利のお約束に即した計算は半年毎の金利変動と5年間の返済額固定の条件があり、エクセルでのシミュレートは不可能に近いです。
３年固定、のち７年固定と読み替えてもらうほうが良いですね。

お礼

なかなか難しいようですね。私も色々やってみましたが、うまくいかず質問させていただきました。
ご指摘のような方法でまたくんでみます。
ありがとうございました。お礼が遅くなり申し訳ありませんでした。