家を買うなら!

元住宅営業が住宅購入のノウハウを徹底解説します

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

投稿日:2018年7月27日 更新日:

ナツダ
どうも、元住宅営業のナツダです。

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

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

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

では、さっそく説明していきます。

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

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

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

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

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

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

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

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

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

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%以上は住宅ローンが破たんしやすくなる、というデータもあります。
でも、あなたの家計にも当てはまるかわかりません。

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

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

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

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

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

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

まとめ

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

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

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

住宅資料請求のコツ、知ってる?

家の購入予算

知ってますか?
住宅資料請求のコツ

どのタイミングで資料請求?
どこでもらえばいい?
資料の見方は?活用方法は?

資料請求は、モデルハウスやオープンハウスに行く前がおすすめです!
その理由を以下の記事にまとめました。

家を買いたいけど、何から始めていいかわからない!
そんな方に、ぜひ知っていただきたい内容です。

住宅購入の参考にどうぞ。

建売住宅の資料請求のコツ
注文住宅の資料請求のコツ

厳選!これから家を買う人にオススメのお役立ちサイト

非公開物件の情報がもらえる資料請求サイト

関東で家を探すなら、イチオシのサイトです。

 送られてくる資料が豊富
 非公開物件も探せる
 新築も中古も探せる

東京都神奈川県千葉県埼玉県の物件情報が豊富です。新築も中古も、一戸建もマンションも、ご希望に合わせて資料請求できます。

要望を書く欄がありますので「大手ハウスメーカーの一戸建て」とか「築10年以内の大手ハウスメーカー中古物件」など記入して申し込むことができます。

提案資料が豊富で、大手不動産情報サイトにも載らない非公開物件資料も手に入ります。

注文住宅の豊富な資料が請求できるサイト

大手の建売買うなら注文住宅という選択肢も!

 間取りの要望を叶えやすい
 構造の品質チェックがしやすい
 ただし、建売より手間がかかる

大手ハウスメーカーの建売住宅は、パワービルダーの建売住宅と比べて高価です。その価格帯なら、地域で頑張っている住宅会社や工務店の注文住宅も視野に入ってきます。

注文住宅は手間がかかりますが、希望にかなった理想の住宅が作れます。こちらも資料請求してみて、建売と比較してみてはいかがでしょうか?

初めから、優しく、初心者に無料でアドバイス

分譲・注文住宅だけでなくマンションもOK!

 何度でも無料で相談できる
 理想の家に出会えるまでサポート
 専門家監修の講座が受講できる

家が欲しいけど、何からしたらわからない」「家づくり、失敗しそうでコワイ」って悩んでませんか?そんなときは「LIFULL HOME'S すまいの窓口」がイチオシです。

店頭窓口で、中立的立場のアドバイザーが無料で解決策を教えてくれます。ただし、店舗があるのは今のところ「東京・神奈川・千葉・埼玉」と限られた地域のみ。

それ以外の地域では、電話サポート(無料)が受けられます。予約して「家が欲しいんですけど、私いったい何からしたらいいんでしょう?」って聞けば親切的確に教えてくれますよ。

人気記事

149,026view

ナツダどうも、元住宅営業のナツダです。 さて、ランキング1位の建売住宅メーカーってどこの会社か知ってますか? いわゆる、パワービルダーってやつですね。 じつは、日本一のパワービルダーは1日100棟以上 ...

103,577view

ナツダどうも、元住宅営業のナツダです。 せっかく苦労して高い家を買ったのに、住んでるうちに後悔し始める・・・。 そんなの、絶対避けたいですよね! でも、けっこう多いのです。そういう方々。 あなたは、そ ...

101,275view

ナツダどうも、元住宅営業のナツダです。 いざ、「一戸建てを買おう!」と思っても、その高さに気絶しそうになりますよね。 「こんな大金の住宅ローンを組んで、ずっと返していけるんだろうか?」みたいな。 家計 ...

86,894view

ナツダどうも、元住宅営業のナツダです。 家を購入する年齢は何歳ぐらいが適齢か、考えたことありますか? 家は、何歳までに買わないといけない? 何歳から、買っても大丈夫? 家を買う人の平均年齢や平均年収っ ...

77,135view

ナツダどうも、元住宅営業のナツダです。 家を買うのって、かなり難しいですよね。 どんなところに注意したらいいのでしょう? 今回はステージ別に「建売住宅を買うときの注意点」をあげてみたいと思います。 最 ...

-住宅ローンの予備知識

Copyright© 家を買うなら! , 2019 All Rights Reserved.