エクセルで住宅ローンの、シミュレーション計算をしていきます。
今回作る表は、次のローンのケースです。
- 借入額 2,000万円
- 期間 35年
- 金利タイプ 固定10年 11年目以降見直しあり
- 元利均等返済方式
そして作成後の表を使って、次の操作ができます。
- 最初の10年の金利を打ち込み、同期間の返済額算出
- 11年目からの予測金利を打ち込み、同期間の返済額算出
- それぞれの金利を試しながら、総支払額を算出
- 11年目から更に10年間、固定金利にした場合の21年目の予測金利打ち込みと返済額算出
（10年ごとに金利を予測、変更しながら試算ができる） - 全期間固定型ローン、全期間変動型ローンも試算可能
【固定金利期間選択型】固定10年金利タイプのシミュレーション表を作成
- 借入額 2,000万円
- 期間 35年
- 固定金利期間選択型
- 元利均等返済方式
のシミュレーション表を、作っていきます。
完成した表は、ここからダウンロードもできます。(パソコンのみ対応）
【固定10年シミュレーション】表の枠組みを作る
表は左から縦に10年づつ区切りました。
35年ローンなので、最後（一番右）は、5年になっています。
10年ごとに太線で囲みます。
【固定10年シミュレーション】計算式を入れる
今回のシミュレーションでは、計算式を入れるゾーンは以下図の赤丸で囲んだゾーンのみです。
解説の便宜上、各ゾーンに名前を付けます。
セルの位置説明に「数字」「アルファベット」を使うので、ゾーンの名前は「ひらがな」で「い・ろ・は・に・ほ・へ・と」とします。
はじめに「ろ」のゾーンから計算式を入れていきます。
「ろ」ゾーンの計算式
このゾーンは「はじめの10年」とありますが、借り入れたときの契約に基づいて数値を入れていきます。
上図のオレンジで塗られたセルは、入力して数字を変えられるセルとなるので、計算式は入れませんが、「C5」のセルは表示形式を「パーセンテージ」にします。
「C5」を選択⇒右クリック⇒セルの書式設定⇒「表示形式タグ」の中、パーセンテージ選択、小数点以下の桁数は3にしてOKをクリック
次に、毎月の返済額を求める「C6」に計算式を入れていきます。
■「C6」に月々返済額の計算式を入れる
「C6」に「＝（イコール）」を入れる⇒数式バーの左「ｆｓ」のアイコンをクリック
⇒関数は「PMT」を選択⇒ダイアログボックスに以下を入力
- 利率・・C5/12
- 期間・・C4*12
- 現在価値・・C2*10000
- 将来価値・・空白でOK
- 支払期日・・空白でOK
ダイアログボックス「OKボタン」を押すと、数式バーに
「PMT(C5/12,C4*12,C3*10000)」
と表示され「C6」は、マイナス表示になります。そこで数式バーに書き足しを行い、次のようにします。
「TRUNC(-(PMT(C5/12,C4*12,C3*10000)))」
（赤文字を書き足します）
TRUNCは小数点をスムーズに切捨てるため、いれておきます。
借入額 2000
返済期間 35
金利 1.358 と入力して、
毎月返済額が「59,855」になれば、計算式は合っているはずです。
「に」ゾーンの計算式
「に」のゾーンは、赤枠の中『上段（9行）』『下段（10行）』とで、計算式が違います。
11行以下は、10行と同じなので、オートフィルで対応できます。
「に」ゾーン 9行目の計算式
■支払い利息だけ、関数を使います。
支払利息以外の項目は、以下のとおりです。
- 月返済額「B9」は「＝C6」
- 支払元金「C9」は「＝B9-D9」
- 残高「E9」は「＝C3-C9」
支払利息は、IPMT関数を使います。
「D9」セルに「＝」を入力後、数式バーのとなりの「fs」アイコンをクリック
⇒関数は「IPMT」を選択⇒ダイアログボックスに以下を入力
- 利率・・C5/12
- 期・・・1
- 期間・・C4*12
- 現在価値・・C3*10000
- 将来価値・・空白でOK
ダイアログボックス「OKボタン」を押すと、数式バーに
「IPMT(C5/12,1,C4*12,C3*10000)」
と表示され「D9」は、マイナス表示になります。そこで数式バーに書き足しを行い、次のようにします。
「TRUNC(-(IPMT(C5/12,1,C4*12,C3*10000)))」
※ TRUNCは小数点をスムーズに切捨てるため、いれておきます。
■ 支払元金だけ、オートフィルで１０年目（128行）まで利息の計算式をコピーします。
「に」ゾーン 10行目の計算式
10行目は「月返済額」「支払金利」「残高」が、９行目と計算式が違ってきます。
■10行目以降の計算式
- 月返済額「B10」は「＝B9」
- 支払元金「C10」は「＝B10-D10」
※すでにオートフィルで作成済 - 残高「E10」は「＝E9-C10」
支払利息は、IPMT関数を使います。
「D10」セルに「＝」を入力後、数式バーのとなりの「fs」アイコンをクリック
⇒関数は「IPMT」を選択⇒ダイアログボックスに以下を入力
- 利率・・$C$5/12
- 期・・・1
- 期間・・$C$4*12
- 現在価値・・E9
- 将来価値・・空白でOK
ダイアログボックス「OKボタン」を押すと、数式バーに
「IPMT($C$5/12,1,$C$4*12,E9)」
と表示され「D10」は、マイナス表示になります。そこで数式バーに書き足しを行い、次のようにします。
「TRUNC(-(IPMT($C$5/12,1,$C$4*12,E9)))」
※ TRUNCは小数点をスムーズに切捨てるため、いれておきます。
※ 「＄」は「Shift」キーを押しながら「４」を押して入力
■「月返済額」「支払利息」「残高」をオートフィルで10年後（エクセル128行目）まで数式コピーをします。
「は」ゾーンの計算式
ローンの金利タイプが「全期間固定型」のローンなら、「ろ」の計算式だけでシミュレーションは終わります。
しかし、期間固定型であれば、期間が過ぎれば金利の見直しがあります。
今回の例「固定１０年金利」なら、１０年が過ぎると
さらにもう１０年の期間固定にするか、変動費にするか、それとも全期間の固定費にするかを決定しなくてはなりません。
■「H3」のセルは「=E128」と入力し、１０年末月の残高とします。
■「H4」のセルは「＝C４-10」と入力し、１０年分を引きます。
■「H5」のセルはランダムに入力できるようにするセルです。
右クリック⇒セルの書式設定⇒常時形式タブ⇒パーセンテージ⇒OK
■「H6」は関数を使います。
支払利息は、IPMT関数を使います。
「H6」セルに「＝」を入力後、数式バーのとなりの「fs」アイコンをクリック
⇒関数は「PMT」を選択⇒ダイアログボックスに以下を入力
- 利率・・H5/12
- 期間・・H4*12
- 現在価値・・H3
- 将来価値・・空白でOK
- 支払期日・・空白でOK
ダイアログボックス「OKボタン」を押すと、数式バーに
「PMT(H5/12,H4*12,H3)」
と表示され「H6」は、マイナス表示になります。そこで数式バーに書き足しを行い、次のようにします。
「TRUNC(-PMT(H5/12,H4*12,H3))」
※ TRUNCは小数点をスムーズに切捨てるため、いれておきます。
※ 「H5」はいろいろな金利を入力し、シミュレートするのに使います⇒ここでは便宜上「1.00％」としておきます。
「ほ」ゾーンの計算式
「ほ」のゾーンは、赤枠の中『上段（9行）』『下段（10行）』とで、計算式が違います。
11行以下は、10行と同じなので、オートフィルで対応できます。
「ほ」のゾーン 9行目の計算式
■支払い利息だけ、関数を使います。
支払利息以外の項目は、以下のとおりです。
- 月返済額「G9」は「＝H6」
- 支払元金「H9」は「＝G9-I9」
- 残高「E9」は「＝H3-H9」
支払利息は、IPMT関数を使います。
「I9」セルに「＝」を入力後、数式バーのとなりの「fs」アイコンをクリック
⇒関数は「IPMT」を選択⇒ダイアログボックスに以下を入力
- 利率・・H5/12
- 期・・・1
- 期間・・H4*12
- 現在価値・・H3
- 将来価値・・空白でOK
ダイアログボックス「OKボタン」を押すと、数式バーに
「IPMT(H5/12,1,H4*12,H3)」
と表示され「I9」は、マイナス表示になります。そこで数式バーに書き足しを行い、次のようにします。
「TRUNC(-(IPMT(H5/12,1,H4*12,H3)))」
※ TRUNCは小数点をスムーズに切捨てるため、いれておきます。
■ 支払元金だけ、オートフィルで20年目末月（128行）まで利息の計算式をコピーします。
「ほ」のゾーン 10行目の計算式
10行目は「月返済額」「支払金利」「残高」が、９行目と計算式が違ってきます。
■10行目以降の計算式
- 月返済額「G10」は「＝G9」
- 支払元金「G10」は「＝G10-I10」
※すでにオートフィルで作成済 - 残高「J10」は「＝J9-H10」
支払利息は、IPMT関数を使います。
「D10」セルに「＝」を入力後、数式バーのとなりの「fs」アイコンをクリック
⇒関数は「IPMT」を選択⇒ダイアログボックスに以下を入力
- 利率・・H5/12
- 期・・・1
- 期間・・$H$4*12
- 現在価値・・J9
- 将来価値・・空白でOK
ダイアログボックス「OKボタン」を押すと、数式バーに
「IPMT($H$5/12,1,$H$4*12,J9)」
と表示され「I10」は、マイナス表示になります。そこで数式バーに書き足しを行い、次のようにします。
「TRUNC(-(IPMT($H$5/12,1,$H$4*12,J9)))」
※ TRUNCは小数点をスムーズに切捨てるため、いれておきます。
※ 「＄」は「Shift」キーを押しながら「４」を押して入力
■「月返済額」「支払利息」「残高」をオートフィルで20年目末月（エクセル128行目）まで数式コピーをします。
「へ」と「と」の上のゾーンはオートフィルで作成
計算式を入力するゾーンは
「い」「ろ」「は」「に」「ほ」「へ」「と」
の７つのゾーンだけです。
とは言え、「へ」と「と」のゾーンは上のゾーンを完成させないと機能しません。
このゾーンは、「は」のゾーンをそっくりオートフィルでコピーして出来上がります。
■「F2」から「J7」を斜めにドラッグし、選択します。
（ドラッグとは、左クリックしたままマウスを動かすこと）
■「J7」セルの右下にカーソルを合わせます。
■カーソルの形が「十字」に変ったら、左クリックしたまま横に「T7」までスライドします。
オートフィルが上手く行かない場合、コピーで解決
セルのサイズが違ったりして、オートフィルが機能しない場合があります。
そんな時はコピーで解決できます。
■「F2」から「J7」を斜めにドラッグし、選択します。
（ドラッグとは、左クリックしたままマウスを動かすこと）
■右クリック⇒コピー
■「K2」を選択⇒「Ctrl」キーを押しながら「P2」をクリック
■一度「Ctrl」キーをはなし、また「Ctrl」キーを押しながら「V」キーを押します。
■「は」のゾーンの計算式が張り付きます。
※最後に「と」のゾーン上のゾーン「次の１０年」を「次の５年」に変えます。
「へ」ゾーンの計算式
これまでの「に」「ほ」のゾーンと同じ計算式ですが、これまで通り解説していきます。
９行目と１０行目は、計算式が違います。
「へ」のゾーン 9行目の計算式
■支払い利息だけ、関数を使います。
支払利息以外の項目は、以下のとおりです。
- 月返済額「L9」は「＝M6」
- 支払元金「M9」は「＝L9-N9」
- 残高「O9」は「＝M3-M9」
支払利息は、IPMT関数を使います。
「N9」セルに「＝」を入力後、数式バーのとなりの「fs」アイコンをクリック
⇒関数は「IPMT」を選択⇒ダイアログボックスに以下を入力
- 利率・・M5/12
- 期・・・1
- 期間・・M4*12
- 現在価値・・M3
- 将来価値・・空白でOK
ダイアログボックス「OKボタン」を押すと、数式バーに
「IPMT(M5/12,1,M4*12,M3)」
と表示され「I9」は、マイナス表示になります。そこで数式バーに書き足しを行い、次のようにします。
「TRUNC(-(IPMT(M5/12,1,M4*12,M3)))」
※ TRUNCは小数点をスムーズに切捨てるため、いれておきます。
■ 支払元金だけ、オートフィルで30年目末月（128行）まで利息の計算式をコピーします。
「へ」のゾーン 10行目の計算式
10行目は「月返済額」「支払金利」「残高」が、９行目と計算式が違ってきます。
■10行目以降の計算式
- 月返済額「L10」は「＝L9」
- 支払元金「M10」は「＝L10-N10」
※すでにオートフィルで作成済 - 残高「O10」は「＝O9-M10」
支払利息は、IPMT関数を使います。
「N10」セルに「＝」を入力後、数式バーのとなりの「fs」アイコンをクリック
⇒関数は「IPMT」を選択⇒ダイアログボックスに以下を入力
- 利率・・$M$5/12
- 期・・・1
- 期間・・$M$4*12
- 現在価値・・O9
- 将来価値・・空白でOK
ダイアログボックス「OKボタン」を押すと、数式バーに
「IPMT($M$5/12,1,$M$4*12,O9)」
と表示され「I10」は、マイナス表示になります。そこで数式バーに書き足しを行い、次のようにします。
「TRUNC(-(IPMT($M$5/12,1,$M$4*12,O9)))」
※ TRUNCは小数点をスムーズに切捨てるため、いれておきます。
※ 「＄」は「Shift」キーを押しながら「４」を押して入力
■「月返済額」「支払利息」「残高」をオートフィルで30年目末月（エクセル128行目）まで数式コピーをします。
「と」ゾーンの計算式
この例は３５年の住宅ローンですから、最後の５年間となります。
９行と１０行の計算式が違うので、それぞれ解説していきます。
「と」のゾーン 9行目の計算式
■支払い利息だけ、関数を使います。
支払利息以外の項目は、以下のとおりです。
- 月返済額「Q9」は「＝R6」
- 支払元金「R9」は「＝Q9-S9」
- 残高「T9」は「＝R3-R9」
支払利息は、IPMT関数を使います。
「S9」セルに「＝」を入力後、数式バーのとなりの「fs」アイコンをクリック
⇒関数は「IPMT」を選択⇒ダイアログボックスに以下を入力
- 利率・・R5/12
- 期・・・1
- 期間・・R4*12
- 現在価値・・R3
- 将来価値・・空白でOK
ダイアログボックス「OKボタン」を押すと、数式バーに
「IPMT(R5/12,1,R4*12,R3)」
と表示され「I9」は、マイナス表示になります。そこで数式バーに書き足しを行い、次のようにします。
「TRUNC(-(IPMT(R5/12,1,R4*12,R3)))」
※ TRUNCは小数点をスムーズに切捨てるため、いれておきます。
■ 支払元金だけ、オートフィルで3５年目末月（68行）まで利息の計算式をコピーします。
「と」のゾーン 10行目の計算式
10行目は「月返済額」「支払金利」「残高」が、９行目と計算式が違ってきます。
■10行目以降の計算式
- 月返済額「Q10」は「＝Q9」
- 支払元金「R10」は「＝Q10-S10」
※すでにオートフィルで作成済 - 残高「T10」は「＝T9-R10」
支払利息は、IPMT関数を使います。
「S10」セルに「＝」を入力後、数式バーのとなりの「fs」アイコンをクリック
⇒関数は「IPMT」を選択⇒ダイアログボックスに以下を入力
- 利率・・$R$5/12
- 期・・・1
- 期間・・$R$4*12
- 現在価値・・T9
- 将来価値・・空白でOK
ダイアログボックス「OKボタン」を押すと、数式バーに
「IPMT($R$5/12,1,$R$4*12,T9)」
と表示され「I10」は、マイナス表示になります。そこで数式バーに書き足しを行い、次のようにします。
「TRUNC(-(IPMT($R$5/12,1,$R$4*12,T9))))」
※ TRUNCは小数点をスムーズに切捨てるため、いれておきます。
※ 「＄」は「Shift」キーを押しながら「４」を押して入力
■「月返済額」「支払利息」「残高」をオートフィルで30年目末月（エクセル68行目）まで数式コピーをします。
「い」ゾーンの計算式
ここのゾーンで、３５年間の総支払額を表示します。
単純に、毎月の返済額を合計したものです。
■「C1」のセルの計算式
「=(SUM(B9:B128))+(SUM(G9:G128))+(SUM(L9:L128))+(SUM(Q9:Q68))+T68」
※ 最後の「T6」は、残高の帳尻を合わせるために足します。
これで、固定10年金利の３５年ローンのシミュレーション作成の説明は終わります。
後悔先に立たず、住宅ローンの一括比較、プランが無料でもらえる便利なサービスも上手に活用してみてはいかがでしょうか。
今後の金利が気になる方は、以下の記事もご覧ください。
コメント