住宅の資金計画にお役立て、簡単!Excelを使って住宅ローンのシュミレーション

スポンサーリンク

ブログに立ち寄って頂き、ありがとうございます!

この記事を気に入って頂けたら、シェアお願いします!

住宅メーカーや銀行のホームページには、金利や借入金額、返済期間を入力すると、月々の返済額を計算してくれるシュミレーション機能を設けています。

それでも多くの場合、シュミレート結果には月々の金利と元金の返済の内訳が表記されません。

この金利と元金の返済バランスを、自分で調べることができるんです。

Excelが使えるパソコンであれば、だれでも簡単に算出できます。

自作だからExcelシートに幾つかのパターンを作って、自在にシュミレートしてみることができます。

住宅ローンはどのように試算しするの?

元金にかかる利息「単利」と、元金と利息にかかる利息「複利」

利息には「単利」と「複利」、2種類の利息があります。

例えば35年ローンで3,000万円のローンを組んで、年利3%の利息だったとします。

この場合、1年の利息は3,000万円の利息3%=90万円です。

これを35年間支払いますから、利息の合計は90万円×35年=31,500,000円と元金を上回る利息となります。

このように、単利の計算でも目を見張るほどの利息になってしまいますが、実は複利はその上をいってしまうのです。

住宅ローンでは「単利」での金融商品は殆どありません。多くは「複利」での貸し付けとなります。

複利とは元金にかかる利息もひっくるめた金額に、また利息が付いてきます。

単純に言うと、1年目は3,000万円の年利3%で90万円の利息がつきます。

ですので2年目は3,090万円に対しての年利3%=927,000円の利息となります。

ただし、実際には1年目に元金から返済された分を差し引かれて計算されるので、もう少し安くなりますが、最初のうちは利息の支払いが多くなり、なかなか元金が返済されにくいのです。

複利の計算では、上記の例で言うと・・

●初月

3,000万円での利息は年利3%でしたから、3,000万円×3%=90万円。

90万円÷12ヶ月=75,000円となります。

仮に月々の支払額が11万円だとすると、11万円(月々支払額)ー75,000円(利息)=35,000円(元金)となり、11万円支払うなか、利息が75,000円で元金の支払いは35,000円となります。

●次月

元金は35,000円が返済されたので、借入額は3,000万円ー35,000円=29,965,000円。

利息はこの29,965,000円の3%で898,950円です。

これを月額に直すと898,950円÷12か月=74,912円です。

よって次月の利息支払い額は74,912円、月の返済額11万円のうち利息が74,912円で、元金の返済額は35,088円と、前の月より88円多く返済できました。

このようにして、複利のローンでは利息は減っていく元金に対してかかっていきます。

●しかし

今回は仮に月額の返済額を11万円と過程しましたが、この返済額は借入金額、利息、返済期間などの変動によって決まってきます。

非常に複雑な計算となります。(-_-;)

そこで、今回は色々なパターンを自分で想定、編集できるように、複利のローン返済シュミレーションをExcelを使って自作する方法を紹介したいと思います。

ここではボーナス返済を考慮しません。

ボーナス払いをすれば月々の返済額は負担が少なくなりますが、長いローン期間でボーナスは安定してあてにできるのか・・?不安に思う人も少なくないと思います。

ボーナス払いを除いたら、どのくらいの返済計画が建てられるのか?を参考に試算してみるのも良いですよね。

Excelで住宅ローンシュミレーターを作ってみる

1. Excelを開き、下図のように基本情報を打ち込む

「借入金額」の下のセル(枠)「A4」を右クリック⇒「セルの書式設定」⇒「数値」を選んで「桁区切り(,)を使用する」のチェックボックスにチェックを入れます。

月々の返済額を導きだしてみる

「利率」の下のセル(枠)「C4」もに右クリック⇒「セルの書式設定」⇒「パーセンテージ」と選択して「小数点以下の桁数」を2にします。

2. 下図のように、返済回数、月々返済額、月々の返済額の内訳(元金と利息)の枠を設けます。

3. 「借り入れ金額」「返済期間(月回数)」「利率」の下のセル(枠)に数値を打ち込みます。

今回も先ほどから出している、例の数値にしていきます。

4. 今度は「月返済額」の下のセル(枠)に「=」を打ち込み、下図の②の赤丸をクリックします。

5. すると「関数の挿入」というウィンドーが出てくるので、➀のプルダウンメニューをクリックし、②の「財務」を選択して「OK」を押します。

6. 「財務」を選択したら、その中の「PMT」を選んで「OK」をクリックします。

(項目はアルファベット順に並んでいます)

7. 「OK」をクリックすると、また新たなウィンドーが現れます。

・「利率」の項目は「3.00%」と打ち込んであるセルをクリック選択し、「/12」と入力し、「月利」に変換します。

・「期間」の項目は「420」と打ち込んであるセルをクリック選択します。

・「現在価値」の項目は「30,000,000」と打ち込んであるセルをクリック選択します。

・「将来価値」の項目は最終的に「0円」になるよう返済するので、「0」と入力します。

・「支払期日」は返済を期首に行うか、期末に行うか、の選択で、「1:期首」「0:期末」という意味で、空白にした場合「0:期末」を選択した意味になります。

ここは、空白で構わないと思います。

最後に「OK」をクリックします。

8. すると、「=」を打ち込んだセルに「¥-115,455」と数値が入ります。

ただし、マイナスの数値だと見づらいので編集します。

「=」と「PMT」の間に「0-」と入力して「Enter」

でマイナスの数値がプラスに変換されました。

9. ついでに同じ箇所のアルファベットの前後に下図のように「$」を打ち込んでください。

これで、下までオートフィルにしても数値が崩れません。


オートフィルとはセル(枠)の右下にマウスポインタが「十字」になるように合わせて、そのまま左クリックしながら(ドラッグしながら)下にマウスを降ろしていくと下のセル(枠)が同じ数式になる機能です。

これで、月々の返済額を導きだすことができました。

あとは、利率や返済期間を自由に打ち込んで、色々と調べることができます。

返済額の内訳を導きだしてみる (利息編)

1. 金利の下のセル(枠)に「=」と打ち込み、下図の②をクリックします。

2. 「関数の挿入」ウィンドーが現れるので「財務」⇒「IPMT」を選び「OK」をクリックします。

3. 新たに「関数の引数」ウィンドーが現れます。

・「利率」の項目は「3.00%」と打ち込んであるセルをクリック選択し、「/12」と入力し、「月利」に変換します。

・「期」は支払い回数を表しますので、「返済回数(回目)」の下のセル(枠)「1」をクリックします。

・「期間」の項目は「420」と打ち込んであるセルをクリック選択します。

・「現在価値」の項目は「30,000,000」と打ち込んであるセルをクリック選択します。

・「将来価値」の項目は最終的に「0円」になるよう返済するので、「0」と入力します。

4. 「OK」ボタンを押すと、下図のように「1回目」の金利が算出されます。

ここでも、マイナス表記をプラスに、オートフィルをしても数値が算出されるように「$」を打ち込み編集します。

(下図参照)

注) 「返済回数(回目)」の下のセル(枠)を意味するA8のAの隣には$を打ち込まないようにしてください。

420回目となると、返済額のうち金利の占める額は288円となっています。

オートフィルのやり方はこちらを参照してください。

オートフィルのやり方

返済額の内訳を導きだしてみる (元金編)

1. 元金の下のセル(枠)に「=」と打ち込み、下図の②をクリックします

2. 「関数の挿入」ウィンドーが現れるので「財務」⇒「PPMT」を選び「OK」をクリックします。

3. 新たに「関数の引数」ウィンドーが現れます。

・「利率」の項目は「3.00%」と打ち込んであるセルをクリック選択し、「/12」と入力して年利を「月利」に変換します。

・「期」は支払い回数を表しますので、「返済回数(回目)」の下のセル(枠)「1」をクリックします。

・「期間」の項目は「420」と打ち込んであるセルをクリック選択します。

・「現在価値」の項目は「30,000,000」と打ち込んであるセルをクリック選択します。

・「将来価値」の項目は最終的に「0円」になるよう返済するので、「0」と入力します。

4. 「OK」ボタンを押すと、下図のように「1回目」の元金の返済額が算出されます。

ここでも、「=」の後ろに「0-」を打ち込んでマイナス表記をプラスに、オートフィルをしても数値が算出されるように「$」を打ち込み編集します。

(下図参照)

注) 「返済回数(回目)」の下のセル(枠)を意味するA8のAの隣には$を打ち込まないようにしてください。

オートフィルを行って月々の返済額の内訳、元金の部分が見えてきます。

オートフィルのやり方

返済額と金利の合計額を算出する

1. ウィンドー枠の固定をして、Excel画面を下にスクロールしても、項目を見られるようにしておきます。

「月返済額」の下のセル(枠)をクリックして選択します。

2. 上の「表示」から「ウィンドー枠の固定」を選びます。

これで、下にスクロールしても項目は隠れないので見やすくなりました。

3. 下図➀のように、「420」の下に「合計」と打ち込み、その右隣りの「月返済額」の合計欄に「=」、②のアイコンをクリックします。

3. 次に「SUM」を選んでOKします。

 

4. 数値1に「B8:B427」と入力して、「OK」ボタンを押します。

 

「B7」は「月返済額」の1回目の支払い(スタート)のセル(枠)です。

「B427」は420回目(ゴール)のセル(枠)です。この2つのセルを入力して初回から最終回までの数値の合計を求める機能です。

 

 

5. 「=」を打ち込んだセル(枠)に「月返済額」の合計が表示されました。

6. 隣のセル(枠)にも、それぞれ「元金」「金利」の合計額をオートフィルを使って求めます。

月返済額の合計が表示されているセルの、右下端にカーソルを合わせます。

そしれそもまま、左クリックをして、クリックを押したまま右2つスライド(ドラック)します。

7. それぞれの項目に合計が表示されました。

8. 合計額に数字が表示されなくて、「#####」となった時は、文字が大きさ的にセル(枠)に収まらないという意味ですので、セルの横幅を大きくしたり、文字サイズを小さくしてみてください。

本日も最後までお読み頂きありがとうございました。

無料で、資金計画の相談を受けてくれるサイトを2つ紹介します。

タウンライフ家づくり


タウンライフ家づくりなら、資金計画から間取りの提案、土地探しまで色々な工務店から一括で無料見積もりをしてもらえます。

住もうよ!マイホーム


住もうよ!マイホームは、不動産と言うより、フィナンシャルプランナーの資格をもつ「お金のプロ」集団。
資金や利息の種類に迷ったら、心強い相談先となります。

この記事を気に入って頂けたら、シェアお願いします!