「住宅ローン」の毎月返済額、総返済額（総支払額）をエクセルで計算する方法を紹介しよう。通常、銀行のシミュレーションサイトで計算する人は多いが、銀行によって仕様が違うため、あまり正確にシミュレーションすることはできない。結局、自分でシミュレーションしたほうが正確に比較できるのだ。さらに、手数料などを含む実質金利の計算方法も紹介するので、エクセルをマスターして、住宅ローンを高精度に徹底比較できるようになろう。
銀行のシミュレーションは、不正確なことも
不動産を購入する際は多くの人が住宅ローンを利用するが、住宅ローンを借りるうえで知っておきたいのが、「毎月返済額」や「総支払額」などだ。こうした計算は、各銀行・金融機関のシミュレーションでも可能だ。しかし実際には、自行の住宅ローンのシミュレーションしか計算できないものが多い。シミュレーションの仕様が違っていたり、中には不正確なシミュレーションもあったりする。
例えば、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%」になる。
ダイヤモンド不動産研究所のシミュレーションが便利
高機能なシミュレーションなら、エクセルは不要。（ダイヤモンド不動産研究所の「返済額シミュレーション」）
さて、住宅ローン償還表を自分で作成できただろうか。エクセルを使うことで住宅ローンのシミュレーションは簡単にできるようになったが、間違わないように入力するのは案外、手間がかかる。
さらに大変なのが、各銀行のサイトなどを見て、「表面金利」「固定期間終了後の金利」「諸費用」を調べること。各銀行で書式や表現方法が違うので、数値を拾い出すだけでも非常な手間となる。
そこで活用してほしいのが、ダイヤモンド不動産研究所の「返済額シミュレーション」だ。ダイヤモンド不動産研究所では、主要18銀行の500商品について、金利、諸費用をすべてデータベース化しており、借入金額や期間などを入力すれば、「総返済額」「実質金利」を高精度に自動計算できる。さらに、借入条件に合った住宅ローンをランキングで並べているので、最もお得な住宅ローンを簡単に見つけられる。
住宅ローンのシミュレーションで、総返済額だけでなく、実質金利まで計算してくれるサイトは少ない。
自分で住宅ローンを計算するのもいいが、ダイヤモンド不動産研究所のシミュレーションを利用すると、スピーディーに最もお得な銀行の住宅ローンを見つけられるので、参考にしよう。
【関連記事はこちら】＞＞返済額シミュレーションで、500商品からお得な住宅ローンを探せ！毎月返済額、実質金利まで試算！
| ◆住宅ローン実質金利ランキング[新規借入] |
| ◆住宅ローン実質金利ランキング[借り換え] |
 |
 |
| 【金利動向】 | 【住宅ローンの基礎】 |
| >>【最新版】金利動向 >>【翌月の金利】を予想 >> 2020年の金利動向 >> 変動金利の上昇時期は？ |
>> 基本「8カ条」 >>「審査」の基本 >>「借り換え」の基本 >> フラット35はどの銀行がいい？ |
| 【2020年6月最新版】競争が激しく、過去最低水準の低金利！ ◆「変動金利」住宅ローン金利ランキング （新規借入） ※借入金額2500万円、借り入れ期間35年（詳細な条件は表組の下に記載） |
||||
| 順位 | 銀行名 <商品名> | |||
|
実質金利
（費用等含む） |
表面金利
（費用等除く） |
保証料
（税込） |
事務手数料
（税込） |
|
|
1
位 |
◆ジャパンネット銀行 <住宅ローン 全期間引下げプラン(自営業、市街化調整区域は不可)> | |||
| 0.529% |
0.399%
|
0円
|
借入額×2.2%
|
|
| 【ジャパンネット銀行の住宅ローンのメリット・おすすめポイント】 ネット銀行のジャパンネット銀行は2019年７月末に住宅ローンの貸し出しをスタート。最大の特徴は、業界最低水準という低い金利で、特に「変動金利」「10年固定金利」に強みがある。オプションの団体信用生命保険も豊富に取りそろえる。 |
||||
|
2
位 |
◆auじぶん銀行 <住宅ローン 全期間引下げプラン（新規借入）> | |||
| 0.540% 全疾病＋がん50％ |
0.410%
|
0円
|
借入額×2.2%
|
|
| 【auじぶん銀行の住宅ローンのメリット・おすすめポイント】 三菱ＵＦＪ銀行とKDDIが共同で立ち上げたネット銀行。変動金利の競争力が高く、業界トップクラスの低金利となっている。がんと診断されると住宅ローン残高が半分になる「がん50％保障団信」が無料付帯。ネットだけで契約を完了でき、仮審査は最短即日回答、契約は最短10日とい短期間での契約が可能だ。 |
||||
| 【関連記事】auじぶん銀行の住宅ローンの金利、手数料、ポイント、注意点は？ | ||||
 |
||||
|
2
位 |
◆SBIマネープラザ <ミスター住宅ローンREAL・通期引下げプラン（新規借入）> | |||
| 0.540% 全疾病保障付き |
0.410%
|
0円
|
借入額×2.2%
|
|
| 【SBIマネープラザの住宅ローンのメリット・おすすめポイント】 SBIマネープラザは、証券、保険、住宅ローンなどを取り扱う、SBIグループのマネー相談プラザ。支店において対面で相談できるので、初心者でも安心だ。変動金利が低い「ミスター住宅ローンREAL」（住信SBIネット銀行の商品）と、全期間固定金利が低い「ARUHIのフラット35」を取り扱っているので、2種類の住宅ローンを比較して申し込みできる。 |
||||
| 【関連記事】SBIマネープラザの住宅ローンの金利、手数料、ポイント、注意点は？ | ||||
|
|
||||
|
4
位 |
◆新生銀行 <住宅ローン 変動金利半年型タイプ・変動フォーカス（新規借入）> | |||
| 0.581% |
0.450%
|
0円
|
借入額×2.2%
|
|
| 【新生銀行の住宅ローンのメリット・おすすめポイント】 2019年7月の商品改訂に伴い、金利を大きく引き下げた。「変動フォーカス」は他の商品と違って、手数料は借入金額×2.2％と高めだが、金利が低いため、競争力がある。過去に繰り上げ返済で期間短縮した場合、入院時などにその期間だけ元本返済を止められるサービスもある。 |
||||
| 【関連記事】新生銀行の住宅ローンの金利、手数料、ポイント、注意点は？ | ||||
|
|
||||
| ※実質金利は、借入金額3000万円、借り入れ期間35年、団信加入、元利均等返済、ボーナス払いなし、最優遇金利を適用として、実質金利を計算。固定期間終了後は変動金利を選択（現在の水準が継続と仮定）。実質金利の計算法はこちら。諸費用は、事務手数料等、保証料とする。保証料は、大手銀行の一般的な保証料率を記載しているので、銀行によっては違う保証料率となる。主要18銀行・金融機関の主な商品を対象とし、ランキングに掲載するのは各銀行の商品の中で最も実質金利が低い商品のみとする。ホームローンドクター代表の淡河範明氏の協力で作成。 | ||||
