エクセル関数で住宅ローンの利息を計算！借入金額・毎月返済・年収から算出する方法

「住宅ローンの計算をエクセルでしたい」
「色々な銀行のホームページで計算して、比較するのが面倒」

住宅ローンを比較検討している方は、様々な金融機関のホームページでシミュレーションをしているかと思います。

しかし金融機関のホームページはそれぞれ作りも違えば、シミュレーション結果に含まれる項目も違っているため、一つひとつのシミュレーション結果を比較していくには時間も手間もかかります。

そこでこの記事ではエクセルを使って、元利均等返済の住宅ローンを簡単に計算をする方法を紹介していきます。

馴染みのない関数も登場してきますが、エクセルでまとめてしまえば比較検討がグッと楽になるので、ぜひご活用ください。

借入金額から毎月の返済額を求める

まずは住宅ローンの借入金額から、「毎月の返済額」を計算する方法を紹介しましょう。

毎月返済額の計算に使用する関数は「PMT関数」で、下記のように入力します。

=-PMT(年利/12, 返済年数×12, 借入金額)

たとえば下記の条件から、毎月の返済額を計算したいとします。

これを先ほどのPMT関数に当てはめると、次の通りとなります。

=-PMT（0.525% / 12, 35*12, 35000000）

金融機関のホームページに記載されている金利は年利ですが、PMT関数では月利をもとに計算するので「年利 / 12」に、返済回数は「返済年数×12」で計算します。

またPMT関数の結果は負の値になるため、関数の先頭にマイナスをつけるのを忘れないようにしましょう。

今回の例では、「9万1242円」が毎月の返済額ということが分かります。

完済までの総返済額は「毎月の返済額×返済回数」

住宅ローンを完済するまでの総返済額は、「毎月の返済額×返済回数」で簡単に計算できます。

例えば先ほどの例では返済年数は35年でしたので、「9万1242円×35年×12ヶ月」で「3832万1640円」が住宅ローンを完済するまでの総返済額になります。

ただし実際に住宅ローンを利用する際には、融資事務手数料などの諸費用も必要になります。具体的に住宅ローンのコストを比較する際は、諸費用も含めた上で計算することをおすすめします。

毎月の返済額から借入可能額を求める

毎月の返済額から借入可能額を計算するには「PV関数」を使用します。

=-PV(年利/12, 返済回数*12, 毎月返済額, 0)

例えば下記の条件で、借入可能額を計算したいとします。

この条件をPV関数に代入していきましょう。

=-PV(0.525%/12, 35*12, 100000, 0)

そうすると「3835万9485円」という数字が算出されますね。

しかし実際の住宅ローンでは、1円単位や10円単位で借りるわけではありません。そこでROUNDDOWN関数を使って、1万円以下を切り捨てておきましょう。

ROUNDDOWN関数で1万円以下を切り捨てる

ROUNDDOWN関数で1万円以下を切り捨てるには、下記のように値を入力します。

=ROUNDDOWN(38359485/10000,0)*10000

ROUNDDOWNは小数点以下を切り捨てる関数なので、一度3835万9485円を1万で割り、小数点以下を切り捨てたあとで、もう一度1万をかけ直しています。

そうすると「3835万円」が算出され、毎月の返済額が10万円、返済年数35年、金利0.525%の場合、3835万円までの住宅ローンを借り入れられることが分かります。

年収から借入可能額を計算する

次は年収から借入可能額を計算してみましょう。

毎月の返済額から借入可能額を計算する時と同様に「PV関数」を使用しますが、こちらは少し手順が多くなります。

ですので、この章では下記の条件を例にSTEP形式で解説していきます。

STEP1：年収から毎月の返済上限額を調べる

まずは年間の返済上限額を、「年収×返済負担率」で計算します。

今回の例では年収500万円、返済負担率25%なので、「500万円×25%＝125万円」が、一年間の返済上限額になります。

さらに125万円を12ヶ月で割ったものが、月間の返済上限額になります。

「125万円÷12ヶ月＝10万4166円(小数点以下切り捨て)」

STEP2：PV関数で借入可能額を調べる

STEP1で算出した毎月の返済上限額を、PV関数に当てはめましょう。

=-PV(0.525%/12, 35*12, 104166, 0)

すると「3995万7541円」が算出されます。

こちらも1円単位で借り入れる訳ではないので、ROUNDDOWN関数を利用して1万円以下を切り捨てておきましょう。

=ROUNDDOWN(39957541/10000,0)*10000

上記の関数から3995万円が算出され、年収500万円、返済年数35年、金利0.525%、返済負担率25%の場合は、3995万円まで借り入れられることがわかります。

【応用編】住宅ローンの返済予定表を作る

ここまではエクセルでの住宅ローンの計算方法を解説してきましたが、応用編として一ヶ月ごとの住宅ローンの返済予定表にも挑戦してみましょう。

返済予定表を作るには、元金の金額を算出する「PPMT関数」もしくは、利息を算出する「IPMT関数」を使用します。

・PPMT関数　…　返済金額のうちの元金を算出する
・IPMT関数　…　返済のうちの利息を算出する

どちらを使って計算しても良いのですが、今回は利息を算出するIPMT関数を使いましょう。

STEP1:返済予定表の枠を作る

関数を入力する前に、まずはそれぞれのデータを格納するための枠を作っていきましょう。

今回は下記のような枠を作成しています。

STEP2：「IPMT関数」で利息を求める

次はD7セルにIPMT関数を入力して、返済1回目の利息額を算出します。

＝-IPMT(年利/12,何回目の返済か,返済年数*12,借入金額,0)

上記の関数を入力すると、返済1回目の利息額は「1万5313円」であることがわかります。

STEP3：「PMT関数」で毎月の返済額を求める

次はPMT関数を使って毎月の返済額を算出します。PMT関数はE7セルに入力していきましょう。

=-PMT(年利/12, 返済年数×12, 借入金額)

上記の関数を入力すると、毎月の返済額は「9万1242円」であることが分かります。

STEP4：毎月返済額と利息の差額から元金を求める

次は返済額のうちの元金の割合を調べましょう。元金は「毎月返済額―利息」で簡単に計算できます。

上記の関数を入力すると、返済1回目の元金は「7万5930円」であることが分かります。

STEP5：8行目以降に関数を貼り付けていく

あとはC7セル～E7セルに入力した関数を、8行目以降にコピー&ペーストすれば、返済予定表の完成です。

グラフで見ても、利息の割合が徐々に少なくなっていることが分かりますね。

まとめ

この記事ではエクセルを活用して、住宅ローンの返済額を計算する方法を解説してきました。

使用した関数をまとめると、下記の通りとなります。

• 借入金額から毎月返済額を計算するには「PMT関数」
• 毎月返済額から借入可能額を計算するには「PV関数」
• 年収から借入可能額を計算するには「PV関数」
• 元金を計算するには「PPMT関数」
• 利息を計算するには「IPMT関数」
• 1万円以下の切り捨てには「ROUNDDOWN関数」

エクセルの比較表を作っておけば、様々な条件での住宅ローンの計算・比較がグッと楽になります。

あまり馴染みのない関数が多く登場してきましたが、ぜひ活用してみてください。

＜プロフィール＞
名前：中澤 悠生（ナビナビ住宅ローン編集部）
プロフィール：住宅ローンをどこよりも分かりやすくをモットーに、住宅ローン情報を解説しています。
https://navinavi-mortgage.com/

【関連記事】
・保険代理店は何をしてくれる？メリット・デメリット、信頼できる代理店の見つけ方とは？
・国民共済より県民共済？「都道府県民共済」がコスパ最強といわれるワケ
・株式投資における長期保有銘柄の選び方。成功させるコツは？
・税金をクレカで支払うときの7つの注意点 高還元率クレジットカード5選＋α
・高級腕時計は投資として成り立つのか？有名4モデルの価格推移を検証！