住宅ローン繰り上げ返済(複数回)のシミュレーションをエクセルでやる関数は？

住宅ローンの繰り上げ返済には

• 期間短縮型
• 返済額軽減型

があります。

期間短縮型は、繰り上げ返済すればするほど返済期間が短くなります。

返済額軽減型は、返済期間はそのままで月々の返済額が減ります。

しかし、長期間の返済回数に散らばってしまい、軽減されているお得感が薄いと言われているんです。

そのため、繰り上げ返済を行う人の多くは期間短縮型を選んでいます。

住宅ローンの繰り上げ返済を複数回したい場合、自分のパソコンでシミュレーションできると便利ですよね。

私も住宅ローンを組む前と組んだ後で、住宅ローン返済シミュレーションををエクセルで作り、おおよそどのくらい利息がお得になるかを確認しました。

ネット上に住宅ローン返済シミュレーションはありますが、ネット回線が必要だし、期間短縮型に対応していなかったりして不便です。

そこで今回は、エクセルで住宅ローンの繰り上げ返済(期間短縮型)シミュレーションを作る際に使う関数の意味や、使い方の紹介をします。

1.住宅ローン返済シミュレーションのエクセル関数は？

住宅ローンの返済シミュレーションをして月々の返済額を確認するには、

①PMT関数（月々返済額）
②PPMT関数（月々返済額の元金部分）
③IPMT関数（月々返済額の利息部分）

を使います。

それぞれの関数の意味や使い方を順番に見ていきましょう。

①PMT関数＝住宅ローン月々返済額を確認する

住宅ローンの月々返済額を知るためには、

PMT関数

を使います。

PMTとは、「支払い」を意味する「Payment(ペイメント)」の略ですね。

PMT関数は以下の公式で表します。

公式の、

• -PMT
• 月利
• 返済回数
• 現在価値

について説明します。

-PMT
PMTの前に-(マイナス)がついていますが、これは普通にPMTと入力すると計算結果がマイナスで表示されるため、表の見やすさを考慮して便宜上つけています。

下で説明する「PPMT関数」、「IPMT関数」でも同様です。

月利
月利は、金利(年利)を12で割った突き当りの金利です。

住宅ローンは月々返済となるので、月利を使います。

返済回数
返済回数は、借入期間(年数)に12を掛けて、返済する月数(つまり回数)を表します。

現在価値
現在価値は、「毎月の返済前時点におけるローン残高」です。

初回返済前は「借入金額」と同じになります。

②月々返済額の元金部分＝PPMT関数

月々返済額のうち、元金の部分だけを表示させる方法です。

PPMT関数

を使います。

PMTの前に「P」が付いていますね。

このPは「元金」を意味する英単語「Principan」の略です。

「Principal Payment」＝「元金の支払い」という意味ですね。

PPMT関数は以下の公式で表します。

• -PPMT
• 月利
• 期
• 返済回数
• 現在価値

について説明します。

-PPMT
PPMTは、月々返済額のうち、元金部分の金額を表示するための関数です。

上で説明しましたPMT関数と同様、便宜上-(マイナス)を付けています。

月利
月利は、金利(年利)を12ヶ月で割った月ごとの金利です。

期
期は、今回の返済が最初から数えて「何回目か」を意味します。

税金の支払いなども、第1期、第2期というふうに記されていますよね。

返済回数
返済回数は、借入年数に12を掛けた数値です。

トータル何回の返済かを指定します。

現在価値
現在価値は、「月々返済前の時点でのローン残高」という意味です。

③月々返済の利息部分＝IPMT関数

月々返済額のうち、利息部分を表示する場合に使うのが

IPMT関数

です。

IPMT関数は以下の公式で表します。

IPMT関数の項目は

• -IPMT
• 月利
• 期
• 返済回数
• 現在価値

です。

これらは「-PPMT」の部分以外は、上で説明したPPMT関数とまったく同じですが、復習にもなるので説明します。

-IPMT
PPMTは、月々返済額のうち利息部分を表示する際に使う関数です。

表の見やすさを考慮して便宜上頭に-(マイナス)を付けています。

月利
月利は、金利（年利）に12を掛けて月単位の金利を表した数値です。

年一括払いの場合は金利(年利)をそのまま使いますが、住宅ローンの月々返済額の利息部分を確認する際には月利を使用します。

期
期は、毎月の返済が何回目かを表す数値です。

2.住宅ローン繰り上げ返済シミュレーションの関数の使い方例

住宅ローンの繰り上げ返済シミュレーションは、情報欄に

• 金利
• 借入年数
• 借入額
• 返済開始月

を入力することで自動計算されるように作ります。

↓情報欄

見やすさを考慮して1行空けて、

A6〜H6のセルに、以下の８つの項目を作ります。

①期
②返済月
③返済額
④元金
⑤利息
⑥返済前残高
⑦返済後残高
⑧繰上返済額

それぞれの項目の作り方を順番に見ていきましょう。

①期

まず、期の欄は下の画像のように

セルA7とセルA8にそれぞれ「1」「2」を入力し、

両方のセルを選択して420までコピーします。

②返済月

返済月は、セルB8に「=C5」と入力し、

セルB9に月単位でコピーできる数式

を入力します。

黄色の枠内の数式をコピー&ペーストでOKです。

そして、セルB9の右下の角をダブルクリックしましょう。

左隣の「期」の数値がある部分まで自動的にコピーされます。

2051年45までいくはずです。

返済月は、情報欄の「返済開始月」に入力した年月に応じて変化します。

③返済額(PMT関数)

「返済額」のセルC8に以下の数式を入力します。

④元金(PPMT関数)

「元金」のセルD8に以下の数式を入力します。

PPMT関数の前に

• IF関数
• ISERROR関数

が付いていますね。

これは、情報欄の「借入年数」を35年未満にした際に発生するエラーを表示しないための対応です。

⑤利息(IPMT関数)

「利息」のセルE8には以下の数式を入力します。

IPMT関数にもPPMT関数のときと同じように

• IF関数
• ISERROR関数

がありますね。

⑥返済前残高

返済前残高は毎月の返済をする前の時点でのローン残高です。

初回の返済の場合は借入金額がそのまま返済前残高になります。

セルF8には以下の数式を入力します。

 
セルF8の下のF9には、以下の数式を入力します。

G8は、前月の返済後の残高であり、今月の返済前残高となります。

⑦返済後残高

返済後残高は、月々の返済が完了した時点でのローン残高です。

返済後残高のセルG8には以下の数式を入力します(コピペ可)。

返済前の残高から、「返済額の元金部分」と「繰上返済額」を引いた数値です。

⑧繰上返済額

「繰上返済額」欄には何も数式を入れません。

表が完成した際に、繰り上げ返済したい額を手入力することで、他のセルに自動計算結果が表示されるんです。

手入力可能なセルには、薄い黄色などで背景色をつけておくと分かりやすいですよ。

1行目を最下部までコピーして完成

上記①〜⑧で作ったセルを選択し、⑧繰上返済額の右下の角をダブルクリックすることでまとめて最下部までコピーされます。

一気に自動計算セルが数値で埋まりますよ。

この状態で、⑧の繰上返済額欄に、繰り上げ返済したい月のところで金額を入れると返済期間が短縮されます。