住宅ローンを比較検討している方は、様々な金融機関のホームページでシミュレーションをしているかと思います。金融機関のホームページはそれぞれ作りも違えば、シミュレーション結果に含まれる項目も違っているため、一つひとつのシミュレーション結果を比較していくには時間も手間もかかります。(画像=selinofoto/Shutterstock.com)そこでこの記事ではエクセルを使って、元利均等返済の住宅ローンを簡単に計算をする方法を紹介していきます。馴染みのない関数も登場してきますが、エクセルでまとめてしまえば比較検討がグッと楽になるので、ぜひご活用ください。借入金額から毎月の返済額を求めるまずは住宅ローンの借入金額から、「毎月の返済額」を計算する方法を紹介しましょう。毎月返済額の計算に使用する関数は「PMT関数」で、下記のように入力します。借入金額)たとえば下記の条件から、毎月の返済額を計算したいとします。金利0.525%これを先ほどのPMT関数に当てはめると、次の通りとなります。12」に、返済回数は「返済年数×12」で計算します。PMT関数の結果は負の値になるため、関数の先頭にマイナスをつけるのを忘れないようにしましょう。今回の例では、「9万1242円」が毎月の返済額ということが分かります。先ほどの例では返済年数は35年でしたので、「9万1242円×35年×12ヶ月」で「3832万1640円」が住宅ローンを完済するまでの総返済額になります。実際に住宅ローンを利用する際には、融資事務手数料などの諸費用も必要になります。具体的に住宅ローンのコストを比較する際は、諸費用も含めた上で計算することをおすすめします。毎月の返済額から借入可能額を求める毎月の返済額から借入可能額を計算するには「PV関数」を使用します。0)例えば下記の条件で、借入可能額を計算したいとします。0.525%この条件をPV関数に代入していきましょう。0)そうすると「3835万9485円」という数字が算出されますね。実際の住宅ローンでは、1円単位や10円単位で借りるわけではありません。そこでROUNDDOWN関数を使って、1万円以下を切り捨てておきましょう。ROUNDDOWN関数で1万円以下を切り捨てるROUNDDOWN関数で1万円以下を切り捨てるには、下記のように値を入力します。=ROUNDDOWN(38359485/10000,0)*10000ROUNDDOWNは小数点以下を切り捨てる関数なので、一度3835万9485円を1万で割り、小数点以下を切り捨てたあとで、もう一度1万をかけ直しています。すると「3835万円」が算出され、毎月の返済額が10万円、返済年数35年、金利0.525%の場合、3835万円までの住宅ローンを借り入れられることが分かります。年収から借入可能額を計算する次は年収から借入可能額を計算してみましょう。毎月の返済額から借入可能額を計算する時と同様に「PV関数」を使用しますが、こちらは少し手順が多くなります。この章では下記の条件を例にSTEP形式で解説していきます。今回の例では年収500万円、返済負担率25%なので、「500万円×25%=125万円」が、一年間の返済上限額になります。125万円を12ヶ月で割ったものが、月間の返済上限額になります。「125万円÷12ヶ月=10万4166円(小数点以下切り捨て)」STEP2:PV関数で借入可能額を調べるSTEP1で算出した毎月の返済上限額を、PV関数に当てはめましょう。0)すると「3995万7541円」が算出されます。こちらも1円単位で借り入れる訳ではないので、ROUNDDOWN関数を利用して1万円以下を切り捨てておきましょう。=ROUNDDOWN(39957541/10000,0)*10000上記の関数から3995万円が算出され、年収500万円、返済年数35年、金利0.525%、返済負担率25%の場合は、3995万円まで借り入れられることがわかります。【応用編】住宅ローンの返済予定表を作るここまではエクセルでの住宅ローンの計算方法を解説してきましたが、応用編として一ヶ月ごとの住宅ローンの返済予定表にも挑戦してみましょう。返済予定表を作るには、元金の金額を算出する「PPMT関数」もしくは、利息を算出する「IPMT関数」を使用します。返済のうちの利息を算出するどちらを使って計算しても良いのですが、今回は利息を算出するIPMT関数を使いましょう。STEP1:返済予定表の枠を作る関数を入力する前に、まずはそれぞれのデータを格納するための枠を作っていきましょう。今回は下記のような枠を作成しています。STEP2:「IPMT関数」で利息を求める次はD7セルにIPMT関数を入力して、返済1回目の利息額を算出します。=-IPMT(年利/12,何回目の返済か,返済年数*12,借入金額,0)上記の関数を入力すると、返済1回目の利息額は「1万5313円」であることがわかります。STEP3:「PMT関数」で毎月の返済額を求める次はPMT関数を使って毎月の返済額を算出します。PMT関数はE7セルに入力していきましょう。借入金額)上記の関数を入力すると、毎月の返済額は「9万1242円」であることが分かります。STEP4:毎月返済額と利息の差額から元金を求める次は返済額のうちの元金の割合を調べましょう。上記の関数を入力すると、返済1回目の元金は「7万5930円」であることが分かります。STEP5:8行目以降に関数を貼り付けていくあとはC7セル~E7セルに入力した関数を、8行目以降にコピー&ペーストすれば、返済予定表の完成です。グラフで見ても、利息の割合が徐々に少なくなっていることが分かりますね。まとめこの記事ではエクセルを活用して、住宅ローンの返済額を計算する方法を解説してきました。使用した関数をまとめると、下記の通りとなります。利息を計算するには「IPMT関数」1万円以下の切り捨てには「ROUNDDOWN関数」エクセルの比較表を作っておけば、様々な条件での住宅ローンの計算・比較がグッと楽になります。馴染みのない関数が多く登場してきましたが、ぜひ活用してみてください。悠生(ナビナビ住宅ローン編集部)プロフィール:住宅ローンをどこよりも分かりやすくをモットーに、住宅ローン情報を解説しています。