Excel関数で住宅ローンの返済シミュレーション表を作ったので公開してみる

はじめに、、

一応のお断りとなりますが、今回用意した返済シミュレーションは極めて簡易的なもので、金融機関の計算ロジックとは異なります。 概算前提で利用する分には十分な精度だと思いますが、計算式に誤りがある可能性を含めて一切の責任を負いかねますので、自己の責任の下でご利用ください。

住宅ローンの返済シミュレーション表の使い方

住宅ローンの返済方法には元利均等と元金均等の2つの方式がありますが、今回作ったのは最も一般的な返済方式である元利均等方式にのみ対応しています。

住宅ローン返済シミュレーション表をご利用頂く場合は、次の情報を準備してください。

  1. 借入金額(円単位)
  2. 借入期間(年単位)
  3. 借入金利(%単位)
  4. 最初の返済日

なお、4の最初の返済日は返済予定表の基準日として表示しますが計算には使わない便宜的なものですので正確でなくても構いません。

それでは、以下順を追って使い方をご説明します。

「住宅ローン返済シミュレーション表(簡易版).xlsx」の入手方法

Excelファイルは私がSCDB JAPANを運営するために契約しているIDCFクラウドのオブジェクトストレージに保管しています。 scdb.jp

以下のリンクをクリックするとファイルのダウンロードが開始されますので適当な場所に保存してください。

https://blog-contents.ds.jp-east.idcfcloud.com/住宅ローン返済シミュレーション表(簡易版).xlsx

借入金額、借入期間、借入金利、最初の返済日の入力

Excelファイルを開くと2つのシートがありますが、入力するのは「返済予定表」シートのセル「B1」~「B4」の4カ所だけです。 以下のように、適切な値を入力してください。

f:id:boost-up:20171129214546p:plain

返済予定表の作成

作成、とは言っても今回はすべてExcelの関数式で作っていますので、必要な値を入力した時点でExcelが自動計算してくれるはずです。 もし自動計算されない場合はブックの計算が「手動」担っている可能性がありますので「F9」ボタンを押して更新してください。

計算が実行されると以下のように計算結果が表示されるはずです。 7行目から下の表が月別の返済予定表、それを集計した結果が上部のセル「D1」~「F3」に表示されます。

f:id:boost-up:20171129215133p:plain

この例だと、3,000万円を金利1.4%で35年借りた場合、毎月の返済額が90,392円となり、 35年の総支払額が3,796万円となることが分かります。

シート「グラフ」には、非常に簡単ではありますが、 返済後残高の推移と合わせて返済額に占める元本返済額と利息の割合推移をグラフ化しています。

f:id:boost-up:20171129215506p:plain

繰上返済額/増額返済額によるシミュレーション

今回の返済シミュレーション表ではG列「繰上返済額/増額返済額」に金額を入力することで、返済額軽減型を前提としたシミュレーションができるようにしてみました。

例えば、借入から半年後に500万円の繰上が可能になる予定があるとします。 (グラフの変化をわかりやすくするために意図的に大きな金額にしています) その場合は、以下のようにG列に5,000,000と入力すると、翌月以降の返済額が再計算されることで、 毎月の支払額が90,392円⇒75,158円(▲15,234円)に変化します。

f:id:boost-up:20171129215908p:plain

また、シート「グラフ」を見ると、上のグラフでは6か月目に500万円が返済されて残高が大きく減少していることが、 下のグラフでは同様に毎月の支払額が6か月目から軽くなることが視覚的に見て取れると思います。

f:id:boost-up:20171129220156p:plain

Excelの解説

返済シミュレーション表そのものに興味がある方はここまでで終了となりますが、一応Excelで何をやっているのかだけ簡単に説明しておきます。

「PMT関数」、「PPMT関数」、「IPMT関数」

Excelで住宅ローンの計算を行う場合、基本的には上記3つの関数が重要となります。

  • PMT関数・・・一定利率の支払が定期的に行われる場合の、ローンの定期支払額を算出します。

f:id:boost-up:20171129221714p:plain

  • PPMT関数・・・一定利率の支払が定期的に行われる場合の、投資の指定した期に支払われる元金を返します。

f:id:boost-up:20171129221813p:plain

  • IPMT関数・・・一定利率の支払が定期的に行われる場合の、投資期間内の指定された期に支払われる金利を返します。

f:id:boost-up:20171129221909p:plain

PPMT関数とIPMT関数は、それぞれ元本返済額、利息を計算するためのもので、その計算結果の合計はPMT関数と一致しますが、 Excel上では端数処理の関係でいずれか一方を使います。 発生した利息は支払うという基本的な考え方からすると、IPMT関数を使用して利息をより正確に計算し、元本返済額は差し引きで出す方が正しいと思いますが、今回は端数処理の容易さを優先し、PPMT関数を使用して元本返済額を直接計算し、利息は差し引きで計算しています。

また、住宅ローンでは月払いが一般的ですので、PMT関数、PPMT関数の計算も月別に行う必要があります。 具体的には、利率、期、期間について少し工夫をしています。

利率

金融機関の表示金利は年利ですが、月別計算を行うため、年利/12をセットします。

これはPPMT関数(IPMT関数)だけの引数ですが、期間全体の中の何期目の計算なのかを示す引数です。 通常は全体の期間を固定して、期をカウントアップしていくのですが、その方法だと「繰上返済額/増額返済額」に対応できないため、 期は常に”1”とし、期間を減らしていくようにします。

期間

上述のように、期間を減らして行きますので、「借入期間×12+1-期間(A列)」とすることで、今回の例であれば420, 419, 418,・・・となるようにしています。