「住宅ローン」の毎月返済額、総返済額（総支払額）をエクセルで計算する方法を紹介しよう。通常、銀行のシミュレーションサイトで計算する人は多いが、銀行によって仕様が違うため、あまり正確にシミュレーションすることはできない。結局、自分でシミュレーションしたほうが正確に比較できるのだ。さらに、手数料などを含む実質金利の計算方法も紹介するので、エクセルをマスターして、住宅ローンを高精度に徹底比較できるようになろう。
銀行のシミュレーションは、不正確なことも
不動産を購入する際は多くの人が住宅ローンを利用するが、住宅ローンを借りるうえで知っておきたいのが、「月々の返済額（毎月返済額）」や「総支払額」などだ。こうした計算は、各銀行・金融機関のシミュレーションでも可能だ。しかし実際には、自行の住宅ローンのシミュレーションしか計算できないものが多い。シミュレーションの仕様が違っていたり、中には不正確なシミュレーションもあったりする。
例えば、10年固定金利の住宅ローンを借りた場合、11年目以降の金利は変動金利か、固定金利かを選ぶことになるが、銀行のサイトで公開されているシミュレーションは設定がまちまちだ。また、諸費用・手数料に、印紙税、司法書士報酬、抵当権設定の登録免許税など（合計で約20万円）を含んでいるか、いないかでも大きな差となる。
結局、正確に住宅ローンをシミュレーションするのであれば、自分でエクセルを使ってシミュレーションしたほうがいいのだ。そうすれば、様々な銀行の住宅ローンを比較して、どの住宅ローンを選ぶのが最もお得なのかも分かる。さらには、諸費用・手数料込みの「実質金利」も高精度に計算できる。
かつては、住宅ローンの返済額を計算するには関数電卓を使いこなす必要があったが、今はエクセルなどの表計算ソフトでスムーズに計算できるようになっており、ファイナンシャルプランナーに相談しなくても、自分で計算は可能だ。
（エクセルファイルのダウンロードはこちら）
様々な金利があるので、間違わないよう注意！
住宅ローンをエクセルでシミュレーションする際、まずは基本用語を確認しておく必要がある。各銀行では、住宅ローンの金利を毎月提示しているが、金利にはいくつかの種類があるので間違えないようにしたい。
実際に適用される金利のことを「表面金利」という。銀行のサイト等で大きく書かれているもので、当初適用される金利なので「適用金利」ともいわれる。
この適用金利の元になるのが、「基準金利（店頭金利）」だ。変動金利の基準金利は大手銀行なら、2.475％だ。これに対して、各銀行は「金利優遇幅」を設定しており、大手銀行なら1.85％程度に設定していることが多い。結果として、2.475％-1.850％＝0.625％が「表面金利」となる。
表面金利＝基準金利－金利優遇幅
銀行のサイトではすべての金利が記載されているので混乱するかもしれないが、実際に適用されるのは「表面金利」なので、覚えておこう。
また、住宅ローンでは、「元利均等返済」「元金均等返済」の2つの返済方法がある。
元利均等返済は、月々の返済額が最後まで一定という返済方法だ。返済計画が立てやすいメリットがあるが、元金均等返済の場合より総返済額が若干多くなるデメリットもある。
一方、元金均等返済は、毎月支払う元金が一定という返済方法。返済が進むにつれて毎月の利息が減っていくので、元利均等返済に比べると総返済額は少なくなるメリットがある。ただし、返済当初の返済額が最も高くなる。
多くの場合は、「元利均等返済」で住宅ローンを組む人が多く、低金利下でどちらの返済方法でも大きな差がなくなっているので、今回は「元利均等返済」による計算を取り上げる。
（参考）期間固定金利の注意点
10年固定金利などの住宅ローンの場合、固定期間終了後の金利を間違う人が多いので注意しよう。銀行の住宅ローンの商品説明書を読むと、「固定期間終了後は、基準金利から金利優遇幅を引いた金利が適用されます」などと書かれていることが多い。つまり、固定期間終了後に変動金利を選択する場合、現在の変動金利ではなく、（変動金利用の）基準金利から金利優遇幅を引いたものが適用されるのだ。実際に適用となる金利は具体的に書いていないので、自分で計算しなければならない。
また事態を複雑にするのが、優遇幅が変動する商品があること。固定期間中の優遇幅は大きいが、固定期間終了後は優遇幅が小さくなる商品もあるので、商品説明の下の細かい文字も見逃さないよう、注意しよう。
エクセルで住宅ローン償還表を作ろう！
それでは、実際にエクセルへの入力をしてみよう。今回は、標準的な変動金利の住宅ローンをシミュレーションすることにした。
（エクセルファイルのダウンロードはこちら）
住宅ローンをエクセルでシミュレーションする際、計算の前提として、以下の情報を用意しておこう。
①借入額
②借入期間
③表面金利
④諸費用
そして、上記の数字を使って、以下の項目を計算する。
⑤元利総支払額
⑥毎月返済額
⑦総返済額
⑧実質金利
※便宜上、表面金利は一定で変化しないものとし、ボーナス返済はなしとする。
まずは、エクセルを開いて、下の画像のように①〜⑧までの項目名と、前提となる数値を設定する。
①借入額＝3000万円（セルD1に「30000000」を入力）
②借入期間＝35年（セルD2に「35」を入力）
③表面金利＝0.500%（セルD3に「0.005」を入力）
「月」列のセルA12には、「0」を入力し、１刻みで「420」（セルA432）までオートフィルで入力する（0を入力後、そのセルの右下にカーソルを合わせると「＋」になる。その状態で、「Ctrl」を押しながら下にドラッグする）。
続いて、「月」「金利」「毎月返済額」「元金」「利息」「残高」の項目名を、画像のように、セルA11から右へと入力していく。
「金利」列も、セルB13に「=$D$3」を入力。金利はずっと変わらないものとして、420カ月までオートフィルで埋めておこう。
④諸費用は、銀行の商品説明書をよく読んで入力しよう。
銀行によってその金額は異なるだけでなく、りそな銀行のように商品によって諸費用が違うという銀行もあるので注意だ。
ネット銀行は契約時に「手数料」として「融資額×2.2％」を支払うことが多い（新生銀行は5.5万円、楽天銀行は33万円という商品もある）。
一方で、大手銀行の場合は、「保証料」を取るケースが多く、借入期間が35年の場合は、契約時に「融資額×2.06%」程度を支払うことになる。
ここでは、セルD4に「660,000」(3000万円×2.2%＝66万円）を入力しておこう。
より正確に諸費用を計算する場合は、印紙税、司法書士報酬、抵当権設定の登録免許税など（合計で約20万円）を加えてもいいだろう。
この表は、住宅ローン償還表と呼ばれ、返済期間（単位：月）のお金の返済表となる。一般的に、住宅ローンのシミュレーションというのは、この住宅ローン償還表を作成することなのだ。
エクセルに、計算式を入れよう！
次に、計算式を入れていく。
■⑤元利総支払額のセルを入力
⑤元利総支払額は、「毎月返済額」の総和になる。
⑤元利総支払額のセルには、SUM関数を使う
うっかり勘違いして、3000万円の金利が0.5%、35年だから、3000万円×0.5%×35（年）＝525万円とする人がいるが、これは間違っている。住宅ローンは毎月返済していくため、残高が減少していく。利息額は「毎月の残高×金利」を合計したものだ。
セルD6に「=SUM(C13:C432)」と入力しよう。SUMというのは、指定した範囲の数値を合計する関数だ。セルC13から、セルC432までの数値を合計する。
■⑥毎月返済額のセルを入力
次に、⑥毎月返済額を計算しよう。そのためには、財務関数の「PMT」を使う。
最初に、セルD7に「=C13」を入力しておこう。
次に、セルC13をクリックして、関数「=PMT($D$3/12,$D$2*12,-$D$1)」を入力しよう。
⑥毎月返済額を計算するのには、PMT関数を使う
関数の中に入れる３つの項目は、それぞれ「利率」「期間」「現在価値」を指している。
「利率」は表面金利0.500%（0.005）をそのまま入力してはいけない。この表面金利は、年利によるもので、毎月返済額を計算するには、月利が必要になる。したがって、金利の空欄に入力するのは12カ月で割った、「$D$3/12」（0.5%/12）となる。
「期間」は月単位でみるので、「$D$2*12」（420カ月）。
「現在価値」は借入額の「-$D$1」（-3000万円）を入力する。借金なので、マイナスをつけるのだ。
入力が終わると、そのセルに「77,876」（円）と表示される。この金額が毎月返済額となる。金利がずっと変わらない前提であれば、このセルを420カ月までオートフィルで入力する。
なお、住宅ローンのシミュレーションは、1円以下の端数処理の方法などが銀行によって違うため、今回のエクセルのシミュレーションと銀行の正式なシミュレーションでは数字が食い違うことがあるが、合計でも数百円程度の誤差なので、今回は無視する。
■元金のセルを入力
「元金」は、毎月返済額から利息を引いたものになる。セルD13に「=C13-E13」を入力。420カ月までオートフィルを使って入力しよう。
■利息のセルを入力
「利息」は、前月の残高に金利（月利）をかけたものだ。セルE13に「=F12*(B13/12)」を入力。420カ月までオートフィルを使って入力しよう。
■残高のセルを入力
「残高」は、0カ月目はそのまま残っているので、セルF12に「=D1」と入力。
翌月以降は、前月の残高から元金を引いたものになる。セルF13に「=F12-D13」を入力。420カ月までオートフィルを使って入力しよう。
■⑦総返済額のセルを入力
ここまでくれば、⑦総返済額などが表示される。
「⑦総返済額」は、④諸費用＋⑤元利総支払額となる。セルD8に「=D4+D6」と入力しよう。
これで設定は終了だ。
ここまで入力すると、⑤元利総支払額、⑥毎月返済額、⑦総返済額が表示されるだろう。
このシミュレーションの結果と、自分の収入や現在の家賃を比べて、住宅ローン（毎月返済額）をきちんと払っていけるかどうかを確かめよう。
（参考）途中で金利が変更になる場合
10年固定金利や、フラット35Sなどは途中で金利が変更になる。どう対応すればいいのか。まず、変更となる月以降の「金利」をすべて変更後の金利に変更する。
次に、「毎月返済額」については、変更となる月のセルに、以下を入力。
=PMT(新金利/12,残りの月数,その前月の残高)
これで、計算された新しい毎月返済額（数値）を、419カ月までコピーすればいい。
エクセルなら、「実質金利」も計算できる！
さて、このエクセルで作った住宅ローンシミュレーションを使えば、様々な銀行の住宅ローンの総返済額を計算し、どの住宅ローンがお得なのか判断がつくだろう。
しかし、総返済額は桁数が多く、いくつかの住宅ローンを比べたときに、どの商品がお得なのか比較しにくいという欠点がある。また、総返済額は同じだが、最初に多くの諸費用をとられる商品と、後で多めに支払うことになる商品では、明らかに後者の方がいい商品だが、「総返済額」では、その違いがわからない。
そこで、高精度に比較したいときに便利なのが、「⑧実質金利」だ。⑧実質金利は、表面金利だけではわかりにくい手数料や保証料などのすべての諸費用を「金利」に換算し、「表面金利」と合算したものだ。さらに、当初必要な諸費用については、金利負担を考えて重めに考慮してくれる指標なので、本当にお得な住宅ローンを見つけることが可能になる。
⑧実質金利を求めるには、財務関数「IRR」を使う。これは一連のキャッシュフローから内部利益率を求めるために便利な関数で知られている。これを使って実質金利を計算してみよう。実質金利を計算するために必要なのは、「借入額と諸費用」「420カ月分の毎月返済額」だ。
まず、0カ月目の「毎月返済額」のセルC12に「=-F12+D4」と入力する。これは、借入額（－3000万円）から、諸費用の66万円を引いたものだ。借り入れをした時のお金の出入りを計算したことになる。
次に、セルD9に関数「=IRR(C12:C432)*12」を入力する。
⑧実質金利を計算するのには、IRR関数を使う
また、計算されるのは月利なので、年利にするために、「＊12」としている。こちらで計算した実質金利は、0.631%となる。
ちなみに「セルC432」は、420カ月目の毎月返済額にあたる。もし、借入期間が35年でない場合は、その期間に合わせて計算範囲を変更する必要がある。
要するに、表面金利は0.500%だったが、諸費用まで含めて考えれば金利が0.131%アップしていることになる。もしも諸費用がもっと高い金融機関の住宅ローンであれば、表面金利が0.500%で同じだとしても、実質金利を計算すれば0.631%を超えるケースもある。表面金利だけ比べていては、本当にお得な住宅ローンの判別がつかないというわけだ。
以上が、エクセルで住宅ローンの返済額、実質金利をシミュレーションする方法だ。参考になっただろうか。
（エクセルファイルのダウンロードはこちら）
（参考）実質金利のもう一つの求め方（金利が変動しないケース）
実質金利をエクセルよって計算するには、もう一つの計算方法がある。金利が変動しないケースにしか使えないがより簡単だ。使うのは、関数「RATE」。必要な項目は、毎月返済額「77,876」、借入額から諸費用を差し引いた-29,340,000（-3000万円+66万円）円、借入期間の420カ月、そして年単位の利率にするため最後に「＊12」が必要になる。空いているセルに以下の関数を入力してみよう。
=RATE(420,77876,-29340000)*12
計算すると、0.00631219と表示されるだろう。これは％で表示すれば0.631%なので、実質金利（％）は、「0.631%」になる。
ダイヤモンド不動産研究所のシミュレーションが便利
高機能なシミュレーションなら、エクセルは不要。（ダイヤモンド不動産研究所の「返済額シミュレーション」）
さて、住宅ローン償還表を自分で作成できただろうか。エクセルを使うことで住宅ローンのシミュレーションは簡単にできるようになったが、間違わないように入力するのは案外、手間がかかる。
さらに大変なのが、各銀行のサイトなどを見て、「表面金利」「固定期間終了後の金利」「諸費用」を調べること。各銀行で書式や表現方法が違うので、数値を拾い出すだけでも非常な手間となる。
そこで活用してほしいのが、ダイヤモンド不動産研究所の「返済額シミュレーション」だ。ダイヤモンド不動産研究所では、主要132銀行・1000商品について、金利、諸費用をすべてデータベース化しており、借入金額や期間などを入力すれば、「総返済額」「実質金利」を高精度に自動計算できる。さらに、借入条件に合った住宅ローンをランキングで並べているので、最もお得な住宅ローンを簡単に見つけられる。
住宅ローンのシミュレーションで、総返済額だけでなく、実質金利まで計算してくれるサイトは少ない。
自分で住宅ローンを計算するのもいいが、ダイヤモンド不動産研究所のシミュレーションを利用すると、スピーディーに最もお得な銀行の住宅ローンを見つけられるので、参考にしよう。
【関連記事はこちら】＞＞返済額シミュレーションで、132銀行・1000商品からお得な住宅ローンを探せ！毎月返済額、実質金利まで試算！
| 132銀行を比較◆住宅ローン実質金利ランキング[新規借入] |
| 132銀行を比較◆住宅ローン実質金利ランキング[借り換え] |
 |
 |
| 【金利動向】おすすめ記事 | 【基礎】から知りたい人の記事 |
| 【最新版】今月の金利動向 【金利】翌月の金利予想 【金利動向】2021年の金利動向 【変動金利】上昇時期は？ 【変動金利】16年分の金利推移 |
【住宅ローン基礎】8カ条 【審査】の基礎 【借り換え】の基礎 【フラット35】どの銀行がいい？ 【住宅ローン控除】メリット最大化法 |
【2021年4月最新版、主要銀行版】
|
||||
| 順位 | 銀行名 <商品名> | |||
|
実質金利
（費用等含む） |
表面金利
（費用等除く） |
保証料
（税込） |
事務手数料
（税込） |
|
|
1
位 |
||||
| 0.510% 全疾病＋がん50％ |
0.380%
|
0円
|
借入額×2.2%
|
|
| 【auじぶん銀行の住宅ローンのメリット・おすすめポイント】 ・三菱ＵＦＪ銀行とKDDIが共同で立ち上げたネット銀行で、変動金利は業界トップクラスの低金利 ・無料団信が充実しており、がんと診断されると住宅ローン残高が半分になる「がん50％保障団信」のほか、「全疾病保障」「月次返済保障」が無料で付帯 ・ネットだけで契約を完了でき、仮審査は最短即日回答、本審査は最短2～3営業日で回答など、審査スピードも速い ・じぶんでんきセット割引も用意。新電力「じぶんでんき」に加入できた場合、金利を0.03％引き下げるもので、適用されない場合の金利は0.41%。 （審査の結果によっては保証付金利プランとなる場合があり、この場合には上記の金利とは異なる金利となります。 金利プランが保証付金利プランとなる場合は、固定金利特約が3年、5年、10年に限定されます）※審査の結果、保証会社をご利用いただく場合は、保証料相当額を上乗せした金利が設定されますが、別途お支払いいただく保証料はございません。 |
||||
| 【関連記事】auじぶん銀行の住宅ローンの金利、手数料、ポイント、注意点は？ | ||||
 |
||||
|
1
位 |
 PayPay銀行 ＜住宅ローン 全期間引下げ(自営業、市街化調整区域は不可)・変動金利＞
|
|||
| 0.510% |
0.380%
|
0円
|
借入額×2.2%
|
|
| 【PayPay銀行の住宅ローンのメリット・おすすめポイント】 ・ネット銀行のPayPay銀行（旧ジャパンネット銀行）が、2019年７月末に住宅ローンの貸し出しをスタート ・業界最低水準という低い金利で、特に「変動金利」「10年固定金利」に強みがある ・オプションの団体信用生命保険も豊富に取りそろえる ・個人事業主、家族が経営する会社に勤務している場合も原則利用不可。借地、市街化調整区域なども不可 |
||||
| 【関連記事】PayPay銀行の住宅ローンの金利、手数料、ポイント、注意点は？ | ||||
|
3
位 |
||||
| 0.540% 全疾病保障付き |
0.410%
|
0円
|
借入額×2.2%
|
|
| 【SBIマネープラザの住宅ローンのメリット・おすすめポイント】 ・SBIマネープラザは、証券、保険、住宅ローンなどを取り扱う、SBIグループのマネー相談プラザ ・全国9支店において対面で相談できるので、初心者でも安心 ・変動金利が低い「ミスター住宅ローンREAL」（住信SBIネット銀行の商品）と、全期間固定金利が低い「フラット35」を取り扱っており、2種類の住宅ローンを比較して申し込める |
||||
| 【関連記事】SBIマネープラザの住宅ローンの金利、手数料、ポイント、注意点は？ | ||||
|
|
||||
|
4
位 |
||||
| 0.571% 全疾病保障付き |
0.440%
|
0円
|
借入額×2.2%
|
|
| 【住信SBIネット銀行の住宅ローンのメリット・おすすめポイント】 ・三井住友信託銀行とSBIホールディングスが設立したネット銀行で、変動金利の低さではトップクラス ・通常の団信に加えて、全疾病保障（8疾病+病気・ケガ）を無料で付帯しているので、おとくな商品と言える ・女性には、がんと診断されると30万円が支給される保障も無料で付けている ・審査結果によっては、表面金利に年0.1％を上乗せする |
||||
| 【関連記事】住信SBIネット銀行の住宅ローンの金利、手数料、ポイント、注意点は？ | ||||
|
|
||||
| ※実質金利は、借入金額3000万円、借入期間35年、団信加入、元利均等返済、ボーナス払いなし、最優遇金利を適用として、実質金利を計算。固定期間終了後は変動金利を選択（現在の水準が継続と仮定）。実質金利の計算法はこちら。諸費用は、事務手数料等、保証料とする。保証料は、大手銀行の一般的な保証料率を記載しているので、銀行によっては違う保証料率となる。主要銀行・金融機関の主な商品を対象とし、ランキングに掲載するのは各銀行の商品の中で最も実質金利が低い商品のみとする。ホームローンドクター代表の淡河範明氏の協力で作成。 | ||||
| 新生銀行は来店不要 | フラット35ならアルヒ | 三井住友銀行はアプリが便利 |
 |
|
|
| 団信保険料・保証料が0円。トータルでおトク⇒関連記事はコチラ | アルヒなら、フラット35の手数料・金利が安い⇒関連記事はコチラ | Web申込なら低金利でおとく！⇒関連記事はコチラ! |
132銀行の住宅ローンを比較＞＞返済額シミュレーションで、全銀行の金利を一気に比較・調査ネット銀行 イオン銀行 auじぶん銀行 PayPay銀行 新生銀行 住信SBIネット銀行 ソニー銀行 楽天銀行 都市銀行 三井住友銀行 三菱ＵＦＪ銀行 みずほ銀行 りそな銀行 信託銀行 ・その他金融機関 アルヒ SBIマネープラザ 三井住友信託銀行 三菱UFJ信託銀行 中央労働金庫 東北ろうきん 新潟ろうきん 長野ろうきん 静岡ろうきん 北陸ろうきん 東海ろうきん 近畿ろうきん 中国ろうきん 四国ろうきん 九州ろうきん 沖縄ろうきん 優良住宅ローン JAバンク埼玉<JAさいたま> 地方銀行 北海道銀行 青森銀行 みちのく銀行 秋田銀行 北都銀行 荘内銀行 山形銀行 岩手銀行 東北銀行 七十七銀行 東邦銀行 群馬銀行 足利銀行 筑波銀行 常陽銀行 埼玉りそな銀行 武蔵野銀行 千葉銀行 横浜銀行 千葉興業銀行 きらぼし銀行 第四北越銀行 静岡銀行 山梨中央銀行 八十二銀行 北陸銀行 富山銀行 北國銀行 福井銀行 スルガ銀行 清水銀行 大垣共立銀行 十六銀行 三重銀行 百五銀行 滋賀銀行 京都銀行 関西みらい銀行 池田泉州銀行 南都銀行 紀陽銀行 但馬銀行 鳥取銀行 山陰合同銀行 中国銀行 広島銀行 山口銀行 阿波銀行 百十四銀行 伊予銀行 四国銀行 福岡銀行 筑邦銀行 佐賀銀行 十八親和銀行 肥後銀行 大分銀行 宮崎銀行 鹿児島銀行 琉球銀行 沖縄銀行 西日本シティ銀行 北九州銀行 第二地方銀行 北洋銀行 きらやか銀行 北日本銀行 仙台銀行 福島銀行 大東銀行 東和銀行 栃木銀行 京葉銀行 東日本銀行 東京スター銀行 神奈川銀行 大光銀行 長野銀行 富山第一銀行 福邦銀行 静岡中央銀行 愛知銀行 名古屋銀行 中京銀行 第三銀行 みなと銀行 島根銀行 トマト銀行 もみじ銀行 西京銀行 徳島大正銀行 香川銀行 愛媛銀行 高知銀行 福岡中央銀行 佐賀共栄銀行 長崎銀行 熊本銀行 豊和銀行 宮崎太陽銀行 南日本銀行 沖縄海邦銀行 |
