エクセルで住宅ローンを計算する方法!元利・元金均等返済別の求め方

エクセルで住宅ローンを計算する方法!元利・元金均等返済別の求め方

住宅ローンの返済計画を立てるときにシミュレーションツールを利用しますが、エクセルでも簡単に返済額を計算することができます。

こちらでは、住宅ローンの返済方法やエクセルを使った計算方法について、わかりやすく解説しています。

住宅ローン返済額シミュレーションのエクセルファイルも無料で公開していますので、必要な方はダウンロードしましょう。

主な要点
  • 住宅ローンの返済方法
  • 住宅ローンの計算式
  • エクセルの返済額シミュレーションツールについて

ローンの返済方法

住宅ローンの返済方法には、大きく分けて以下の2つがあります。

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

元利均等返済

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

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

毎月の返済額は、毎月一定で住宅ローンの返済計画を立てやすいのが特徴です。

元利均等返済のメリットとデメリットは、以下が挙げられます。

元利均等返済のメリット
  • 返済額が一定のため、住宅ローンの返済計画が立てやすい
  • 返済当初の返済額が元金均等返済よりも少なくすることができる
元利均等返済のデメリット
  • 借入金残高の減り方が遅くなる
  • 同じ借入期間の場合、元金均等返済よりも総返済額が多くなる

元金均等返済

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

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

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

元金均等返済のメリットとデメリットは、以下が挙げられます。

変動金利のメリット
  • 返済が進むと返済額が少なくなっていく
  • 同じ借入期間の場合、元金均等返済よりも総返済額が少なく済む
変動金利のデメリット
  • 返済当初の返済額が高い

元利均等返済の計算式

住宅ローンのシミュレーションで求める項目としては、以下の3つがあります。

  • 毎月の返済額
  • 毎月の元金分
  • 毎月の利息分

元利金等返済では、3つもエクセル関数を利用することで、簡単に計算できます。

それぞれを求めるエクセル関数は以下の3つです。

  • PMT関数
  • PPMT関数
  • IPMT関数

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

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

PMT関数で必要な項目は「月利」「返済回数」「借入金残高」で、以下の計算式で求めます。

PMT関数の計算式=PMT(月利, 返済回数, 借入金残高)

月利

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

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

月利=金利÷12

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

1.2%÷12=0.1%

となります。

返済回数

返済回数は 『毎月支払う回数』のことです。

例えば、返済期間35年の場合は、

35年×12ヶ月=420回

が返済回数となります。

借入金残高

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

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

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

になります。

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

エクセル

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

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

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

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

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

計算式の中では、

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

と、PMTの前に「」が入っています。

これは、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(月利, 回, 返済回数, 借入金残高)

月利・返済回数・借入金残高

月利・返済回数・借入金残高は、PMT関数と内容は同じです。

回は 『何回目の元金分か』というです。

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関数の計算式=PPMT(月利, 回, 返済回数, 借入金残高)

月利・回・返済回数・借入金残高

月利・回・返済回数・借入金残高は、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)

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

元金均等返済の計算式

元金金等返済では、毎月の利息分を計算する場合は、以下のエクセル関数を利用します。

  • ISPMT関数

毎月の元金分を求める

元利均等返済では、毎月の元金分を求めるのに「PPMT関数」を使いましたが、元金均等返済では、特別な関数はありません。

元金均等返済は、元金が毎月一定ですので、単純に

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

借入金額

借入金額とは 『住宅ローンで借りた金額』 のことです。

借入金残高ではなく、借入金額を入力します。

返済回数

返済回数は 『毎月支払う回数』のことです。

例えば、返済期間35年の場合は、

35年×12ヶ月=420回

が返済回数となります。

エクセル

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

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

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

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

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

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

ISPMT関数とは 『元金均等返済の1回あたりの利息分を求める関数』 のことです。

ISPMT関数で必要な項目は「月利」「」「返済回数」「借入金額」で、以下の計算式で求めます。

ISPMT関数の計算式=ISPMT(月利, 回, 返済回数, 借入金額)

月利・返済回数・借入金額

月利・返済回数・借入金額については、これまで説明した内容と同様です。

回は 『何回目の利息分か』というですが、回数の数え方に注意が必要です。

PPMT関数とIPMT関数では、5回目の利息なら「5」と代入しましたが、ISPMT関数は1回目が「0」から始まります。

つまり、5回目の利息分を知りたい場合は「4」を、25回目の利息分を知りたい場合は「24」を代入します。

エクセル

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

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

例えば、1回目の返済額(利息分)を計算する場合は、

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

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

15回目の返済額(利息分)を計算する場合は、

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

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

毎月の返済額を求める

元金と利息が求められていますので、毎月の返済額は以下の計算式で簡単に算出できます。

元金均等返済毎月返済額の計算式=指定月の元金分+指定月の利息分

エクセル

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

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

こちらの例で、1回目の元金分は「71,429円」、利息分は「27,750」のため、毎月返済額を求めると

=72,429+27,750

で、「99,179円」となります。

エクセルの返済額シミュレーションツール

住宅ローンをシミュレーションする際に返済予定表を作成します。

返済予定表とは 『住宅ローンの毎月の返済予定がまとめられた表』 のことで、償還予定表(償還表)とも呼ばれます。

住宅ローンの返済額シミューレーションでは、エクセルを利用すると簡単に数値を変更できて、とても便利です。

借入条件を入力すると「元利均等返済」と「元金均等返済」の返済予定表をチェックできる、シミュレーションツールをエクセルを作成しましたので、以下より無料でダウンロードできます。

住宅ローン返済額シミュレーションツール(Excel)

元利均等返済」と「元金均等返済」の2シートあります。

以下の借入情報を入力すると借入返済予定表に毎月の情報が表示されます。

  • 返済開始日
  • 購入金額
  • 頭金
  • ボーナス予定返済額(1回分)
  • ボーナス予定返済回数
  • 借入期間
  • 金利(年利)

■借入返済予定表の項目

借入返済予定表は以下の項目を確認できます。

  • 年数(年目)
  • 返済年月
  • 返済回数
  • 毎月返済額
  • 元金
  • 利息
  • 借入金残高

■ボーナス返済について

ボーナス返済を利用する場合は、「ボーナス予定返済額」「ボーナス予定返済回数」を入力すると、毎月返済額をシミュレーションできます。

Size:276KB
Version:1.0.0

返済額シミュレーションツールの使い方

まず、エクセルファイルを開くと、以下の2つのシートがあります。

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

住宅ローン返済額シミュレーションツールのシート

どちらかの返済方法のシートを選択します。

住宅ローン返済額シミュレーションツールの入力項目

次に入力項目は、以下の7項目です。

  • 返済開始日
  • 購入金額
  • 頭金
  • ボーナス予定返済額(1回分)
  • ボーナス予定返済回数(n)
  • 借入期間
  • 金利(年利)

返済開始日

返済開始日には、いつから返済を開始するか日付を入力します。

購入金額

購入金額は、住宅ローンの対象となる不動産の購入価格を入力します。

頭金

頭金は、自分で用意できる資金を入力します。

ボーナス予定返済額(1回分)

ボーナス予定返済額は、ボーナス時に返済できる1回分の金額を入力します。

ボーナス返済をしない場合は、入力不要です。

ボーナス予定返済回数(n)

ボーナス予定返済回数は、ボーナスの回数になりますが、こちらのエクセルファイルでは「1」「2」のいずれかしか入力できません。

こちらもボーナス返済をしない場合は、入力不要です。

借入期間

借入期間は、住宅ローンの借入期間を年単位で入力します。

金利(年利)

金利(年利)は、住宅ローンを借りる金融機関などの金利を入力します。

シミュレーションする場合は、[金利情報 ≫]をクリックすると、フラット35の最新の金利情報ページが開きます。

最新の金利情報を参考にしましょう。

これらの情報を入力すると毎月の返済額や元金・利息をチェックすることができます。

まとめ

ここまで、住宅ローンの返済方法やエクセルを使った計算方法について、わかりやすく解説しました。

住宅ローンの返済計画を立てるときにシミュレーションツールを利用すると、エクセルでも簡単に返済額を計算することができます。

住宅ローンの毎月返済額を求める場合は、エクセル以外にも「返済額早見表」を使って求めることもできます。

返済額早見表から返済額を計算する方法については住宅ローンの計算方法は?金利と利息の仕組みを分かりやすく解説 】をご確認ください。

 自分で抵当権抹消手続きを考えている方へ

住宅ローンを完済したら抵当権抹消登記をする必要があります。抵当権の抹消は司法書士に依頼できますが、自分ですることも可能です。こちらでは、申請書の書き方や抵当権抹消の流れについて、わかりやすく解説しています。

抵当権抹消登記申請書の詳しい書き方を知りたい方へ

抵当権抹消登記申請書は、自分で作成しても問題ありません。こちらでは、抵当権抹消登記申請書のダウンロード方法や項目ごとの書き方、必要な書類の説明まで、わかりやすく解説しています。実際の記入例もありますので、書き方が分からない方は参考にしてください。

 売買による登録免許税と計算方法を知りたい方へ

計算方法や軽減措置の要件、軽減税率をわかりやすく解説。不動産登記と登録免許税の関係、課税される登記の種類と税率、事例を用いた計算例を要点をまとめています。

公衆用道路を購入したときの登録免許税の計算方法を知りたい方へ

公衆用道路が0円の登録免許税は非課税ではありません。公衆用道路の課税価格は近傍宅地単価から求めます。公衆用道路とはどのようなものか、公衆用道路の登録免許税の計算方法について分かりやすく解説。

 相続による登録免許税と計算方法を知りたい方へ

相続による登録免許税には軽減措置はありませんが、免税措置があります。相続登記と登録免許税の関係、事例を用いた計算例、免税措置について要点をまとめています。

 相続による登録免許税の免税措置を知りたい方へ

相続登記による登録免許税には2つの免税措置があります。それは、一次相続人が土地の相続登記をしていない場合と土地価格が10万円以下の場合で免除されます。こちらでは、相続登記による登録免許税の免税措置について、わかりやすく解説していきます

 不動産に関する税金について知りたい方へ

不動産の取得・売却・相続・贈与には、さまざなな税金がかかります。「不動産に係る税金の基礎知識入門書」では、それぞれに該当する必要な税金についてまとめていますので、気になる方は確認しましょう。

コメント

タイトルとURLをコピーしました