生活・暮らし

エクセルの関数「PMT」「IPMT」「PPMT」住宅ローンのシュミレーションに便利。

更新日:

今は宅地分譲を探してハウスメーカーや工務店で住宅の注文建築を計画しています。

そんな中、やっぱり気になるのが毎月の住宅ローンの返済額です。

住宅ローン シュミレーション」で検索をすると便利なサイトの情報がありますが、支払い額や利息分の計算はエクセルの関数を使うことでもできます。

元利均等の計算で主に使う関数は、PMTIPMTPPMT、です。

エクセルで表すことで住宅購入の計画の妥当性が見えたりするのでかなり参考になります。

(SPONSORED LINK)


PTM関数で毎月の支払額を計算

PTM関数は現金と利息の合計の毎月の支払額を計算することができます。

関数の式は、=PMT(利率, 期間, 現在価値,将来価値, 支払期日) です。

A B C
1 融資額(円) 30,000,000 現在価値
2 年利(%) 0.6 利率
3 返済期間 35 期間

上の表のように値が入っていると関数の式は以下のようになります。
=PMT(B2/12,B3*12,B1,,)

この時の条件の結果は、¥-79,209となります。毎月の住宅ローンの支出金額(元金と利息の合計)になります。。

関数で気を付けておきたいことは、利率は年間分になっているので、月額の計算にするために12で割ることが必要です。年間の利息を12分の1にすることで一か月分にします。

期間は支払回数を示しています。年額一括払いであれば35回でもいいですが、35年ローンは420回払いなので、期間に年間の月数の12を掛け算します。

利率についても注意が必要です。年利が0.6%だからといってエクセルのセルに0.6と入力しても書式設定はデフォルトでは標準になっていると思います。書式設定の標準の値の0.6をパーセント(%)に変更すると60%になってしまうので、計算結果の金額は莫大になります。書式設定でパーセンテージに変更しておいたほうが惑わされにくいです。

エクセル書式設定-パーセンテージ

現在価値は借り入れの融資額の数値をいるだけです。将来価値, 支払期日については必然的にデフォルト値が入るので何も入力しなくても大丈夫です。

これで住宅ローンの月額の金額を算出することができます。

参考:PMT関数

IPMT関数で毎月の利息の金額を計算

PMT関数では元金と利息を合わせた金額を計算できますが、IPMT関数利息分の金額を算出することができます。

こうすることで、住宅ローンの金利がいかに高いのかを感じることができるのではないでしょうか。

関数の数式は、IPMT(利率, 期, 期間,現在価値, 将来価値, 支払期日)

A B C
1 融資額(円) 30,000,000 現在価値
2 年利(%) 0.6 利率
3 返済何回目か 1
4 返済期間 35 期間

 

実際にセルの値などを入れてみるとこんな感じです。

=IPMT($B$2/12,B3,$B$4*12,$B$1,,)

現在価値、利率、返済期間は不変のものなので、アルファベットと数値の前に$の記号を入れて絶対参照にしています。

計算の結果は、¥-15,000
で毎月の利息分の支払金額です。

少し応用をして期については列だけを絶対参照($を付ける)にして、行は相対参照にすることで2回目以降の金利分と利息分の合計の計算などもできます。こうすることで、住宅ローン支払い当初は利息ばかりを払っていて元金がいかに減らないかを感じることができると思います。

IPMT結果
1 ¥-15,000
2 ¥-14,968
3 ¥-14,936
4 ¥-14,904
5 ¥-14,871
6 ¥-14,839
7 ¥-14,807




414 ¥-277
415 ¥-237
416 ¥-198
417 ¥-158
418 ¥-119
419 ¥-79
420 ¥-40
合計 ¥-3,267,641

※金利は変動を想定しているので結果も変動します。

参考:IPMT関数

PPMT関数で毎月の元金の金額を計算

PPMT関数で毎月の元金の金額を確認することができます。

関数の数式は、=PPMT(利率,期,期間,現在価値,将来価値,支払期日) です。

A B C
1 融資額(円) 30,000,000 現在価値
2 年利(%) 0.6 利率
3 返済何回目か 1
4 返済期間 35 期間

 

表の通りに入力してみると式はこのようになります。
=PPMT($B$2/12,B3,$B$4*12,B1,,)

絶対参照、相対参照は調整を適宜してください。

計算の結果は、¥-64,209で毎月の元金分の返済金額です。

そして、期の行を相対参照にすることで支払の何回目はいくらになるかについても確認できます。

IPMT結果
1 ¥-64,209
2 ¥-64,241
3 ¥-64,273
4 ¥-64,305
5 ¥-64,337
6 ¥-64,369
7 ¥-64,402




414 ¥-78,932
415 ¥-78,971
416 ¥-79,011
417 ¥-79,050
418 ¥-79,090
419 ¥-79,130
420 ¥-79,169
合計 ¥-30,000,000

応用してグラフ化してみる

IPMTが利息分、PPMTが元金分です。そして期を相対参照にすることで支払期間分のリストを作ってグラフ化することができます。

まずは、IPMTとPPMTのリストを作って、

IPMT PPMT
1 -15,000 -64,209
2 -14,968 -64,241
3 -14,936 -64,273
4 -14,904 -64,305
5 -14,871 -64,337
6 -14,839 -64,369
7 -14,807 -64,402






414 -277 -78,932
415 -237 -78,971
416 -198 -79,011
417 -158 -79,050
418 -119 -79,090
419 -79 -79,130
420 -40 -79,169
合計 -3,267,641 -30,000,000

 

表をもとにグラフにするとこんな感じです。

元金と利息の割合

グラフで図示することでローンのイメージを作ることが出てべんりです。

ちなみに、バブル期だと

ハウスメーカーや工務店に行って話をしていると、バブル期の金利の話になるときが稀にあります。

バブル期の住宅ローンの金利はなんと、元利均等で6%で絶頂期であれば8%ということもあったようです。。。

仮に6%だったとするとグラフはこんな感じです。

元金と利息の割合(6%)

ちなみに、融資額が3,000万円で元利均等の金利が6%だとすると、金利だけの総額が 41,843,903円です。。支払い開始当初は高額な利息ばかりで元金がほとんど減らず、金利の合計が融資額以上でまさに破産の一途です。。。恐ろしい時代です。

あとがき

住宅の購入は人生の中で金額が最も大きい買い物です。そんな買い物をするときに無計画すぎると破たんを招くことになりかねないので、それを避けるためにも数値化・グラフ化して計画の妥当性を把握しておくことは必要です。

不動産屋さんに計画資料を作ってもらうのも手段の一つでが、自分で作成するほうが印象は強くなります。

そのためのツールとしてエクセルの関数の、PMT関数IPMT関数PPMT関数はすごく便利なので紹介してみました。

※金利が変動金利の場合、内容全般的に数値は変動するので、参考までにとしてください。

-生活・暮らし

Copyright© ZIBUNLOG , 2017 All Rights Reserved Powered by AFFINGER4.