住宅ローン返済シミュレーションをエクセルで(2) <PPMT関数とIPMT関数:元金返済額と利息支払い額の計算>

ローン シミュレーション エクセル

Excel関数を使ってのローン返済シミュレーションの第2回です。
今回は、元利均等返済を選択した場合の、月々の支払額に占める、元金と利息の額を求める方法をご紹介します。

元利均等返済は、毎回の返済額が均等となる返済方法

元金均等返済は、元金部分を毎回均等に返済する返済方法

前回の記事でご説明しましたが、元利均等返済時の月々の返済額を求めるにはPMT関数を使います。
では、月々の返済額のうち「元金の返済額」は果たしていくらなのか?
それを求めるのがPPMT関数です。

月々の元金返済額を計算する<PPMT関数>

PPMT関数の書式は、以下のとおりです。

=PPMT ( 月々の金利, 期, 返済回数, 借入金額 )

PMT関数にはなかった、「期」という要素が加わりました。
PPMT関数においては、「期」、つまり何期目の返済なのかを指定する必要があります。

それ以外に注意する点は、PMT関数と同じです。

  1. 金利は、年利ではなく、月々の金利である。
  2. 返済回数は、借入れ年数ではなく、これも月単位である。

このため、金利は、「年利 ÷ 12」としなければなりませんし、返済回数は、「借入れ年数 × 12」としなければなりません。

実際にExcelに入力すると、次のようになります。

【年利】 2%
【返済期間】 35年
【借入金額】 3千万円
【期】 第1回目

ppmt エクセル関数

矢印

ppmt エクセル関数

前回計算したとおり、月々の返済額は99,379円ですから、そのうち元本の返済に充てられるのは、最初の返済では約半分しかありません。残りは利息の支払いに充てられます。

これが20年経つとどうなるか?
計算してみると、こうなります。

ppmt エクセル関数

20年経つと、だいぶ元本の返済に充てられる割合が高くなっていますね。言い換えると、この頃にならないと、あまり元本が減らないとも言えます。なかなか銀行に有利な仕組みですね。

月々の利息支払い額を計算する<IPMT関数>

続いて、元利均等返済時の月々の利息支払い額を求める、IPMT関数をご紹介します。
正直、月々の返済額を求めるPMT関数と、それに占める元金返済額を求めるPPMT関数を知っていれば、引き算すれば済む話なんですが、まあ一応、こんなのもありますよと言うことで。

=IPMT ( 月々の金利, 期, 返済回数, 借入金額 )

先ほど計算した、第1回目の返済時の利息を求めると次のようになります。

【年利】 2%
【返済期間】 35年
【借入金額】 3千万円
【期】 第1回目

ipmt エクセル関数

矢印

ipmt エクセル関数

第1回目の利息支払い額は、50,000円となりました。先ほどの、元本返済額49,379円と合計すると、99,379円。
計算が合いましたね。

次回は、元金均等返済をシミュレーションする方法をご紹介します。

<連載記事>

  1. 住宅ローン返済シミュレーションをエクセルで(1) <PMT関数:ローン返済額の計算>
  2. 住宅ローン返済シミュレーションをエクセルで(2) <PPMT関数とIPMT関数:元金返済額と利息支払い額の計算>
コメント

この記事についてのご感想などをお寄せください。
サイト運営の参考にさせていただきます。
頂いたコメントには、2〜3日以内にメールアドレス宛に回答いたします。(詳細
メールアドレスの入力ミスにご注意ください。
なお、頂いたコメント及びその後のメール等でのやり取りは、この欄でご紹介させていただく場合がございます。