エクセルで住宅ローンの返済シミュレーション！ コピペでカンタン作成

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

ネットを探すといろんな返済シミュレーションができるサイトがあるけど使い勝手がイマイチ。
もし、あなたのパソコンにエクセルがあるなら計算させてみましょう。一度、エクセルで作っておけば繰り上げ返済をするときにどのくらい期間と利息が少なくなるかひとめで分かるようになります。

①借入額、②金利、③何年ローンの3つだけで毎月の返済額と利子をエクセルで超簡単に計算できます。

住宅ローンの返済額と利子をエクセルシートにする。

エクセルの計算機能を使って住宅ローンの返済額を計算するシートを作ってみます。数式をコピペするだけであなた用の住宅ローン返済シートができあがります。

❶フォーマットを作成

下の4行をA列にコピペします。

住宅ローン借入額
金利
何年ローン
毎月の返済

 

❷毎月の返済の式

下の式を毎月の返済の横（B4）にコピペ

=INT(PMT(B2%/12,B3*12,-B1*10000))

コピペすると #NUM! と表示されますが気にしないでください。

住宅ローンは、元利均等返済で計算します。

元金均等返済とは、毎月同じ額を支払い、初めは利息の返済が多いけど支払うごとに元金の比率が多くなっていく支払い方式です。

毎月、一定額で返済しているひとは元利均等返済です。

 

❸借入額・金利・金利を入力

住宅ローン借入額 万円（B1）、金利％（B2）、ローン年（B3）を入力します。

例として、3000 / 0.8 / 35 と入力すると毎月の返済額が計算されます。

3000 万円を金利 0.8 %で 35 年ローンのときは、毎月の返済額は 81,918円のようです。

 

❹フォーマットをコピペ

A6 → 返済日、B6 → 返済額、C6 → 元金、D6 → 利息、E6 → =B1*10000

緑の枠のようになっていることを確認してください。E は自動で計算されます。

 

❺計算式をコピペ

それぞれの計算式をコピペしてください。

返済額の下 =IF(D7>0,$B$4,0)
元金の下 =INT(B7-D7)
利息の下 =IF(INT(E6*$B$2*1/12/100)<0,0,INT(E6*$B$2*1/12/100))
金額の下 =IF(INT(E6-C7)<0,0,INT(E6-C7))

計算されて、緑枠のようになります。

 

6.　計算式をひっぱる
B7～E7を選択してカーソルが＋になったら下にずーーっとローンの支払い回数分を引っ張ります。35年 ローンだと427行です。

参考

• 20年 247行
• 25年 307行
• 30年 367行
• 35年 427行

 

7.　返済日を入力します。
二か月分の返済日を入力したら先ほどと同様に下にずーーーーーーーっと引っ張ります。

これで住宅ローン返済シートの完成です。

8．利子の合計も計算します
好きなセルに以下の式を代入してください。
返済額 =SUM(B5:B426)
利子合計 =SUM(D5:D426)

こんな感じで完成です。

 

住宅ローン返済エクセルのサンプル（エクセルファイル）

コピペすらめんどくせぇ…

というひとにはサンプルをご用意しました。
ご参考までに使ってみてください。

住宅ローン返済エクセル 2020/11/11 版

使い方

長く支払っていく住宅ローンですがどういう内訳で支払っているのか分かっていませんでした。

• 利子って一か月でどのくらい？
• 繰り上げ返済すると利子はどのくらい減る?
• 途中で金利が変わったら？
• 毎月の返済額を増やす（減らす）と？

エクセルを使ったこの住宅ローン返済シミュレーションがあれば手軽に自由に計算できます。

繰り上げ返済の計算をするときは？

B列の式に繰り上げ返済額を足してください。
例　300万円繰り上げ返済するとき
=IF(D9>0,$B$4,0)+3000000

住宅ローン減税の還付額が知りたいときは？

住宅ローン減税の還付額は年末の残高の1%です。
12月行のF列に　=E18*0.01　と入れてみましょう。

利用規約・免責事項

当記事の数式、エクセルシートは、計算結果を保証するものではなく、また、当該情報にもとづいて起こされた行動によって生じた損害・障害・不利益等に対する責任は負いかねます。