【住宅ローンシミュレーション】エクセルを使って簡単計算《固定10年 35年ローンでシミュレーション》

住宅に関わるお金のこと

エクセルで住宅ローンの、シミュレーション計算をしていきます。

今回作る表は、次のローンのケースです。

  • 借入額 2,000万円
  • 期間  35年
  • 金利タイプ 固定10年 11年目以降見直しあり
  • 元利均等返済方式

そして作成後の表を使って、次の操作ができます。

  • 最初の10年の金利を打ち込み、同期間の返済額算出
  • 11年目からの予測金利を打ち込み、同期間の返済額算出
  • それぞれの金利を試しながら、総支払額を算出
  • 11年目から更に10年間、固定金利にした場合の21年目の予測金利打ち込みと返済額算出
    (10年ごとに金利を予測、変更しながら試算ができる)
  • 全期間固定型ローン、全期間変動型ローンも試算可能
エクセルのつくりを少しいじれば、返済期間や「固定5年」などの金利タイプをもカスタマイズできます

【固定金利期間選択型】固定10年金利タイプのシミュレーション表を作成

  • 借入額 2,000万円
  • 期間  35年
  • 固定金利期間選択型
  • 元利均等返済方式

のシミュレーション表を、作っていきます。

完成した表は、ここからダウンロードもできます。(パソコンのみ対応)

▶▶▶ 固定10年シミュレーション/サンプル

【固定10年シミュレーション】表の枠組みを作る

表は左から縦に10年づつ区切りました。

35年ローンなので、最後(一番右)は、5年になっています。

10年ごとに太線で囲みます。

【固定10年シミュレーション】計算式を入れる

今回のシミュレーションでは、計算式を入れるゾーンは以下図の赤丸で囲んだゾーンのみです。

解説の便宜上、各ゾーンに名前を付けます。

セルの位置説明に「数字」「アルファベット」を使うので、ゾーンの名前は「ひらがな」で「い・ろ・は・に・ほ・へ・と」とします。

はじめに「ろ」のゾーンから計算式を入れていきます。

「ろ」ゾーンの計算式

このゾーンは「はじめの10年」とありますが、借り入れたときの契約に基づいて数値を入れていきます。

上図のオレンジで塗られたセルは、入力して数字を変えられるセルとなるので、計算式は入れませんが、「C5」のセルは表示形式を「パーセンテージ」にします。

■セルの表示形式をパーセンテージにする
「C5」を選択⇒右クリック⇒セルの書式設定⇒「表示形式タグ」の中、パーセンテージ選択、小数点以下の桁数は3にしてOKをクリック

次に、毎月の返済額を求める「C6」に計算式を入れていきます。

 ■「C6」に月々返済額の計算式を入れる

「C6」に「=(イコール)」を入れる⇒数式バーの左「fs」のアイコンをクリック

関数は「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は小数点をスムーズに切捨てるため、いれておきます。

■ 支払元金だけ、オートフィルで10年目(128行)まで利息の計算式をコピーします。

「に」ゾーン 10行目の計算式

10行目は「月返済額」「支払金利」「残高」が、9行目と計算式が違ってきます。

■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」キーを押しながら「4」を押して入力

■「月返済額」「支払利息」「残高」をオートフィルで10年後(エクセル128行目)まで数式コピーをします。

「は」ゾーンの計算式

ローンの金利タイプが「全期間固定型」のローンなら、「ろ」の計算式だけでシミュレーションは終わります。

しかし、期間固定型であれば、期間が過ぎれば金利の見直しがあります。

今回の例「固定10年金利」なら、10年が過ぎると

さらにもう10年の期間固定にするか、変動費にするか、それとも全期間の固定費にするかを決定しなくてはなりません。

■「H3」のセルは「=E128」と入力し、10年末月の残高とします。

■「H4」のセルは「=C4-10」と入力し、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行目は「月返済額」「支払金利」「残高」が、9行目と計算式が違ってきます。

■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」キーを押しながら「4」を押して入力

■「月返済額」「支払利息」「残高」をオートフィルで20年目末月(エクセル128行目)まで数式コピーをします。

「へ」と「と」の上のゾーンはオートフィルで作成

計算式を入力するゾーンは

「い」「ろ」「は」「に」「ほ」「へ」「と」

の7つのゾーンだけです。

とは言え、「へ」と「と」のゾーンは上のゾーンを完成させないと機能しません。

このゾーンは、「は」のゾーンをそっくりオートフィルでコピーして出来上がります。

■「F2」から「J7」を斜めにドラッグし、選択します。
(ドラッグとは、左クリックしたままマウスを動かすこと)

■「J7」セルの右下にカーソルを合わせます。

■カーソルの形が「十字」に変ったら、左クリックしたまま横に「T7」までスライドします。

オートフィルが上手く行かない場合、コピーで解決

セルのサイズが違ったりして、オートフィルが機能しない場合があります。

そんな時はコピーで解決できます。

■「F2」から「J7」を斜めにドラッグし、選択します。
(ドラッグとは、左クリックしたままマウスを動かすこと)

■右クリック⇒コピー

■「K2」を選択⇒「Ctrl」キーを押しながら「P2」をクリック

■一度「Ctrl」キーをはなし、また「Ctrl」キーを押しながら「V」キーを押します。

■「は」のゾーンの計算式が張り付きます。

※最後に「と」のゾーン上のゾーン「次の10年」を「次の5年」に変えます。

「へ」ゾーンの計算式

これまでの「に」「ほ」のゾーンと同じ計算式ですが、これまで通り解説していきます。

9行目と10行目は、計算式が違います。

「へ」のゾーン 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行目は「月返済額」「支払金利」「残高」が、9行目と計算式が違ってきます。

■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」キーを押しながら「4」を押して入力

■「月返済額」「支払利息」「残高」をオートフィルで30年目末月(エクセル128行目)まで数式コピーをします。

「と」ゾーンの計算式

この例は35年の住宅ローンですから、最後の5年間となります。

9行と10行の計算式が違うので、それぞれ解説していきます。

「と」のゾーン 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は小数点をスムーズに切捨てるため、いれておきます。

■ 支払元金だけ、オートフィルで35年目末月(68行)まで利息の計算式をコピーします。

「と」のゾーン 10行目の計算式

10行目は「月返済額」「支払金利」「残高」が、9行目と計算式が違ってきます。

■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」キーを押しながら「4」を押して入力

■「月返済額」「支払利息」「残高」をオートフィルで30年目末月(エクセル68行目)まで数式コピーをします。

「い」ゾーンの計算式

ここのゾーンで、35年間の総支払額を表示します。

単純に、毎月の返済額を合計したものです。

■「C1」のセルの計算式

「=(SUM(B9:B128))+(SUM(G9:G128))+(SUM(L9:L128))+(SUM(Q9:Q68))+T68」

※ 最後の「T6」は、残高の帳尻を合わせるために足します。

これで、固定10年金利の35年ローンのシミュレーション作成の説明は終わります。

今後の金利が気になる方は、以下の記事もご覧ください。

コメント