【エクセルの住宅ローン返済予定表テンプレート無料配布中！】
エクセルで住宅ローンを計算する場合、「返済額」「元金」「利息」を求める関数を使うと、簡単に住宅ローンのシミュレーションができます。
元利均等返済と元金均等返済では、利用する関数が異なりますので、注意しましょう。
こちらでは、エクセルを使って元利均等返済と元金均等返済の「返済額」「元金」「利息」の計算方法について、わかりやすく解説しています。
- 元利均等返済と元金均等返済の比較
- 元利均等返済の「PMT」「PPMT」「IPMT」関数の使い方
- 元金均等返済の「ISPMT」関数の使い方
- 【無料配布】エクセルの住宅ローン返済予定表テンプレート
住宅ローンの返済方法
住宅ローンの返済方法には、以下の2つがあります。
- 元利均等返済
- 元金均等返済
元利均等返済と元金均等返済の比較
こちらは、元利均等返済と元金均等返済のメリット・デメリットを比較したものです。
| 元利均等返済方式 | 元金均等返済方式 | |
|---|---|---|
| 返済方法 | 元金分と利息分を合わせた金額(返済額)が毎月一定となる返済方法 | 元金分を毎月均等にして、利息と合わせて返済する方法 |
| 返済イメージ |  |  |
| メリット | ・毎月返済額が一定で、返済計画が立てやすい ・返済開始当初は、元金均等返済より返済額が少ない | ・返済が進むにつれて、毎月返済額が少なくなる ・同条件で、元利均等返済より総返済額が少ない |
| デメリット | ・同条件で、元金均等返済より総返済額が多い ・元金の減り方が遅く、利息が高い | ・返済開始当初の返済額が多い ・借入可能額が少なくなる |
返済イメージを見て分かるように、毎月の返済額に違いがあります。
つまり、元利均等返済と元金均等返済では、計算式が異なるということです。
そこで、エクセルでは、それぞれの「元金」「利息」「返済額」を求める関数が用意されています。
それぞれのエクセル関数の使い方について、詳しく解説していきます。
元利均等返済のエクセル関数
元利均等返済とは 『元金分と利息分を合わせた毎月の返済額が一定となる返済方法』のことです。
毎月の元金分と利息分を調整して、毎月の返済額が一定となるのが特徴です。
元利均等返済で使うエクセル関数
元利均等返済で使うエクセル関数は、以下の3つです。
- PMT関数・・・毎月の返済額を計算
- PPMT関数・・・毎月の元金分を計算
- IPMT関数・・・毎月の利息分を計算
毎月の返済額を求めるPMT関数
PMT関数とは 『一定利率で1回あたりの返済額を求める関数』 のことです。
元利均等返済の毎月返済額を求める場合は、PMT関数を利用します。
PMT関数の計算式は、以下の通りです。
| 項目（引数） | 詳細 |
|---|---|
| 利率（必須） | 金融機関の利率を指定 |
| 期間（必須） | 住宅ローン返済期間の返済回数合計を指定 ※「利率」と同じ単位を指定しなければいけません。 「年利1.2％→10年」「月利0.1％→120ヶ月」 |
| 現在価値（必須） | 住宅ローンの借入金額を指定 |
| 将来価値（省略可） | 住宅ローン返済では、「0」を指定 ※省略すると「0」で処理されます。 |
| 支払期日（省略可） | 支払いを「各期の期末（0）」か「各期の期首（1）」を指定 ※省略すると「0」の各期の期末で処理されます。 |
毎月の返済額を計算する場合は、「利率」「期間」「現在価値」の3つを入力すれば、求められます。
利率
利率には、毎年の返済額を求めるなら「年利」を、毎月の返済額を求めるなら「月利」を入力します。
月利とは 『借入金額に対して月単位でかかる金利』 のことです。
住宅ローンの金利は、年単位の年利で記載されていますので、月利に変換するには、12ヶ月で割ります。
例えば、金利1.2％の月利は、
となります。
期間
期間には、住宅ローン返済期間の返済回数合計を入力します。
「期間」と「利率」は、同じ単位を指定しなければいけません。
例えば、利率に年利1.2％を入力したら返済回数は10(年)、利率に月利0.1％を入力したら返済回数は120(ヶ月)となります。
現在価値
現在価値には、住宅ローンの借入金額を入力します。
借入金残高は 『返済済み元金を差し引いた借入金』のことです。
例えば、借入金3,000万円で58,000円を返済したら、借入金残高は、
になります。
ちなみに返済1回目は、まだ返済していませんので、借入金残高は「3,000万円」となります。
将来価値
将来価値は、省略可能項目で入力不要です。
将来価値には、最後の支払いが終わった後の現金を指定しますが、住宅ローン返済は支払いが完了すると将来価値は「0」です。
省略した場合は、「0」で処理されますので、入力しなくても問題ありません。
支払期日
支払期日は、省略可能項目で入力不要です。
支払期日には、「各期の期末（0）」か「各期の期首（1）」の支払期日を指定しますが、通常は期末払いのため「0」を指定します。
例えば、1月の期末払いであれば1月31日、期首払いであれば1月1日が、支払期日となります。
省略した場合は、「0」で処理されますので、入力しなくても問題ありません。
ちなみに、期首払いは、利息がかかる前に1回目の返済を行うため、期末払いよりも若干返済総額が少なくなります。
エクセルの計算式
エクセルでは、このように該当するセルを参照させます。
例えば、「借入金額：3,000万円」「借入期間：35年」「金利（年利）1.110％」で計算式に代入すると
で、毎月の返済額「86,232円」が求められます。
計算式の注意点
計算式の注意点として、以下のようにPMTの前に「－（マイナス）」が入っています。
PMT関数は、返済額を手元から出て行くお金と判断して、「－」で表示されます。
マイナス表示は見づらいため、「－」に「－」をかけて、プラスに変換しています。
他にも「ABS関数」で絶対値で表示させる方法もありますが、「－」をつけたほうが早いです。
ボーナス返済
PMT関数は、1回あたりの返済額を求める関数ですので、年間返済額やボーナス返済の返済額を求めたい場合は、「年利」で計算しても問題ありません。
例えば、「借入金額（ボーナス返済分）：560万円」「借入期間：35年」「金利（年利）1.110％」「年1回のボーナス返済」を考えている場合は、
で、ボーナス返済額「96,763円」が求められます。
もしも、年2回ボーナス返済を検討する場合は、
とエクセルの計算式を変更すれば、回数に応じてボーナス返済額を求めることができます。
毎月の元金分を求めるPPMT関数
PPMT関数とは 『一定利率で1回あたりの元金分を求める関数』 のことです。
元利均等返済の毎月元金分を求める場合は、PPMT関数を利用します。
PPMT関数の計算式は、以下の通りです。
| 項目（引数） | 詳細 |
|---|---|
| 利率（必須） | 金融機関の利率を指定 |
| 期（必須） | 住宅ローン返済期間のうち何回目かを指定 ※「利率」と同じ単位を指定しなければいけません。 「年利1.2％→10年」「月利0.1％→120ヶ月」 |
| 期間（必須） | 住宅ローン返済期間の返済回数合計を指定 ※「利率」と同じ単位を指定しなければいけません。 「年利1.2％→10年」「月利0.1％→120ヶ月」 |
| 現在価値（必須） | 住宅ローンの借入金額を指定 |
| 将来価値（省略可） | 住宅ローン返済では、「0」を指定 ※省略すると「0」で処理されます。 |
| 支払期日（省略可） | 支払いを「各期の期末（0）」か「各期の期首（1）」を指定 ※省略すると「0」の各期の期末で処理されます。 |
毎月の元金分を計算する場合は、「利率」「期」「期間」「現在価値」の4つを入力すれば、求められます。
利率・期間・現在価値・将来価値・支払期日
利率・期間・現在価値・将来価値・支払期日は、すべてPMT関数と内容は同じです。
期
期には、住宅ローン返済期間のうち何回目かを入力します。
例えば、返済期間35年（420回）であれば、期は「1～420」のいずれかで、5回目の元金分を計算したい場合は「5」を、23回目の元金分を計算したい場合は「23」を入力します。
エクセルの計算式
エクセルでは、このように該当するセルを参照させます。
例えば、1回目の元金分を計算する場合は、
で、「58,482円」が求められます。
15回目の元金分を計算する場合は、
と、「期」を変えていけば、知りたい期の元金分を計算できます。
毎月の利息分を求めるIPMT関数
IPMT関数とは 『一定利率で1回あたりの利息分を求める関数』 のことです。
元利均等返済の利息分を求める場合は、IPMT関数を利用します。
IPMT関数の計算式は、以下の通りです。
| 項目（引数） | 詳細 |
|---|---|
| 利率（必須） | 金融機関の利率を指定 |
| 期（必須） | 住宅ローン返済期間のうち何回目かを指定 ※「利率」と同じ単位を指定しなければいけません。 「年利1.2％→10年」「月利0.1％→120ヶ月」 |
| 期間（必須） | 住宅ローン返済期間の返済回数合計を指定 ※「利率」と同じ単位を指定しなければいけません。 「年利1.2％→10年」「月利0.1％→120ヶ月」 |
| 現在価値（必須） | 住宅ローンの借入金額を指定 |
| 将来価値（省略可） | 住宅ローン返済では、「0」を指定 ※省略すると「0」で処理されます。 |
| 支払期日（省略可） | 支払いを「各期の期末（0）」か「各期の期首（1）」を指定 ※省略すると「0」の各期の期末で処理されます。 |
毎月の利息分を計算する場合は、「利率」「期」「期間」「現在価値」の4つを入力すれば、求められます。
利率・期・期間・現在価値・将来価値・支払期日
利率・期・期間・現在価値・将来価値・支払期日は、すべてPPMT関数と内容は同じです。
エクセルの計算式
エクセルでは、このように該当するセルを参照させます。
例えば、1回目の利息分を計算する場合は、
で、「27,750円」が求められます。
30回目の利息分を計算する場合は、
と、回数を変えていけば、知りたい回数の利息分を計算できます。
計算式の注意点
IPMT関数もPMT関数と同じで、結果が「－（マイナス）」になります。
計算式に入力する際は、「－」をつけましょう。
PMT・PPMT・IPMT関数のうち使うのは2つだけ！
エクセル関数を使って、元利均等返済の「返済額」「元金」「利息」の計算方法を説明しましたが、エクセルで住宅ローンの返済予定表を作る場合は、PMT・PPMT・IPMT関数のうち2つだけを使ってください。
なぜかというと、それぞれ計算した場合に端数処理で誤差が生じます。
住宅ローンの返済額は、「元金+利息」で求められますが、例えば、3回目の元金と利息を合わせると、
と、PMT関数で計算した「86,232」よりも1円多く、計算が合いません。
セル上では、小数点以下を表示させていませんが、PMT・PPMT・IPMT関数の計算結果は、小数第10位まで値があります。
そのため、金額を合わせるには、ROUND・ROUNDDOWN・ROUNDUP関数のいずれかで「四捨五入・切捨て・切り上げ」をしてから整数にしましょう。
切り捨てには、ROUNDDOWN関数のほかにINT関数がありますが、INT関数はマイナス値で切り上げになる場合がありますので、利用しないほうがよいです。
更に、「返済額」「元金」「利息」のいずれかを、足し算あるいは引き算で求めると誤差が生じません。
元金均等返済のエクセル関数
元金均等返済とは 『元金分を毎月均等に返済し、利息を含めて返済する方法』のことです。
毎月の返済額は、返済当初が最も高く、返済が進むと返済額が少なくなるのが特徴です。
元金均等返済で使うエクセル関数
元金均等返済で使うエクセル関数は、以下の1つです。
- ISPMT関数・・・n回目の利息分を計算
毎月の返済額と元金分は、四則計算で求められます。
毎月の元金分を求める「÷」
元利均等返済の元金分を求める際に「PPMT関数」を利用しましたが、元金均等返済は、元金が一定のため以下の計算式で簡単に求められます。
エクセルの計算式
エクセルでは、このように該当するセルを参照させます。
こちらの例では、「借入金額3,000万円」「借入期間35年」の場合、