Excelで住宅ローンの計算
家を買うときに必要なのが資金計画です。家の値段、頭金（自己資金ともいう）の量、金利、ボーナス時の支払いはどうするのかなどなど、いろいろと複雑な計算が必要です。
住宅の販売員は見積書をつくってくれますが、ちょっと時間がかかるので、あれこれ細かな注文はしづらいものです。
そこで、Excelを使って、販売員のつくる見積書と同じものをつくりましょう。
手順
1. 初めてのローン計算
住宅の販売価格を3000万、30年固定金利で年3%、頭金を100万円で考えて見ましょう。下記の表のように数値を入力します。
返済額の欄に122265と表示されましたか？これが2900万借りて30年間固定金利3%で返済するときの月々の返済額になります。
PMT関数は引数に金利、返済回数、借入金の負数をとります。金利は年3%を毎月支払う場合は0.03を12で割ったもの、返済回数は30年x12月になります。
2. ボーナス払いを含める
ボーナスからも決まった額を返すようにした場合の返済額の計算をします。この場合借入金の2900万をボーナス返済分と毎月返済分に分け、計算をします。
住宅のセールスではボーナスの返済額を10万円とか設定しますが、実際には販売員の人が裏でボーナス返済相当額をいろいろいじってそれらしくなるようにしているようです。
毎月返済が105401、ボーナス返済が101574となりましたか。
ボーナスの返済額をきっちり10万にしたい場合は、Excelのゴールシーク機能を使います。
- メニューの「データ」−「What-If分析」から「ゴールシーク」を選択します。
- 数式入力セルにH12(ボーナスの返済額)、目標値に100000、変化させるセルにH8(ボーナスの借入金)を入力してOKをクリックします。
借入金の数値が変化し、3938027になりましたか。
3. 償還表をつくる
お金を借りると償還表なるものがもらえます。これは30年払いだと計360回分の返済と利息、残金の表です。多くの人にとってこれは眺めて、減っていかない残金にため息をつくために存在します。
20-22行に以下のように式をつくります。
その後、22行目を下へコピーします。
回数の数値が361になったとき、残金が0(ほぼ0)になるのがわかります。つまり、360回目の支払いと終わると残金がなくなり、長かったローンの支払いが終わることになります。
ボーナス払い用にも同じものをコピーします。ボーナス用のものは支払いが6ヶ月に1回なので、60回で残金が0になります。
4. 10年後の金利変化を考える
公庫のローンの場合11年目以降の金利が上がります。また30年のローン支払い計画に対し、銀行の20年間固定ローンを組んだ場合など、金利の変動で支払額がどう変化するか知りたくなります。
20年後に金利が上がった場合をシュミレーションしてみます。この場合、借入金を20年後の残高にします。償還表からそれを探すのは大変なので、offset関数で値を引用します。
下の図ではB7からE381をL7にコピーしています。20年後という設定に合わせて、借入先に=OFFSET(B21,20*M11,0)という式を設定しています。20(年後)×M11(年あたりの支払回数)だけ下に移動したセルの値を参照するようにしています。期間を残り年数に変更するのを忘れないように。
支払額が107939になったでしょうか。残金がかなり減っているので、支払額はそれほど多く増えないのがわかります。
5. 支払い総額を計算する
払うものは払わないといけないのであまり気にしてもしかたがないのですが、総額でいくら返すことになるの計算してみます。
2500万円を借り、3%の金利で30年間支払うと総額37944363円の支払い(約5割増し)となるのがわかります。
6. 繰上げ返済を考える
予想以上に給料が増えたとか、宝くじに当たったなど、余剰のお金をローンの繰上げ返済にあてる事態になるかもしれません。しかし、繰上げ返済には手数料がかかたりしてどのぐらい得なのかがわかりにくいと思います。先に作成した償還表を見るとどのぐら得になるのか（利息の支払いを減らすことができるのか）がわかります。
話を簡単にするために借入金2900万全額を銀行の30年間固定ローン(金利3%)で、ボーナス払いなしでローンを組んだとします。この場合毎月の返済額は122265円となります。
5年後100万円貯金ができたとします。このときの償還表は下記のようになります(一部省略しています)。
60回目の支払いが終わった後の残額が25,782,845円です。ここで100万円を繰上げ返済すると残額が24,782,845円となります。おおよそ78回目の残額24,780,206円と等しくなります。
計算を簡便にするために100万円ではなく、1,060,303円を繰上げ返済したとします。するとその後の返済は79回目からとなり、61回目から78回目までの18回分の利息(合計1,137,819円)が節約できたことになります。これが繰り越し返済で節約できた金額です。
7. 元金均等返済
多くの人は毎月の支払いが一定となる元利均等返済を選択します。実はローンの返済方法にはもう一つ元金均等返済という方法があります。こちらは、借入金額を借入期間で割って、それに当該月の利息を加えて返済する方法です。月の支払額が最初は大きいですが、支払総額は少なくなります。
以下のように返済額の式を変更します。
終わりに
上記の式に加えて、もう少し使いやすくしたExcelのファイルをつくりました。よかったら使ってください。
[クリックしてExcelのファイルをダウンロード]
計算結果等に関しては一切責任を持ちませんので、実際の返済プランに関しては銀行などとよく相談してください。
私が三井不動産でとってもらった見積書と比較すると数値はぴったり合いましたが、実際の銀行のローンの計算では数円の単位で異なっていました。おそらく１円未満の端数の処理が異なるためだと思います。本当に家を買うときにはこの辺の数値の誤差より登記やら引越しやらの手数料のほうがべらぼうに高いので気にしなくていいと思います。
毎月の返済額には管理費、修繕積み立て金などが入っていません。実際にはそれらの金額も考慮する必要があると思います。あと毎年固定資産税がかかります。
それではローンの返済がんばってください。