56382
52744
HOME»金利について » エクセルを使った住宅ローンシミュレーションの利息計算方法をFPが解説!
※この記事は約3分で読めます。

エクセルを使った住宅ローンシミュレーションの利息計算方法をFPが解説!

エクセルを使った計算方法を解説
皆さんこんにちは、ファイナンシャルプランナーの藤です。私は仕事柄エクセルの関数を使う機会が多く、複雑な計算を間違いなく簡単に計算できるようエクセルVBAエキスパートの資格を所有しております。今回は、気になる住宅ローンの利息で、エクセルを使った計算方法を解説していきます。

1. 利息計算でエクセルを使うメリット

毎月の返済額をエクセルを使って見てみましょう。下記からダウンロード!


住宅ローンシミュレーションエクセルダウンロード(simulation.xlsx)

住宅ローンの利息計算は電卓でも可能ですが、普段使うエクセルを利用すれば簡単に利息計算ができ、一度、エクセルに関数を入れてしまえば、数値を変えるなどシミュレーションをすることができます。金融機関のサイトにあるシミュレーションを使うと簡単にシミュレーションができますが、エクセルの方が手軽なこともあります。ここでは、毎月の返済額が一定の元利均等返済を前提に、エクセルを使った計算方法を見てみましょう。

なんとなく選んだ住宅ローンで損をしていませんか?
支払い総額では数百万の差が出ます。金利が安い銀行を比較しましょう↓

見直しで残債が480万円も減額?プロが客観的にあなたの住宅ローンを無料で診断!


ネット銀行住宅ローンで金利が一番低いのがどこ?人気の3社を比較!
ネット銀行住宅ローンの低金利3社を比較したページはコチラ

2. PMT関数

PMT関数についてご説明します


マイクロソフト社のサイトによると、PMTとは、「一定利率の支払いが定期的に行われる場合の、ローンの定期支払額を算出します」とあります。エクセルでPMTを打ち込めば、あとはセルを選択していけばいいのですが、計算に必要な値(引数)を選ぶ必要がありますので、構文を確認しておきましょう。
PMT(利率,期間,現在価値,[将来価値],[支払期日])
※[ ]は省略可能です。
・利率:ローンの利率を入力したセルを指定します。
・期間:支払回数を入力したセルを指定します。35年返済の場合、35×12=420回となります。
・現在価値:借入金額を入力したセルを指定します。
・将来価値:最後の支払いを終えた後の残債額を入力したセルを指定します。つまり「0」となります。指定しなくても「0」となります。
・支払期日:各期の期首に支払う場合は「1」を、期末に支払う場合は「0」を入力したセルを指定します。
注意点としては、支払回数は月数と同じなので、利率も月利率に直した数値を入力します。

3. 実際にPMT関数を使ってみよう

実際にPMT関数を使って見てみましょう


ではPMT関数を使ってみましょう。まずは準備として年利、支払期間、借入金額のセルを作り、それぞれに値を入れます。住宅ローンの場合、利率は年利で、支払期間は年単位で表記されることがほとんどですので、そのまま入力できるように単位を合わせています。
実際にPMT関数を使ってみよう01
次にPMT関数を入力しますが、計算に必要な値(引数)は左から順番に選択すればいいだけです。セルを一つ選んだら、半角カンマ「,」を入力し、最後にエンターキーを押せばPMT関数の設定は完了です。
実際にPMT関数を使ってみよう02
次に、年利を月利率に、支払期間を月単位に変えます。
月利率=年利÷12 ⇒ 割り算は「/」
月単位=支払期間×12 ⇒ 掛け算は「*」
を付け加えます。
実際にPMT関数を使ってみよう03
すると、下記のような結果になると思います。
実際にPMT関数を使ってみよう04
数値がマイナスになりますので、PMTの前に「-」を加えて完了です。
実際にPMT関数を使ってみよう05
完成!
実際にPMT関数を使ってみよう06

3. IPMT関数

IPMT関数についてご説明します


同じく、マイクロソフト社のサイトによると、IPMTとは、「定額の支払いを定期的に行い、利率が一定であると仮定して、投資期間内の指定された期に支払われる金利を返します」とあります。月々の返済額に含まれる利息額を求めるのがIPMT関数です。構文を見ておきましょう。
月々の返済額に含まれる利息額を求めます
IPMT(利率,期,期間,現在価値,[将来価値],[支払期日])
※[ ]は省略可能です。
・利率:ローンの利率を入力したセルを指定します。
・期:利息額を求める回数を入力したセルを指定します。35年返済の場合、1~420のいずれかの数値が入ります。
・期間:支払回数を入力したセルを指定します。35年返済の場合、35×12=420回となります。
・現在価値:借入金額を入力したセルを指定します。
・将来価値:最後の支払いを終えた後の残債額を入力したセルを指定します。つまり「0」となります。指定しなくても「0」となります。
・支払期日:各期の期首に支払う場合は「1」を、期末に支払う場合は「0」を入力したセルを指定します。

4. 実際にIPMT関数を使ってみよう

実際にIPMT関数を使って見てみましょう


ではIPMT関数を使ってみましょう。まずは準備として年利、期、支払期間、借入金額のセルを作り、それぞれに値を入れます。
実際にIPMT関数を使ってみよう01
「期」の「1」は、第1回目の支払いを表し、その際の利息額を求めることになります。基本はPMTと同じですので、完成形を示したいと思います。
実際にIPMT関数を使ってみよう02
このIPMT関数で使用する「期」をA列に移動すれば、1~420回の返済額と利息額の一覧を作ることができます。

5. 住宅ローンの償還表を作成しよう

住宅ローンの償還表を作成してみましょう


セルを移動すると関数の式も自動で変わってくれます。「期」を1~420を自動入力で表示させ、「IMPT関数」を「利息額」に変更します。
住宅ローンの償還表を作成しよう01
次に返済額のところに、PMT関数を入れます。すると、毎月の返済額と利息額が求まりますので、返済額から利息額を差し引けば返済額に含まれる元金を算出することができます。
住宅ローンの償還表を作成しよう02
あとは、各セルに同じように関数を入れれば、住宅ローンの償還表が完成します。

6. エクセルのスキルも身につく住宅ローンの利息計算方法

色々なシミュレーションができるのはいいですね


今回は、PMT関数とIPMT関数を使いましたが、別の関数で計算する方法もあります。エクセルを使うと応用がきき、色々なシミュレーションができます。最後に住宅金融支援機構のシミュレーションでこれまでの数値が正しいか確認しました。
住宅ローンの利息計算方法

もくじ

毎月返済額がほぼ同じですので、サイトのシミュレーションと同じものが出来たことがわかります。ネットを検索すれば関数の使い方も分かりますのでチャレンジしてみてください。

 

もくじ

低金利が期待できるネットバンク。フラット35も含め、一覧で比較しました↓


なんとなく選んだ住宅ローンで損をしていませんか?
支払い総額では数百万の差が出ます。金利が安い銀行を比較しましょう↓

ネット銀行住宅ローンで金利が一番低いのがどこ?人気の3社を比較!
ネット銀行住宅ローンの低金利3社を比較したページはコチラ

PR

このページでわからない点があれば教えて下さい、住宅ローン牧場のFPが確認後回答致します。








  • ネット銀行の住宅ローンランキング、FPが金利で比較! ネット銀行の住宅ローンランキング、FPが金利で比較!
    更新日:2018/7/5 ネットバンクの中でも金利が低いものを集めました 借換え/新規共に複数社の申込みを併願... 116,847 views
  • リフォームローン金利ランキング、人気の5社をFPが解説!審査内容は? リフォームローン金利ランキング、人気の5社をFPが解説!審査内容は?
    リフォームローン金利ランキング、人気の5社をFPが解説!審査内容は? 新たな住まいを購入する際、無理に新築にこだわる必要... 40,078 views
  • 住宅ローンの固定金利の利率。35年、30年。上手な組み方 住宅ローンの固定金利の利率。35年、30年。上手な組み方
    住宅ローンの固定金利の利率。35年、30年。上手な組み方 住宅ローンは大きく分けて固定金利と変動金利があります。さらに細かく分けてい... 13,437 views
  • 優良住宅ローンはどうなの?金利や口コミをFPがシミュレーション! 優良住宅ローンはどうなの?金利や口コミをFPがシミュレーション!
    ネット銀行住宅ローンで金利が一番低いのがどこ?人気の3社を比較! ネット銀行住宅ローンの低金利3社を比較したページはコチラ 株式会社優良住宅ローン... 12,815 views
  • 住宅ローンで5000万の借入額。その適正年収や審査などをFPが解説!職業は? 住宅ローンで5000万の借入額。その適正年収や審査などをFPが解説!職業は?
    住宅ローンで5000万の借入額。その適正年収や審査などをFPが解説!職業は? 住宅ローンには3,000万円の壁という言葉があります。 日本... 9,205 views
  • 人気記事
  • 住宅ローン仮審査と本審査の違い。日数はどのぐらい?FPが解説! 住宅ローン仮審査と本審査の違い。日数はどのぐらい?FPが解説!
    更新日:2017/5/15 ネット銀行住宅ローンで金利が一... 252,503 views
  • ネット銀行の住宅ローンランキング、FPが金利で比較! ネット銀行の住宅ローンランキング、FPが金利で比較!
    更新日:2018/7/5 ネットバンクの中でも金利が低... 116,847 views
  • ろうきん住宅ローンを申し込むメリット。評判と口コミをFPが分析! ろうきん住宅ローンを申し込むメリット。評判と口コミをFPが分析!
    ろうきん住宅ローンを申し込むメリット。評判もいいです。 み... 86,295 views
  • 楽天銀行住宅ローンのメリットデメリット&口コミ~FPが解説! 楽天銀行住宅ローンのメリットデメリット&口コミ~FPが解説!
    ネット銀行住宅ローンで金利が一番低いのがどこ?人気の3社を比... 68,408 views
  • 住宅ローン控除のシミュレーション、計算方法をFPが解説 住宅ローン控除のシミュレーション、計算方法をFPが解説
    住宅ローン控除のシミュレーション、計算方法について教えます ... 62,718 views
  • 最新記事
  • 住宅ローンの借り換えで損をするケース?FPが注意点を喚起!~ネットバンク編
    住宅ローンの借り換えで損をするケース?FPが注意点を喚起!~ネットバンク編 住宅...
  • リフォームローンと住宅ローンの違いを解説!金利や審査は異なる?FPが解説!
    リフォームローンと住宅ローンの違いを解説!金利や審査は異なる?FPが解説! 多く...
  • 固定資産税のシミュレーションとは?新築建売やマンションではどうなる?FPが解説!
    固定資産税のシミュレーションとは?新築建売やマンションではどうなる?FPが解説!...
  • 信託銀行と銀行の違いをわかりやすくFPが解説!
    信託銀行と銀行の違いをわかりやすくFPが解説! 一般社団法人信託協会によると、信...
  • 住宅ローンの審査で健康状態はどう影響する?健康診断書は必要?FPが解説!
    住宅ローンの審査で健康状態はどう影響する?健康診断書は必要?FPが解説! 住宅購...
  • 全記事