住宅ローンの元利均等返済をExcelで再現すると

ツール

今回は、住宅ローンの元利均等返済をExcelで計算するツールを紹介します。Excelには標準関数としてPMT関数(利率が一定であると仮定した場合の元利均等の毎月の支払額を計算する関数)があります。計算ロジックの説明をメインとして行うため、この関数の再現も交えてやってみました。

住宅ローン返済シミュレーション

住宅ローンの返済シミュレーションは銀行ホームページ等に行けばいくらでも見つけることができます。

楽天銀行

お借入希望額から月々の返済額をシミュレーション|住宅ローン|楽天銀行
お客さまのご希望のお借入金額と返済期間、ボーナス時に追加でお支払いいただく金額(任意設定)から、楽天銀行住宅ローン(金利選択型)/フラット35/「固定と変動」の毎月の返済額を試算できます。

じぶん銀行

変動金利選択/通期引下げプラン | 住宅ローン | auじぶん銀行
auじぶん銀行の住宅ローンシミュレーションでは、毎月の返済額や現在の年収からの借入可能額、お借換えのメリットなど、簡単にシミュレーションが可能です。住宅ローンの新規お借入れをご検討の方、お借換えをご検討の方、まずはこちらのシミュレーションにてお試しください。また、繰上返済についても計算することも可能です。auじぶん銀行...

住信SBIネット銀行

住宅ローン - お返済額試算|住信SBIネット銀行
住宅ローンシミュレーションによって金利の計算や借り入れ額を試算できます。住信SBIネット銀行では返済額、借入額、繰上返済、金利切替についてシミュレーションが可能です。

住宅保証機構

住宅ローンシミュレーション

で、どうやって計算してんの?ということですが、一般的な元利均等返済方式について調べるとExcelのPMT関数に関する解説が多いです。

PMT関数

PMT関数の中身は元利均等返済の公式をそのまま計算しているだけであるが、気になったので再現してみました。

公式の導出はこちらがわかりやすいです。

元利均等返済と元金均等返済の意味と計算式 - 具体例で学ぶ数学
元利均等返済とは、毎回支払う金額が一定となるような返済方法です。元金均等返済(がんきんきんとうへんさい)とは、毎回支払う金額のうち、元金の部分が一定となるような返済方法です。

Wikipediaの式も上記と同じ

元利均等返済 - Wikipedia

こちらは分子分母をで除しているが意味するところは同じなのでこちらをExcelで再現してみました。最下部のExcelファイルにあるPMTシートを参照してください。

SFDC:数式項目でExcelのPMT関数と同じ計算を行う方法 - tyoshikawa1106のブログ
Success Communityで紹介されていた数式項目の活用例です。ExcelのPMT関数のように住宅ローンの計算を行う方法です。 Principal__c/((1-(1+( Rate__c /12)) ^ ((-1)* ABS(Term__c) ))/(Rate__c/12)) 項目はこんな感じ。 PMT関数につ...

返済スケジュールをExcelで再現

PMT関数の仕組みが分かったので実際に住宅ローンの返済スケジュールを計算してみます。

Memoシートにも記載しているが概要と前提は次の通りです。

◆概要

  • 前提条件の下での住宅ローン返済スケジュールを計算するファイル
  • 黄色ハイライトセルに、「借入金額」「返済期間」「金利」を入力
  • 「金利」は月ごとに異なっていても計算可能

◆前提条件

  • 返済方式は元利均等返済方式
  • 端数は切り捨て、最終返済時にまとめて返済
  • ボーナス返済なし
  • 繰り延べ返済なし

ツールのダウンロードリンク

↓ツールのダウンロードは下のリンクから!

drive.google.com

※本ブログに記載のツールの仕様により発生した損失・損害については一切責任を負いませんので、ご自身のご判断でお使いいただければと思います。

コメント

運営者

データ分析やモデル等の仕事をしてる人々。週に1回程度のペースで金融や統計に関する記事を更新しています。
C++/Python/R/SAS/HTML/VBA/PHP/SQL

naruseをフォローする
タイトルとURLをコピーしました