教えて!住まいの先生

Q 住宅ローンの計算式を教えてください

住宅ローンシュミレーションをエクセルの計算式にしたく試行錯誤しております。
フラット35と変動の2種類で借り入れ予定です。

35年420回払なので、
420行に毎月の返済額、利息分、元金などを入れて管理したいのです。

また、変動も借りるので
金利を420セルすべてに設定入力できるようにしておき、変動した際に書き換える方法にしたいです。


①フラット35S
借入額 2500万円
420回(35年)
政府の金利優遇措置により
0年~10年目1.55%
11年~20年目2.25%
21年目以降2.55%

=PMT(年金利/12ヶ月,420回,-2500万円,0)

とすると、0~10年目の返済額77160円/月とできたのですが

この式にて途中で2.25%となり、2.55%となり、エクセルで可変していくと、420回を待たずして完済するようになってしまいます。。。
きっと、全期固定の場合の計算式なんでしょうね。


②変動金利
借入額 1900万円
420回(35年)
現金利1.47%




どなたか計算式を教えてください。
宜しくお願い致します。
質問日時: 2011/2/28 14:08:46 解決済み 解決日時: 2011/2/28 16:27:13
回答数: 2 閲覧数: 12709 お礼: 100枚
共感した: 0 この質問が不快なら

ベストアンサーに選ばれた回答

A 回答日時: 2011/2/28 16:27:13
PMT関数の形式は、
PMT(返済1回当たりの利率、残りの返済回数、-未返済の元金、将来価値<省略可>, 返済タイミング<省略可>)
となります。

これより、最初の10年間の返済額の計算はご提示のとおり、
=PMT(0.0155/12,420,-25000000)
でいいと思います。

10年経ったところで金利が変わったら、次のとおりになります。
=PMT(0.0225/12,420-120,-10年返済完了時の未返済の元金)
となります。
ここで、”10年返済完了時の未返済の元金”をどう求めるかですが、s0k0y0zさんのExcelのシートのフォーマットが分からないのですが、もしその中に元金の未返済額を算出する列があるのでしたら、そこの10年(120回)分返済完了時の元金未返済額を使えばいいですし、あるいは、
=CUMPRINC(0.0155/12, 420, 25000000, 1, 120,0)
で、返済1回目から、120回目まで元金返済累計を求めることができますので、2500万円からその額を引けば(マイナスの値で返ってくるので、実際には足してしてください)元金の未返済額が求まります。
その額は、だいたい19,180,174円程度となり、毎月の返済額は、83,651円程度だと思います。

同じように20年経ち、再度金利が変わったら、次のとおりになります。
=PMT(0.0255/12,420-120-120,-20年返済完了時の未返済の元金)
となります。
”20年返済完了時の未返済の元金”は、
=19180174+CUMPRINC(0.0225/12, 420-120, 19180174, 1, 120,0)
で求めることができます。
その額は、だいたい12,769,433円程度で、毎月の返済額は85,446円程度になると思います。

これらの金額で計算すると420回で返済完了すると思います。

変動金利についても同じ考えで、金利が変わるたびにその時点での残りの返済回数と元金未返済額とで計算してください。


そのうち、ボーナス返済も含めた返済のシミュレーションとかもしたくなると思います。
それも説明すると長くなってしまうので、別の機会にまたご質問ください。
自分でExcelシートの返済予定表を作ったら、繰り上げ返済時の効果とかシミュレーションできて楽しいですよね。
  • なるほど:2
  • そうだね:0
  • ありがとう:0

この回答が不快なら

質問した人からのコメント

回答日時: 2011/2/28 16:27:13

金利が変わる際に、回数も残回数に変更していいのですね。
そこらへんの認識が違っていました。

フラット35なのに、金利が変わるので、なかなかネット上で回答をみつける事ができずにいました。

やっと解決して、我が家のプランシートが出来上がりそうです。
ありがとうございました。

回答

1件を表示しています。

  1. 前へ
  2. 1
  3. 次へ
A 回答日時: 2011/2/28 14:37:06
シミュレーションサイトをご利用になったほうが早いのではないでしょうか?
http://www.hownes.com/loan/sim/
実際に借りた際には償還明細表というのが送られてきます。
家計簿ソフトでもシミュレーションは可能です。
私はMICROSOFT MONEYというのを使っています。
繰り上げ返済などを実行するとまた計算が違ってきますのでこういったサイトかソフトをお使いになることをオススメします。
  • なるほど:0
  • そうだね:0
  • ありがとう:0

この質問が不快なら

1件を表示しています。

  1. 前へ
  2. 1
  3. 次へ

Yahoo!不動産で住まいを探そう!

関連する物件をYahoo!不動産で探す

売る

家を売りたい!と思ったら

不動産会社に無料で査定依頼ができます。

知る

Yahoo!不動産マンションカタログ

マンションのスペック情報だけではなく、住んでいるからこそわかる、クチコミ情報を提供しています。
たくさんのマンションの中から、失敗のない「理想の住み替え先」がきっと見つかります。

JavaScript license information