家を買うことにしました。 で、必要なのが資金計画です。 家の値段、頭金（自己資金ともいう）の量、金利、ボーナス時の支払いはどうするのか。 いろいろ複雑な計算が必要です。 住宅の販売員は見積書をつくってくれますが、ちょっと時間がかかるので、あれこれ細かな注文はしづらいものです。 そこで、Excelを使って、販売員のつくる見積書と同じものをつくりましょう。
住宅の販売価格を3000万、30年固定金利で年3%、頭金を100万円で考えて見ましょう。下記の表のように数値を入力します。
| 1 | 2 | |
|---|---|---|
| 1 | 購入費 | 29000000 |
| 2 | 頭金 | 1000000 |
| 3 | 借入金 | =r1c2-r2c2 |
| 4 | 期間 | 30 |
| 5 | 金利 | 0.03 |
| 6 | 返済額 | =PMT(r5c2/12,r4c2*12,-r3c2) |
返済額の欄に122265と表示されましたか？これが2900万借りて30年間固定金利3%で返済するときの月々の返済額になります。
PMT関数は引数に金利、返済回数、借入金の負数をとります。金利は年3%を毎月支払う場合は0.03を12で割ったもの、返済回数は30年x12月になります。
ボーナスからも決まった額を返すようにした場合の返済額の計算をします。この場合借入金の2900万をボーナス返済分と毎月返済分に分け、計算をします。
住宅のセールスではボーナスの返済額を10万円とか設定しますが、実際には販売員の人が裏でボーナス返済相当額をいろいろいじってそれらしくなるようにしているようです。
| 1 | 2 | 3 | |
|---|---|---|---|
| 1 | 購入費 | 29000000 | |
| 2 | 頭金 | 1000000 | |
| 3 | 借入金 | =r1c2-r2c2 | |
| 4 | 期間 | 30 | |
| 5 | 金利 | 0.03 | |
| 6 | |||
| 7 | 借入金 | 返済額 | |
| 8 | 毎月返済 | =r3c2-r[1]c | =PMT(r5c2/12,r4c2*12,-rc[-1]) |
| 9 | ボーナス返済 | 4000000 | =PMT(r5c2/2,r4c2*2,-rc[-1]) |
毎月返済が105401、ボーナス返済が101574となりましたか。
ボーナスの返済額をきっちり10万にしたい場合は、Excelのゴールシーク機能を使います。
借入金の数値が変化し、3938027になりましたか。
銀行と住宅金融公庫の両方から借りる場合を考えてみます。この場合の計算は借り入れ場所をさらに増やすことで対応します（毎月とボーナス返済をそれぞれ銀行と公庫で行うとします）。銀行の金利を3%とし、公庫の金利を2.5%とします。
借入金の設定を銀行と公庫のボーナスに対して設定します。返済額が希望のものとなるようにそれぞれの値を調整します(必要に応じてゴールシークを使用してください)。公庫の場合は融資の上限があるので、それを2400万として設定します。
| 1 | 2 | 3 | |
|---|---|---|---|
| 1 | 購入費 | 30000000 | |
| 2 | 頭金 | 1000000 | |
| 3 | 借入金 | =r1c2-r2c2 | |
| 4 | 期間 | 30 | |
| 5 | |||
| 6 | 銀行 | 金利 | 0.03 |
| 7 | 借入金 | 返済額 | |
| 8 | 総額 | =r3c-r14c | |
| 9 | 毎月返済 | =r[-1]c-r[1]c | =PMT(r[-3]c/12,r4c2*12,-rc[-1]) |
| 10 | ボーナス返済 | 500000 | =PMT(r[-4]c/2,r4c2*2,-rc[-1]) |
| 11 | |||
| 12 | 公庫 | 金利 | 0.025 |
| 13 | 借入金 | 返済額 | |
| 14 | 総額 | 24000000 | |
| 15 | 毎月返済 | =r[-1]c-r[1]c | =PMT(r[-3]c/12,r4c2*12,-rc[-1]) |
| 16 | ボーナス返済 | 4000000 | =PMT(r[-4]c/2,r4c2*2,-rc[-1]) |
お金を借りると償還表なるものがもらえます。 これは30年払いだと計360回分の返済と利息、残金の表です。 多くの人にとってこれは眺めて、減っていかない残金にため息をつくために存在します。
| 1 | 2 | 3 | 4 | 5 | |
|---|---|---|---|---|---|
| 28 | 償還表 | ||||
| 29 | 銀行毎月 | ||||
| 30 | 回数 | 残金 | 金利(%) | 利子 | 返済額 |
| 31 | 1 | =R9C2 | =R6C3 | =RC[-2]*RC[-1]/12 | =R9C3 |
| 32 | =r[-1]c+1 | =R[-1]C-R[-1]C[3]+R[-1]C[2] | =R[-1]C | =RC[-2]*RC[-1]/12 | =R[-1]C |
32行目を下へコピーします。 回数の数値が361になったとき、残金が０(ほぼ０)になるのがわかります。 つまり、360回目の支払いと終わると残金がなくなり、長かったローンの支払いが終わることになります。 続いてボーナス払いようのものを作成します。
| 6 | 7 | 8 | 9 | |
|---|---|---|---|---|
| 29 | 銀行ボーナス | |||
| 30 | 残金 | 金利(%) | 利子 | 返済額 |
| 31 | =R10C2 | =R6C3 | =RC[-2]*RC[-1]/12 | =R10C3 |
| 37 | =R[-6]C-R[-6]C[3]+R[-6]C[2] | =R[-6]C | =RC[-2]*RC[-1]/2 | =R[-6]C |
ボーナス用のものは支払いが6ヶ月に一回になっている点に注意してください。コピーするときには6行ずつにします。
作成した償還表を公庫分としてもう一つ作成します。
公庫のローンの場合11年目以降の金利が上がります。 また30年のローン支払い計画に対し、銀行の20年間固定ローンを組んだ場合など、金利の変動で支払額がどう変化するか知りたくなります。 それをシュミレーションしてみます。
公庫の11年目以降の金利を2.8%とします。 この場合、10年後の残高に対してPMT関数を使用し、支払額を計算します。
| 4 | 5 | |
|---|---|---|
| 11 | 10 | 年後 |
| 12 | 金利 | 0.028 |
| 13 | 借入金 | 返済額 |
| 14 | ||
| 15 | =INDIRECT(CONCATENATE("R",R[-4]C*12+31,"C2"),FALSE) | =PMT(R[-3]C/12,(R4C2-R[-4]C[-1])*12,-RC[-1]) |
| 16 | =INDIRECT(CONCATENATE("R",R[-5]C*12+31,"C7"),FALSE) | =PMT(R[-4]C/2,(R4C2-R[-5]C[-1])*2,-RC[-1]) |
10年後の残金を下の償還表から引用します。このとき、10年という数値が変わるので、indirect関数を使用して参照するセルを指定する点がポイントです。私はRC形式が好きなのでindirectの二つ目の引数がfalseですが、A1形式を使用する場合はtrueになります。もちろんその場合はconcatenate関数の引数を調整し、参照するセルをA1方式で指定します。
払うものは払わないといけないのであまり気にしてもしかたがないのですが、総額でいくら銀行や公庫に返すことになるのでしょうか。
| 6 | |
|---|---|
| 7 | 返済総額 |
| 9 | =(RC[-3]*R[-4]C[-2]+RC[-1]*(R4C2-R[-4]C[-2]))*12 |
| 10 | =(RC[-3]*R[-5]C[-2]+RC[-1]*(R4C2-R[-5]C[-2]))*2 |
| 15 | =(RC[-3]*R[-4]C[-2]+RC[-1]*(R4C2-R[-4]C[-2]))*12 |
| 16 | =(RC[-3]*R[-5]C[-2]+RC[-1]*(R4C2-R[-5]C[-2]))*2 |
| 4 | 5 | |
|---|---|---|
| 7 | 返済総額 | =R[8]C[1]+R[9]C[1]+R[14]C[1]+R[15]C[1] |
3%ぐらいの金利で30年借りると約5割増しで返済することになるのがわかります。
予想以上に給料が増えたとか、宝くじに当たったなど、余剰のお金をローンの繰上げ返済にあてる事態になるかもしれません。しかし、繰上げ返済には手数料がかかたりしてどのぐらい得なのかがわかりにくいと思います。先に作成した償還表を見るとどのぐら得になるのか（利息の支払いを減らすことができるのか）がわかります。
話を簡単にするために借入金2900万全額を銀行の30年間固定ローン(金利4%)で、ボーナス払いなしでローンを組んだとします。この場合毎月の返済額は138450円となります。
5年後100万円貯金ができたとします。このときの償還表は下記のようになります(一部省略しています)。
| 1 | 2 | 3 | 4 | 5 | |
|---|---|---|---|---|---|
| 90 | 60 | \26,280,627 | 4.00% | \87,602 | \138,450 |
| 91 | 61 | \26,229,779 | 4.00% | \87,433 | \138,450 |
| 108 | 78 | \25,338,957 | 4.00% | \84,463 | \138,450 |
| 109 | 79 | \25,284,970 | 4.00% | \84,283 | \138,450 |
| 110 | 80 | \25,230,803 | 4.00% | \84,103 | \138,450 |
| 111 | 81 | \25,176,455 | 4.00% | \83,922 | \138,450 |
60回目の支払いが終わった後の残額が26,229,779円です。 ここで100万円を繰上げ返済すると残額が25,229,779円となります。 おおよそ79回目の支払い後の残額25,230,803円と等しくなります。 計算を簡便にするために100万円ではなく、998,976円を繰上げ返済したとします。 するとその後の返済は80回目からとなり、61回目から79回目までの19回分の利息が節約できたことになります。 この間の利息の合計は1,831,582円になり、これが繰り越し返済で節約できた金額となります。
次に気になるのは繰り越し返済はこまめにしたほうがいいのか、大きくしたほうがいいのかということです。 5年で100万余分にためたので、もう一年がんばると120万円ためることができると考えます。 このとき、5年後に100万返済するのか、6年後に120万返済するのかどちらが得になるのか計算してみましょう。 6年後の償還表を見てみます。 72回目の支払い後(73回)の残額は25,606,215円です。 約2440万の残金を探すと95回の残金が24,396,287円となっています。 ので、簡便性のために1,209,928円繰上げ返済をするとします。 すると73回から94回の間の利息の合計1,835,982円が節約できます。 20万余分に貯めても4千円ぐらいしか得になりませんでした。 なるべく早い時期(利息の大きな時期)に返済をしたほうが有利になると考えられます。 実際には手数料がかかるのであまり小さな金額では節約効果がでませんが、繰上げ返済はこまめにしたほうがよさそうです。
必要なら下記に必要事項を記入してExcelのファイルをダウンロードして使ってください。 計算結果等に関しては一切責任を持ちませんので、実際の返済プランにかんしては銀行などとよく相談してください。
私が三井不動産でとってもらった見積書と比較すると数値はぴったり合いますが、実際の銀行のローンの計算では数円の単位で異なってくることがあります。 おそらく１円未満の端数の処理が異なるためだと思います。 本当に家を買うときにはこの辺の数値の誤差より登記やら引越しやらの手数料のほうがべらぼうに高いので気にしなくていいと思います。
毎月の返済額には管理費、修繕積み立て金などが入っていません。 実際にはそれらの金額も考慮する必要があると思います。 あと毎年固定資産税がかかります。
それではローンの返済がんばってください。