エクセルで住宅ローン計算!元利均等と元金均等で返済額をシミュレーション!

エクセルで住宅ローン計算!元利均等返済と元金均等返済の「返済額」「元金」「利息」の計算方法

【エクセルの住宅ローン返済予定表テンプレート無料配布中!】

エクセルで住宅ローンを計算する場合、「返済額」「元金」「利息」を求める関数を使うと、簡単に住宅ローンのシミュレーションができます

元利均等返済と元金均等返済では、利用する関数が異なりますので、注意しましょう。

こちらでは、エクセルを使って元利均等返済と元金均等返済の「返済額」「元金」「利息」の計算方法について、わかりやすく解説しています。

主な要点
  • 元利均等返済と元金均等返済の比較
  • 元利均等返済の「PMT」「PPMT」「IPMT」関数の使い方
  • 元金均等返済の「ISPMT」関数の使い方
  • 【無料配布】エクセルの住宅ローン返済予定表テンプレート

住宅ローンの返済方法

住宅ローンの返済方法には、以下の2つがあります。

  • 元利均等返済
  • 元金均等返済

元利均等返済と元金均等返済の比較

こちらは、元利均等返済と元金均等返済のメリット・デメリットを比較したものです。

住宅ローン返済方法のメリット・デメリット
元利均等返済方式元金均等返済方式
返済方法元金分と利息分を合わせた金額(返済額)が毎月一定となる返済方法元金分を毎月均等にして、利息と合わせて返済する方法
返済イメージ元利均等返済の返済イメージ元金均等返済の返済イメージ
メリット・毎月返済額が一定で、返済計画が立てやすい
・返済開始当初は、元金均等返済より返済額が少ない
・返済が進むにつれて、毎月返済額が少なくなる
・同条件で、元利均等返済より総返済額が少ない
デメリット・同条件で、元金均等返済より総返済額が多い
・元金の減り方が遅く、利息が高い
・返済開始当初の返済額が多い
・借入可能額が少なくなる

返済イメージを見て分かるように、毎月の返済額に違いがあります。

つまり、元利均等返済と元金均等返済では、計算式が異なるということです。

そこで、エクセルでは、それぞれの「元金」「利息」「返済額」を求める関数が用意されています。

それぞれのエクセル関数の使い方について、詳しく解説していきます。

元利均等返済のエクセル関数

元利均等返済は毎月の返済額が一定なのが特徴

元利均等返済とは 『元金分と利息分を合わせた毎月の返済額が一定となる返済方法』のことです。

元金とは土地や住宅を購入するために金融機関などから借り入れた金額のこと。
icon-book 元金とは?元利金とは?元利均等返済と元金均等返済の違い

毎月の元金分と利息分を調整して、毎月の返済額が一定となるのが特徴です。

元利均等返済で使うエクセル関数

元利均等返済で使うエクセル関数は、以下の3つです。

  • PMT関数・・・毎月の返済額を計算
  • PPMT関数・・・毎月の元金分を計算
  • IPMT関数・・・毎月の利息分を計算

毎月の返済額を求めるPMT関数

PMT関数とは 『一定利率で1回あたりの返済額を求める関数』 のことです。

元利均等返済の毎月返済額を求める場合は、PMT関数を利用します。

PMT関数の計算式は、以下の通りです。

PMT関数の計算式=PMT(利率, 期間, 現在価値, [将来価値], [支払期日])
PMT関数の項目(引数)
項目(引数)詳細
利率(必須)金融機関の利率を指定
期間(必須)住宅ローン返済期間の返済回数合計を指定
※「利率」と同じ単位を指定しなければいけません。
年利1.2%→10」「月利0.1%→120ヶ月
現在価値(必須)住宅ローンの借入金額を指定
将来価値(省略可)住宅ローン返済では、「0」を指定
※省略すると「0」で処理されます。
支払期日(省略可)支払いを「各期の期末(0)」か「各期の期首(1)」を指定
※省略すると「0」の各期の期末で処理されます。

毎月の返済額を計算する場合は、「利率」「期間」「現在価値」の3つを入力すれば、求められます。

利率

利率には、毎年の返済額を求めるなら「年利」を、毎月の返済額を求めるなら「月利」を入力します。

月利とは 『借入金額に対して月単位でかかる金利』 のことです。

住宅ローンの金利は、年単位の年利で記載されていますので、月利に変換するには、12ヶ月で割ります。

月利=金利÷12

例えば、金利1.2%の月利は、

1.2%÷12=0.1%

となります。

期間

期間には、住宅ローン返済期間の返済回数合計を入力します。

「期間」と「利率」は、同じ単位を指定しなければいけません。

例えば、利率に年利1.2%を入力したら返済回数は10(年)、利率に月利0.1%を入力したら返済回数は120(ヶ月)となります。

10年×12ヶ月=120回

現在価値

現在価値には、住宅ローンの借入金額を入力します。

借入金残高は 『返済済み元金を差し引いた借入金』のことです。

例えば、借入金3,000万円で58,000円を返済したら、借入金残高は、

30,000,000円-58,000円=29,942,000円

になります。

ちなみに返済1回目は、まだ返済していませんので、借入金残高は「3,000万円」となります。

将来価値

将来価値は、省略可能項目で入力不要です。

将来価値には、最後の支払いが終わった後の現金を指定しますが、住宅ローン返済は支払いが完了すると将来価値は「0」です。

省略した場合は、「0」で処理されますので、入力しなくても問題ありません。

支払期日

支払期日は、省略可能項目で入力不要です。

支払期日には、「各期の期末(0)」か「各期の期首(1)」の支払期日を指定しますが、通常は期末払いのため「0」を指定します。

例えば、1月の期末払いであれば1月31日、期首払いであれば1月1日が、支払期日となります。

省略した場合は、「0」で処理されますので、入力しなくても問題ありません。

ちなみに、期首払いは、利息がかかる前に1回目の返済を行うため、期末払いよりも若干返済総額が少なくなります。

エクセルの計算式

毎月の返済額を求めるPMT関数

エクセルでは、このように該当するセルを参照させます。

例えば、「借入金額:3,000万円」「借入期間:35年」「金利(年利)1.110%」で計算式に代入すると

=-PMT(0.011/12, 35*12, 30,000,000)

で、毎月の返済額「86,232円」が求められます。

計算式の注意点

計算式の注意点として、以下のようにPMTの前に「-(マイナス)」が入っています。

=PMT(0.011/12, 35*12, 30,000,000)

PMT関数は、返済額を手元から出て行くお金と判断して、「-」で表示されます

マイナス表示は見づらいため、「-」に「-」をかけて、プラスに変換しています。

他にも「ABS関数」で絶対値で表示させる方法もありますが、「-」をつけたほうが早いです。

ボーナス返済

PMT関数は、1回あたりの返済額を求める関数ですので、年間返済額やボーナス返済の返済額を求めたい場合は、「年利」で計算しても問題ありません。

例えば、「借入金額(ボーナス返済分):560万円」「借入期間:35年」「金利(年利)1.110%」「年1回のボーナス返済」を考えている場合は、

=-PMT(0.011, 35, 5,600,000)

で、ボーナス返済額「96,763円」が求められます。

もしも、年2回ボーナス返済を検討する場合は、

=-PMT(0.011/2, 35*2, 5,600,000)

とエクセルの計算式を変更すれば、回数に応じてボーナス返済額を求めることができます

毎月の元金分を求めるPPMT関数

PPMT関数とは 『一定利率で1回あたりの元金分を求める関数』 のことです。

元利均等返済の毎月元金分を求める場合は、PPMT関数を利用します。

PPMT関数の計算式は、以下の通りです。

PPMT関数の計算式=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」を入力します。

エクセルの計算式

毎月の元金分を求めるPPMT関数

エクセルでは、このように該当するセルを参照させます。

例えば、1回目の元金分を計算する場合は、

=-PPMT(0.011/12, 1, 35*12, 30,000,000)

で、「58,482円」が求められます。

15回目の元金分を計算する場合は、

=-PPMT(0.011/12, 15, 35*12, 30,000,000)

と、「期」を変えていけば、知りたい期の元金分を計算できます。

毎月の利息分を求めるIPMT関数

IPMT関数とは 『一定利率で1回あたりの利息分を求める関数』 のことです。

元利均等返済の利息分を求める場合は、IPMT関数を利用します。

IPMT関数の計算式は、以下の通りです。

IPMT関数の計算式=IPMT(利率, 期, 期間, 現在価値, [将来価値], [支払期日])
IPMT関数の項目(引数)
項目(引数)詳細
利率(必須)金融機関の利率を指定
期(必須)住宅ローン返済期間のうち何回目かを指定
※「利率」と同じ単位を指定しなければいけません。
年利1.2%→10」「月利0.1%→120ヶ月
期間(必須)住宅ローン返済期間の返済回数合計を指定
※「利率」と同じ単位を指定しなければいけません。
年利1.2%→10」「月利0.1%→120ヶ月
現在価値(必須)住宅ローンの借入金額を指定
将来価値(省略可)住宅ローン返済では、「0」を指定
※省略すると「0」で処理されます。
支払期日(省略可)支払いを「各期の期末(0)」か「各期の期首(1)」を指定
※省略すると「0」の各期の期末で処理されます。

毎月の利息分を計算する場合は、「利率」「」「期間」「現在価値」の4つを入力すれば、求められます。

利率・期・期間・現在価値・将来価値・支払期日

利率・期・期間・現在価値・将来価値・支払期日は、すべてPPMT関数と内容は同じです

エクセルの計算式

毎月の利息分を求めるIPMT関数

エクセルでは、このように該当するセルを参照させます。

例えば、1回目の利息分を計算する場合は、

=-IPMT(0.011/12, 1, 35*12, 30,000,000)

で、「27,750円」が求められます。

30回目の利息分を計算する場合は、

=-IPMT(0.011/12, 30, 35*12, 30,000,000)

と、回数を変えていけば、知りたい回数の利息分を計算できます。

計算式の注意点

IPMT関数もPMT関数と同じで、結果が「-(マイナス)」になります。

計算式に入力する際は、「-」をつけましょう。

=IPMT(0.011/12, 30, 35*12, 30,000,000)

PMT・PPMT・IPMT関数のうち使うのは2つだけ!

エクセル関数を使って、元利均等返済の「返済額」「元金」「利息」の計算方法を説明しましたが、エクセルで住宅ローンの返済予定表を作る場合は、PMT・PPMT・IPMT関数のうち2つだけを使ってください

なぜかというと、それぞれ計算した場合に端数処理で誤差が生じます。

元利均等返済でPMT・PPMT・IPMT関数を使うと端数で誤差が生じる

住宅ローンの返済額は、「元金+利息」で求められますが、例えば、3回目の元金と利息を合わせると、

58,591+27,642=86,233

と、PMT関数で計算した「86,232」よりも1円多く、計算が合いません

セル上では、小数点以下を表示させていませんが、PMT・PPMT・IPMT関数の計算結果は、小数第10位まで値があります。

そのため、金額を合わせるには、ROUND・ROUNDDOWN・ROUNDUP関数のいずれかで「四捨五入・切捨て・切り上げ」をしてから整数にしましょう

切り捨てには、ROUNDDOWN関数のほかにINT関数がありますが、INT関数はマイナス値で切り上げになる場合がありますので、利用しないほうがよいです。

=ROUND(86232.344,0) → 86,232

更に、「返済額」「元金」「利息」のいずれかを、足し算あるいは引き算で求めると誤差が生じません

返済額=元金+利息
元金=返済額-利息
利息=返済額-元金

元金均等返済のエクセル関数

元金均等返済は元金の返済額が毎月一定なのが特徴

元金均等返済とは 『元金分を毎月均等に返済し、利息を含めて返済する方法』のことです。

毎月の返済額は、返済当初が最も高く、返済が進むと返済額が少なくなるのが特徴です。

 

元金均等返済で使うエクセル関数

元金均等返済で使うエクセル関数は、以下の1つです。

  • ISPMT関数・・・n回目の利息分を計算

毎月の返済額と元金分は、四則計算で求められます。

毎月の元金分を求める「÷」

元利均等返済の元金分を求める際に「PPMT関数」を利用しましたが、元金均等返済は、元金が一定のため以下の計算式で簡単に求められます。

元金均等返済元金分の計算式=借入金額/返済回数

エクセルの計算式

元金均等返済の毎月の元金分を求める計算式

エクセルでは、このように該当するセルを参照させます。

こちらの例では、「借入金額3,000万円」「借入期間35年」の場合、