住宅ローンを借りるとき、毎月の返済額の試算はどうされていますか？
金額をちょっと変えて、返済回数をちょっと変えて、金利をちょっと変えて、、、、など色々試しに計算してみたくなると思います。
３５年ローンとかを組んだら、総額いくら返済することになるのか？とか、変動金利で借りた場合、金利が変わったら返済額はいくらになるのか？
とか気になりますよね？
金融機関のホームページなどにも返済額のシミュレーションページなどあったりしますので、普通はそういったものを使われると思います。
でも、あれだと自由な設定とかができなかったりして、もう少し自分で修正して自由に計算できたらいいのにと思いませんか？
実はエクセルを使えば簡単に住宅ローンの返済額の計算ができてしまうんです。
以前、エクセルを使った簡単な計算方法をご紹介しました。
こちらの記事です⇒住宅ローンの月々の返済額計算方法。エクセルを使って利息も計算。
前回のは初級編でしたが、今回は少し上級編です。（中級くらい、という感じでしょうか^^）
住宅ローンの変動金利型の月々の返済額の計算方法
エクセルって本当に色々な関数があって、しかも、シート自体がかなり大きなものも作れてしまうので、実はすごく複雑な計算ができるんです。
前回は単純に、金利も返済回数も固定した状態で、返済額がいくらになるのか計算してみましたが、エクセルの能力を使えば、もっと複雑な条件設定でローンの返済額を計算することが出来るんです。
金融機関も、不動産会社も、面倒なのでローンの返済額に関して細かい計算方法とか注意点は教えてくれません。特に不動産会社の担当は、不動産を売ってなんぼですから、住宅購入者が本当にローンを返済することが可能なのか、なんて考えてませんから注意してください！
変動金利の住宅ローンは金利が上がったら、返済額も思った以上に増加するので注意が必要です。
自分の身は自分で守りましょう。
変動金利の住宅ローン返済額を金利を変えてエクセルで計算するには
住宅ローンの変動金利とは？
住宅ローンは大きく分けると固定金利型と変動金利型の２種類がありますよね。
一般的には変動金利の住宅ローンのほうが金利が低いので返済額も少なくなります。不動産会社の営業担当者はこちらをすすめてくるはずです。
（何故なら当面の返済額が少ない方が借りやすくなるからです）。
でも！
変動金利住宅ローンというのは、金利が上昇したときには返済額が増えるリスクがあるので注意が必要です！
固定金利というのは、最終回まで金利が変わらない、というもので分かりやすいと思います。
では、変動金利ってなんなのでしょうか？
金融市場には金利の市場というものがありまして、１日物の金利（オーバーナイト金利）から３０年くらいまでの金利が取引されています。
今は、金利が凄く低い状態が長い間続いているので（異常な状態）、金利が上がったらどうなるのか、とかを心配する人は少ないかもしれません。
金融機関で市場関連の仕事をしている人以外、金利の動向なんてほとんど気にすることはないかもしれませんが、金利は本来景気動向や日本銀行の金融政策などによって上がったり下がったりするので注意が必要なのです。
事実、アメリカでは景気の過熱感から中央銀行が政策金利を動かして対応しており、かなりの幅で金利は動いています。
変動金利の住宅ローンは、６か月の金利や短期プライムレートと呼ばれる短期金利に連動している商品が多く、短期金利というのは日本銀行の政策によって直接動かされます。
ですので、足元の変動金利の住宅ローンの方が固定金利住宅ローンより低いのを見て、「変動金利の方が得だ！」と思うと、期間の長い住宅ローンでは非常に危険と言えます。
住宅ローン 変動金利の返済額を金利を変えて計算するには
では、エクセルを使って変動金利住宅ローンが返済期間中に金利が上がったらどのくらい返済額が変わるのか計算してみたいと思います。
まずは、これが完成形です。黄色い部分を変化させると、E列の返済額が変化するように作ってあります。
デザインはこだわってないので、出来上がったらかっこよく色付けたりしてください！^^
では、順を追ってご説明します。
元利均等返済額の計算方法
住宅ローンの返済額は、「元利均等返済方式」という、返済額を定額にする計算式が使われます。これを計算するには手（電卓）でやろうとすると結構複雑な計算になってしまうのですが、エクセルでやると一発で計算することが出来ます。
それは、エクセルのPMT関数というのを使います。
これを使うと、元利均等返済額の計算が簡単にできるんです。
PMT関数の計算に必要となる項目というのは、
１．ローン残高
２．残りの返済回数
３．金利
の３項目です。
この前提を頭に入れたうえで、エクセルで計算シートを作っていけば、途中で金利を変えながら返済額を計算させる表を作ることが出来ます。以下ご説明します。
返済回数と残返済回数の算出
まず、B列の「回数」、これはベタ打ちです。何回目の返済なのかを指していまして、ここでは０~４２０まで入力しています（３５年ローンなので）。
その隣の「残回数」のC列、ここが重要で、あと何回返済が残っているかを計算して出しています。この数字がＰＭＴ関数を使う上で必要になります。計算式は、I１セル（＄で囲って固定します）の「返済回数」からB列の「回数」を引き算して出しています。
金利の設定
その隣のD列の金利の列、ここは黄色に塗っていますが、手で入力するようにしています。とりあえず一旦全部”１％”と入力して作っていますが、全部出来上がったら、自由に変えて計算できます。
毎月の返済額の計算
E列が返済額の計算ですが、ここでPMT関数を使います。
①利率 ：同じ行の金利の列のセルを指定（ここでいうとD７）して、１２で割ります（金利は年間の金利なので計算上は月利に直すため）
②期間 ：ひとつ上の行の「残回数」のセルを指定します。（ここでいうと、C6のセル）
③現在価値：ひとつ上の行の「ローン残高」のセルを指定します（ここでいうと、I６のセル）
そして、PMT関数で計算して出た値は、マイナスの値になるため、式全体の前に「－」を入れて、正の値に直します。
この計算で算出された値が、「毎月の返済額」になります。この毎月の返済額は、全部がローンの返済額になるわけではなく、内訳としては、「金利」「元本」に分けられます。
金利は、借入銀行へ支払う「利息」であり、ローンの返済に充てられるのが「元本」です。
つまり毎月の返済額というのは、金融機関へ支払う利息と、借金返済に充てられる元本返済の合計額ということになるのです。
では、以下、毎月の返済額の内訳の計算方法をしながら、ローン残高の推移を計算していく方法をご説明します。
返済額のうち利息額の計算
F列で返済額のうち、利息額がいくらなのかを計算して算出します。
計算式は、
一つ上の行のI列「ローン残高」（ここでいうと、I６のセル）×同じ行の金利（D７）
÷１２です。
これで、当月の返済額のうち、利息の金額を算出することが出来ます。
上記の例でいうと、１回目の返済額（金利１%で計算）98,800円のうち、29,167円が利息ということになります。たったの１％の金利なのに、金利は3万円近く払う、ということなんです。
返済額のうち元本額の計算
次に、毎月の返済額のうち、元本返済の金額の計算です。これは、毎月の返済額から上記で計算して出した利息の額を引いた値になります。
計算式としては、同じ行の返済額（E7のセル）－同じ行の利息の額（F７のセル）で、上記の例の金額としては（１％の金利で計算）、１回目の返済額のうち、元本額は69,633円になりますね。
毎月のローン残高推移の計算
次に、元本を返済した後の、ローン残高を計算していきます。（I列です）
まず、スタート時点の「回数」ゼロ時点の残高である「I６」のセルは、I１のセルを指定します。（I１のセルの値は、自由に変えられます。ここでは420回しています。）
そして、「回数」1回目の返済後、以後の残高推移の計算が、I7以降のセルになりまして、Ｉ７のセルの計算式としては、「I６－Ｇ７－Ｈ７」になります。
Ｈ列は、繰り上げ弁済をしたときに返済額がどうなるか計算できるように設定したもので、金額を自由に入れることが出来ます（入れなくても可）。
上記の通りに式を入力し終わったら、7行目のセルの式を、420回分コピペして表を完成させてください。（その際、Ｂ列だけは計算式ではなく、０~４２０までベタ打ちなので、注意してください）
上記の例では最初からすでに４２０回分入力した状態でご説明しましたが、作るときは最初と次の行（この例では「６」と「７」の行）だけ作って最後にコピペして表を完成させました。
表のチェック 正しく作れているかの確認
一通り作り終わったら、420回返済し終わった最後のところで、ローン残高がきちんとゼロになっているかを確認してください。端数の誤差が生じるため、「－０」と表示されることもありますが、これはOKです。
ちなみに、Ｉ１の「返済回数」を例えば、「360」（30年ローンですね）など、４２０以下の数字を入力しても計算できますが、この場合、３６０回目で残高がゼロになり、３６１回目以降はマイナス表示になります。３６１回目以降は無視してください。
毎月の金利を変えて計算してみる
最後に、Ｄ列の金利を変えた時に、返済額がどのように変わるか試してみました。
１%の時は、返済額が98,800円、
２％に上がると、115,702円
３％に上がると、133,937円
と変化します。結構大きく変わることが分かると思います。
またこのシートでは、返済回数の「Ｉ１」のセルと、金額の「Ｉ２」のセルも自由に変えることが出来ますので色々試して返済額を試算することが出来ます。
まとめ
いかがでしょうか？
実際の住宅ローンは、金利が上がってもすぐ返済額を変えずしわ寄せを後ろにもっていったりするなど、もっと複雑な計算をして返済額を算出する商品があったりしますが基本的な考え方や計算は上記のとおりです。
こうやって計算してみると、金利が１％あがっただけで随分返済額が増えるのが分かると思います。
今の環境ですと、金利が１%あがる、なんてありえないよ！と思うかもしれませんが、金利というのはいったん動き出すと、相当大きく動きます。
特に、長年異常な低金利状態を無理して続けてきた日本の場合、一度金利が上昇し始めるとかなりの変化が起こる可能性もあるのではないか、と非常に心配されるところです。
住宅ローンは長い期間にわたって返済していくものですので、足元の返済額だけにとらわれず、よくよくご自分の将来の収入見込みなど考慮に入れて、返済計画を立てることをおすすめします。
返済計画にかなり余裕があり、いざとなったら全額早く返済してしまう！という方であれば、足元の金利が低い変動金利はおススメだと思います。
ですが、返済額にそれほど余裕がない資金計画の方の場合は、金利上昇時に返済額が増えてしまうリスクはなるべく取らない、固定金利の住宅ローンがおススメです。
以上ご参考になれば幸いです。
ありがとうございました。