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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

まず、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回目までドラッグしてください。

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

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

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

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

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

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

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

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

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

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

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

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

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

つづいて、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回目までドラッグします。

なお、隣の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回目までドラッグします。

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

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

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

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

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

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

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

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

=SUM(H3:H422)

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

=SUM(G3:G422)

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

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

=SUM(E3:F422)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

まずは、A1とB1、C1とD1、E1とF1、G1とH1、I1とJ1、K1とL1をそれぞれ結合。A1セルに「返済期間」。E1セルに「審査金利」。I1セルに「返済比率」と記入します。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

つづいて、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セルまでドラッグします。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

審査金利とは？

住宅ローンの審査に使う金利のこと。実際の融資金利（実行金利）より厳しい数字になっている。

たとえば最近は変動金利型なら1%を切るが、審査金利は3%～4%ぐらいに設定している金融機関が多い。

返済比率(返済負担率)とは？

年間ローン返済額が、年収に占める割合のこと。年収400万円の人が年間120万円返済しているなら、返済比率は30%。

この場合の返済額には、住宅ローンだけでなくカーローンやカードローンなど全てのローンを含める。

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

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

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

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

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

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

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

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

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

60view

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

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

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

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

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

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

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

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