住宅ローンの予備知識

住宅ローン計算をエクセルでやる!シミュレーションを自作する方法

ナツダ
どうも、元住宅営業のナツダです。住宅ローンの仕組みや特徴をわかりやすく解説します。

これから住宅ローンを借りようとお考えの方。ちゃんと毎月やりくりできるか、心配ですよね。

「月々、いくらずつ返せばいいの?利息は、どれぐらい払うの?」とか「家計は苦しくならない?やっていけるの?」って思いますよね。

そんなときは、償還表(返済計画表)を自作すると不安がやわらぎますよ。ということで、今回は住宅ローンの計算エクセルでやる方法を解説しますね。

住宅ローンの返済シミュレーションをエクセルで自作する方法

住宅ローンの返済シミュレーションをエクセルで自作

住宅ローンを借りる前に、償還表返済計画表)を作っておきましょう。

まぁ、銀行に言えば作ってくれそうですが。何度も頼むのも気が引けるし、自作すればシミュレーションし放題です。

ナツダ
なお、エクセルで作る償還表は「目安」と考えてください。実際におこなう返済と比べて、誤差が出ます。

では、さっそく順を追って説明しますね。

償還表の見出しセルを作る

今回作成するのは「元利均等返済かつ固定金利」の住宅ローン償還表です。さっそく作成手順を解説します。

まず、行と列に「見出し」セルを作ります。

1行目のセルに以下の見出しを。A列には「基本情報」と1から420までのナンバーをふったセルを作ります。

1行目の見出し

  • 回(第何回目の返済か)
  • 返済月
  • 残返済回数
  • 金利
  • 月々返済額
  • 繰上返済
  • 利息分
  • 元金分
  • 借入残高

参考画像を載せておきますね。

償還表の見出しセル

規則性がある連続したセルは、ドラッグするだけで自動入力されます。エクセルの便利機能ですね。

「返済月」が自動入力されるようにする

つづいて「返済月」のセルが自動入力されるようにします。

まず、B3セルからB422セルまで選択し、右クリックで「セルの書式設定」を選択。「表示形式」のタブから「日付○○○○年○○月」を選びます。

償還表の見出しセル

つづいてB4セルに以下の計算式を書き、422行目(420回目の支払い)までドラッグします。

=IF(B3="","",IF(C4="","",EDATE(B3, 1)))

参考画像も載せておきますね。

償還表の見出しセル

これで、B3セルに「2019年6月」のように年月を記載すればあとは自動で入力されます。(ただし、基本情報が入力されるまでは空白を返す処理をしてます)

「残返済回数」が自動入力されるようにする

つづいて、C3セルに以下の計算式を記入します。

=IF(C2="","",IF(C2-1>=0,C2-1,""))

この数式も、420回目までドラッグしてください。

金利1

これで、基本情報の「残返済回数」欄に借入月数を入力すると以下は自動入力されます。

「金利」が自動入力されるようにする

まず、D2セルからD422セルまで選択します。選択セル上で右クリックして、表示形式から「パーセンテージ」を選びます。

金利2

つづいて、D3セルに以下の計算式を記入します。

=IF(C3="","",IF(D2="","",D2))

金利3

さらに、D4セルに以下の計算式を記入します。

=IF(C4="","",D3)

この数式を、420回目までドラッグします。

金利2

これで、基本情報が未入力のときは空白を。記入されたら、以下の金利は自動で入力されるようになりました。

「月々返済額」が自動入力されるようにする

月々の返済額は「返済回数、金利、借入額」が決まれば自動で計算されるようにします。

ここではPMT関数というのを使いますが、この関数が償還表の肝になります。

まずは、E3セルからE422セルを選択します。選択セル上で右クリックして、表示形式から「通貨」を選びます。

ナツダ
小数点以下の桁数は「0」。
記号は「」にしておきましょう。

月々返済額1

つづいて、E3セルに以下の計算式を記入します。

=IF(I2="","",IF(C2="","",IF(D2="","",INT(PMT(D2/12,C2,-I2)))))

さらに、E4セルに以下の計算式を記入します。

=IF(I3="","",IF(I3=0,"",IF(H3*2>=I3,I3+I3*D4/12,E3)))

この数式を、420回目までドラッグします。

月々返済額2

なお、隣のF列(繰上返済)は何も記入しなくてOKです。

「利息分」が自動入力されるようにする

返済額内の利息を計算したいときは、IPMT関数という専用の関数を使う方法があります。今回はあえてその関数を使わず、利息と借入残高から計算しようと思います。

なぜなら、金利と利息の関係が理解しやすいから。「金利が低い銀行で借りるのって、大事なんだな」と感じてもらいやすいと思います。

ということでまず、G3セルに以下の計算式を記入します。

=IF(E3="","",I2*D3/12)

この数式を、420回目までドラッグします。

エクセルで利息の計算

エクセルの計算式を見ていただくとわかるとおり、利息の計算はカンタンです。わかりやすく書くと、こんな感じ。

利息 = 借入残高 × 金利 ÷ 12

住宅ローンの金利は「年利」です。ですから金利を12(か月)で割る必要があります。

この計算式はとても単純ですが、とても大事なことを示してます。つまり「利息は、借入額と金利で決まる」ということ。

少しでも借入額を減らし少しでも低い金利で借りると、支払う利息も減るのです。

「元金分」と「借入残高」が自動入力されるようにする

もうすぐ完成です。数式の入力は、これで最後です。

まず「元金分」から。月々の返済額から利息を引けば、元金が出せます。

H3セルに、以下の計算式を入力しましょう。

=IF(G3="","",E3-G3+F3)

この数式を、420回目までドラッグします。

エクセルで元金分の計算

つづいて「借入残高」もやってしまいましょう。前回の借入残高から今回の利息分を引くと、最新の借入残高が出せます。

I3セルに、以下の計算式を入力しましょう。

=IF(H3="","",I2-H3)

この数式も、420回目までドラッグします。

エクセルで借入残高の計算

以上で数式の入力は終わりです。

最後に、償還表の体裁を整えましょう。

住宅ローン償還表の体裁を整える

このままでは見にくいので、罫線を引いたりセルに色をつけてみましょう。

入力不要なセルは斜線を引きます。(セルの上で右クリック → セルの書式設定 → 罫線タブで線が引けます)

見出しセルはグレーで着色。入力が必要なセルは、薄いブルーで着色してみました。

ナツダ
B3セルとC2セル、D2セル、I2セルの入力が必須です。繰り上げ返済の列は、必要に応じて入力します。

エクセルで作った住宅ローン償還表1

最後に元金や利息の合計、総返済額がわかる欄を作ります。

まず、元金の合計欄を作り以下の計算式を記入します。

=SUM(H3:H422)

つづいて、利息の合計欄に以下の計算式を記入します。

=SUM(G3:G422)

最後に総返済額。先に計算した元金の合計と利息の合計を足せば、算出できます。

ですが、ここはあえて「月々返済額」と「繰上返済」を合計してみましょう。

=SUM(E3:F422)

エクセル償還表がちゃんとできていたら「元金と利息の合計=総返済額」になってるはず。

試しに、着色したセルに数字を入れてみてください。

エクセルで作った住宅ローン償還表1

どうでしょう?できましたか?

エクセルで住宅ローンの早見表を作る方法

償還表は、住宅ローンの返済をシミュレーションするのに役立ちます。あわせて「早見表」があるともっと便利ですよね。

たとえば、金利や借入年数の違いで返済額がどう変わるかひと目でわかる早見表とか。月々の返済額と金利から借入額がわかる早見表とか。

そんな便利な早見表も、エクセルでカンタンに作れます。さっそく作り方を説明していきますね。

借入額100万円あたりの返済額早見表

借入額100万円あたりの返済額早見表

では、金利や借入年数の違いで返済額がどう変わるか。早見表を作って確認してみましょう。

金利は0.50%から1.50%まで、0.01%きざみで。借入年数は20年から35年まで、1年きざみで作ってみますね。

まず、B1セルからQ1セルまで選択して右クリック。書式設定を選びます。表示形式の中から「ユーザー定義」を選んで、種類欄に「0"年"」と記入します。

エクセル返済額早見表1

つづいて、B1セルに「20」。C1セルに「21」と記入します。(先ほどの処理で、勝手に「年」が自動入力されるはずです)

こんどは、記入済みのB1セルC1セルを選択したらQ1セルまでドラッグします。これで「35年」までの見出しセルができます。

エクセル返済額早見表2

次はA2セルからA122セルまで選択して、右クリック。表示形式の中から「パーセンテージ」を選んで、小数点以下の桁数に「2」と記入します。

エクセル返済額早見表3

つづいて、A2セルに「0.50」。A3セルに「0.51」と記入します。(先ほどの処理で、勝手に「%」が自動入力されるはずです)

借入年数のときと同じく、A2セルA3セルを選択したらA102セルまでドラッグします。これで「1.50%」までの金利が連続してならびます。

エクセル返済額早見表3

お次は、一気に早見表の中身を作っていきます。

まずB2セルからQ102セルまで選択し、右クリック。表示形式は「通貨」。小数点以下の桁数は「0」。記号は「」を選んでおきます。

エクセル返済額早見表4

B2セルに、以下の計算式を記入します。

=PMT($A$2/12,B1*12,-1000000)

この数式をQ2セルまでドラッグします。

エクセル返済額早見表5

B2セルからQ2セルを選択したまま「Ctrl + H」を押して「検索と置換」を出しましょう。

検索する文字列には「$A$2」を。置換後の文字列には「A2」を入力します。

「すべて置換」を押せば置換が完了し「16件置換しました」とメッセージが出てきます。

エクセル返済額早見表6

置換後の数式には、借入年数が書かれたセルの指定部分があります。そのセルのアルファベットと数字の前に「$」を付けていきます。

たとえば、B2セルの計算式は以下のように「$」を付けます。

=PMT(A2/12,$B$1*12,-1000000)

C3セルの計算式は、以下のようになります。

=PMT(A2/12,$C$1*12,-1000000)

こんな感じで、2行目の計算式をQ2セルまで全部書き換えます。

エクセル返済額早見表7

すべての計算式を書き換え終わったら、もう一度B2セルからQ2セルまで選択します。

選択したら、右下の点をQ102セルまでドラッグします。

エクセル返済額早見表8

これで「借入額100万円あたりの返済額」がすべて記入されました。たとえば3000万円借りたいときは、表の数字に30をかければ月々の返済額がわかります。

あとは罫線などで体裁を整えて完成です。

エクセル返済額早見表9

これで、同じ金利で借入年数を変えた場合のシミュレーションとか。同じ借入年数で金利が違う銀行を比較することが、カンタンにできます。

月々の返済額からみた借入可能額の早見表

月々の返済額からみた借入可能額の早見表

さて、月々の返済額から(金利に応じて)どれぐらい借りられるか。早見表を作って確認してみましょう。

月々の返済額は5万円から15万円まで、1万円きざみで。金利は0.50%から1.50%まで、0.01%きざみで作ってみますね。

まずは、A1B1C1D1E1F1G1H1I1J1K1L1をそれぞれ結合。A1セルに「返済期間」。E1セルに「審査金利」。I1セルに「返済比率」と記入します。

エクセル借入可能額早見表1

つづいて、C1セルを右クリック。セルの書式設定を選び、表示形式タブで「ユーザー定義」を選択します。

種類の欄に「0"年返済"」と記入します。

エクセル借入可能額早見表2

次はG1セルK1セルを選択して、右クリック。セルの書式設定を選び、表示形式タブで「パーセンテージ」を選択します。

小数点以下の桁数は「0」にしておきます。

エクセル借入可能額早見表3

試しにC1セルに「35」。G1セルに「3」。K1セルに「30」と入力してみてください。単位が自動で入力されたでしょうか?

つづけて、A3セルに「月々返済額」と記入。A4セルA5セルを結合し「年収の目安」と記入します。

次にB3セルからL3セルまで選択し、右クリック。書式設定から表示形式を選び「ユーザー定義」の種類に「0"万円"」と記入します。

エクセル借入可能額早見表4

試しにB3セルに「5」。C3セルに「6」と、整数を順番に記入していってください。L3セルに「15」まで書けたらOK。単位が自動で入力されたでしょうか?

エクセル借入可能額早見表5

つづいて、6Aセルから106Aセルまで選択し、右クリック。書式設定から表示形式を選び「パーセンテージ」を選択します。

小数点以下の桁数は「2」にしておきます。

エクセル借入可能額早見表6

ためしにA6セルに「0.50」。A7セルに「0.51」と入力してみましょう。単位が自動で入力されましたか?

問題なければA6セルA7セルを選択します。つづけて、右下の点をA106セルまでドラッグしてください。

「1.50%」まで入力できたらOKです。

エクセル借入可能額早見表7

さて、一気に借入可能額を計算していきます。エクセルではPV関数というのが用意されてるので、これを使います。

B6セルに以下の計算式を入力します。

=PV($A$6/12,$C$1*12,-B3)

この数式をL6セルまでドラッグします。

エクセル借入可能額早見表8

B6セルからL6セルまで選択されてると思うので、その状態のまま「Ctrl + H」を押します。「検索と置換」ウィンドウが出ましたか?

検索する文字列に「$A$6」。置換後の文字列に「A6」と記入して「すべて置換」を押します。

11件を置換しました」と出たらOKです。

エクセル借入可能額早見表9

つづいて、B6セルからL6セルまでの数式を書き換えます。式の最後が「,-B3)」のようになってるので、アルファベットと数字の前に「$」を付けます。

たとえば、B6セルの計算式は以下のように直します。

=PV(A6/12,$C$1*12,-$B$3)

C6セルは、以下のように直します。

=PV(A6/12,$C$1*12,-$C$3)

同じようにL6セルまで直します。すべての数式を書き換えたら、B6セルからL6セルまで選択。

右下の点をL106セルまでドラッグします。

エクセル借入可能額早見表10

B6セルからL106セルまで選択されてると思うので、どこか選択セルの上で右クリック。書式設定から表示形式タブを選び「ユーザー定義」を選択します。

種類の欄に「#,##0"万円"」と記述しておきましょう。

エクセル借入可能額早見表11

これで、月々の支払額と金利から借入額がわかる早見表ができました。つづけて、年収の目安欄を埋めていきましょう。

まずB4セルからL4セルまで選択して、右クリック。書式設定から表示形式を選び「ユーザー定義」を選択します。

種類の欄に「#,##0"万円~"」と記入しましょう。

エクセル借入可能額早見表12

つづいて、B5セルからL5セルまで選択して、右クリック。書式設定から表示形式を選び「ユーザー定義」を選択します。

種類の欄に「#,##0"万円"」と記入しましょう。

エクセル借入可能額早見表13

次は、B4セルに以下の計算式を記入します。

=PMT($G$1/12,$C$1*12,-B106)*(12/$K$1)

この数式をL4セルまでドラッグします。

エクセル借入可能額早見表14

最後には、B5セルに以下の計算式を記入します。

=PMT($G$1/12,$C$1*12,-B6)*(12/$K$1)

この数式をL5セルまでドラッグします。

エクセル借入可能額早見表15

これで早見表の入力が終わりました。罫線や色を使って、表の体裁を整えましょう。

返済期間審査金利返済比率」は数字を変えることができます。パッと見てわかるように、セルの色を変えておきましょう。

エクセル借入可能額早見表16

ところで「審査金利、返済比率」とは何か、ごぞんじですか?ちょっと意味を解説しておきます。

審査金利とは?
住宅ローンの審査に使う金利のこと。実際の融資金利(実行金利)より厳しい数字になっている。
たとえば最近は変動金利型なら1%を切るが、審査金利は3%~4%ぐらいに設定している金融機関が多い。
返済比率(返済負担率)とは?
年間ローン返済額が、年収に占める割合のこと。年収400万円の人が年間120万円返済しているなら、返済比率は30%。
この場合の返済額には、住宅ローンだけでなくカーローンやカードローンなど全てのローンを含める。

銀行は、融資上限額を算出するのに審査金利返済比率を参考にしています。今回の早見表の「年収の目安」も、この数字を使って計算しました。

といっても、お勤め先や勤続年数など他の要因も審査に影響します。あくまで目安としてご活用ください。

実際の家計でシミュレーションすることの重要性

エクセルで住宅ローンのシミュレーションや早見表を作ってみて、どうでしたか?けっこう、いろんなことがわかりますよね。

毎月、いくらずつ返すのか?どれぐらい利息を払うのか?希望の返済額だと、どれぐらい借入できるのか?

いろんな数字を当てはめて試してみると、だんだん返済のイメージがわいてきます。

ナツダ
でも、いまいちリアルさに欠けますよね?

ここで、もうひとつシミュレーションしていただきたいことがあります。それが何かというと「家計からみたローン返済の圧迫感」です。

返済比率25%以上は住宅ローンが破たんしやすくなる、というデータもあります。でも、あなたの家計にも当てはまるかわかりません。

ぜひ以下の記事を参考に、借り入れ後の家計簿シミュレーションをやてみてください。そして今と住宅購入後で住宅関連費がどう変わるのか、比較してください。

60view
住宅ローン、40歳で年収400万円だけど借りて大丈夫ですか?

「40歳で年収400万円だけど、住宅ローンを借りても大丈夫かな?」そうお考えの方が、けっこう多いです。 もちろん、住宅ロ ...

ちなみに「住宅関連費」とは、たとえば以下のものを指します。

  • 家賃
  • 共益費
  • 駐車場代
  • 住宅ローン返済
  • 火災保険・地震保険
  • 固定資産税・都市計画税
  • 管理組合費(マンションの場合)
  • 修繕積立金
  • 水道光熱費

基本は年間で、今より住宅購入後の住宅関連費を低くすること。そうすれば、あまり家計を圧迫せず新居を手に入れられるでしょう。

【まとめ】住宅ローン計算をエクセルでやる!シミュレーションを自作する方法

住宅ローンの償還表(返済計画表)をエクセルで作る方法について、解説しました。

自作の償還表があれば、いつでもシミュレーションしたい放題です。返済イメージがつかみやすくなるので、作ってみてることをオススメします。

今と比べて住宅購入後の家計がどうなるのか、比較することも大事です。家計に無理のない返済額を把握したうえで、シミュレーションを進めていきましょう。

50万円、カンタンに節約できるかも?

住宅購入費用の節約のコツ

知ってますか?一番、住宅購入費を節約できる方法

ナツダ
買う家の価格を下げる?そんなことしなくても、大丈夫です。

じつは、営業マンが家を買う人にちゃんと説明しない方法があります。それは、住宅ローン金利です。

たとえば、3000万円を30年ローンで借りたとき、金利1.0%と0.9%で返済額がいくら違うか知ってますか?

ナツダ
答えは、49万円です。

わずか0.1%違うだけでこんなに差がでるのに……みな様、あまりにもアッサリ銀行を選ばれます。きっと、営業マンが提携銀行しか紹介しないからでしょう。

もし、あなたが節約好きなら、自分で徹底的に金利が低い銀行を探してみては?その努力の見返りは、とても大きいですよ。

以下の記事で「住宅ローンの選び方」についてまとめました。家を買うときの参考にどうぞ。

住宅ローンの選び方と注目すべきポイント

  • この記事を書いた人
  • 最新記事
ナツダ アツシ

ナツダ アツシ

高気密高断熱を得意とする注文住宅の会社で、約8年間営業職を経験。インテリアコーディネーターの資格保有。自宅の分譲マンションを、スケルトンからリノベーションして居住中です。「家探し」や「家づくり」のノウハウを、わかりやすく解説します。

-住宅ローンの予備知識

© 2020 Home Sweet Home