やまでら くみこ のレシピ

短時間で簡単に作れる人気家庭料理を紹介。

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

投稿日:2013年6月16日 更新日:

ローン シミュレーション エクセル
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関数:元金返済額と利息支払い額の計算>

おすすめ記事

【文字数カウント】いちどに複数の文章をチェックできるWebアプリ。

文字数カウンター/文字数チェッカーを作りましたので、サイト運営に役立て…

「ヘロンの公式」計算機。三角形の面積と高さを、3辺の長さから計算します。

「ヘロンの公式」計算機は、三角形の面積と高さをWeb上でカンタンに計算…

台形の面積 計算機。基本公式と高さが分からないときの求め方。

「台形の面積」計算機は、台形の面積をWeb上でカンタンに計算できる電卓…

写真の位置情報を確認するツール「Exif-GPS」。iPhone、Android、PC対応。

「Exif-GPS」は、iPhoneやAndroidなどのスマホで撮っ…

Macでデスクトップを表示する2つの方法。キーボードショートカットとトラックパッドで簡単操作。

マックでデスクトップ画面を表示する方法をご紹介します。ウィンドウズなら…

wordpress

WordPressをブルートフォースアタックから守るために、最低限やっておきたい.htaccessの設定

世界中で頻発する、WordPressを狙った不正なアタック。その手口の…