「住宅ローンの計算をエクセルでしたい」
「色々な銀行のホームページで計算して、比較するのが面倒」
住宅ローンを比較検討している方は、様々な金融機関のホームページでシミュレーションをしているかと思います。
しかし金融機関のホームページはそれぞれ作りも違えば、シミュレーション結果に含まれる項目も違っているため、一つひとつのシミュレーション結果を比較していくには時間も手間もかかります。
そこでこの記事ではエクセルを使って、元利均等返済の住宅ローンを簡単に計算をする方法を紹介していきます。
馴染みのない関数も登場してきますが、エクセルでまとめてしまえば比較検討がグッと楽になるので、ぜひご活用ください。
借入金額から毎月の返済額を求める
まずは住宅ローンの借入金額から、「毎月の返済額」を計算する方法を紹介しましょう。
毎月返済額の計算に使用する関数は「PMT関数」で、下記のように入力します。
=-PMT(年利/12, 返済年数×12, 借入金額)
たとえば下記の条件から、毎月の返済額を計算したいとします。
借入金額 | 3500万円 |
---|---|
返済年数 | 35年 |
金利 | 0.525% |
これを先ほどの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)
例えば下記の条件で、借入可能額を計算したいとします。
毎月の返済額 | 10万円 |
---|---|
返済年数 | 35年 |
金利 | 0.525% |
この条件を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形式で解説していきます。
年収 | 500万円 |
---|---|
返済年数 | 35年 |
金利 | 0.525% |
返済負担率 | 25% |
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モデルの価格推移を検証!