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

銀行
スポンサーリンク

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

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

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

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

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

1.まずはフォーマットを作ります。下の4行をA列にコピペします。
住宅ローン借入額
金利
何年ローン
毎月の返済

 

2.毎月の返済の右のセル(B4)に下の式をコピペします。

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

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

元利均等返済で計算します。
住宅ローンの元金均等返済とは、毎月同じ額を支払い、初めは利息の返済返済が多いけど支払うごとに元金の比率が多くなっていく支払い方で毎月、一定額で返済しているひとは元利均等返済です。

 

3.住宅ローン借入額 万円 (B1)、金利%(B2)、ローン年(B3)を入力してみましょう。

例として、3000 / 0.8 / 35 と入力してみます。入力すると毎月の返済額が計算されます。
3000万円を金利 0.8%で 35年ローンのときは、毎月の返済額は81,918円のようです。

ローン年じゃなくて返済回数で入力したいときは、B3セルに
=返済回数/12 (例:420回ならば、=420/12 )と入力してください。

 

4.フォーマットをコピペして作ります。
A6 → 返済日、B6 → 返済額、C6 → 元金、D6 → 利息、E6 → =B1*10000

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

 

5.計算式をコピペします
それぞれに = 〜 )  をコピペしてください。
返済額の下 =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 と入れてみましょう。

利用規約・免責事項

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

 

Microsoft Excel 2019 オンラインコード版Win10/mac対応
マイクロソフト

MacとWinで両方で使える2ライセンスです。買取タイプなので毎月の費用はかかりません。
自宅のパソコンにもエクセルがあるやっぱり便利です。