PR

住宅ローンの返済シミュレーションをExcelで作ってみた(Excel2019)

住宅ローンの返済シミュレーションをExcelで作ってみた(Excel2019)

人生で最も大きな買い物は住宅でしょう。
その時に、多くの人が住宅ローンを借ります。

借入金額や返済年数など色々なパターンがありとても迷います。
ローンの返済シミュレーションをExcelで作って無理のない返済額を考えてみましょう。
これは住宅だけでなく色々なローンの返済シミュレーションができます。

まず、住宅ローンの基本を理解しましょう。
住宅ローンは毎月の返済額を一定額にする元利均等返済と、毎月の返済額のうち元金が一定の元金均等返済があります。
元利均等返済は毎月の返済額が一定なので返済計画が立てやすくなります。
元金均等返済は毎月の返済額が少しずつ減っていきますが、当初の返済額がとても多くなります。
そのため、住宅ローンを借りる人の多くが元利均等返済を選びます。

ここでは、元利均等返済の住宅ローン返済シミュレーションを作成します。

例として、借入金額1,000万円  返済期間30年  金利2%で作成してみます。

毎月の返済額を計算します。

Excelで住宅ローンシミュレーション

A.表を作ります。

1.セルA1に「住宅ローン返済額」と入力します。

セルA1に「住宅ローン返済額」と入力します

2.セルA3に「借入額」B3に「期間(年)」C3に「年利(%)」D3に「毎月の返済額」と入力します。

セルA3に「借入額」B3に「期間(年)」C3に「年利(%)」D3に「毎月の返済額」と入力します

3.セルA3からD3までの幅を「15」にします。

セルA3からD3までの幅を「15」にします

4.セルA3からD4まで枠線を引きます。

セルA3からD4まで枠線を引きます

5.セルA3からD3まで色を付けます。

セルA3からD3まで色を付けます

6.セルA3からD3まで中央揃えにします。

セルA3からD3まで中央揃えにします

7.セルA3を【桁区切りスタイル】にします。

8.セルB4を選択して、書式設定の【表示形式】からの【ユーザー設定】で「#,##”年”」と入力します。

セルB4を選択して、書式設定の【表示形式】からの【ユーザー設定】で「#,##"年"」と入力します
セルB4を選択して、書式設定の【表示形式】からの【ユーザー設定】で「#,##"年"」と入力します

9.OKをクリックします。

10.同様にセルC4を選択して【ユーザー設定】で「#,##”%”」と入力します。

同様にセルC4を選択して【ユーザー設定】で「#,##"%"」と入力します

B.計算式を入力します。

計算式は =PMT(利率,期間,現在価値,将来価値,支払期日)

利率に月利  期間に返済期間(月数)  現在価値に借入金額  将来価値は0(完済する)  支払い期日は省略

1.仮の数値を入力します。(借入額10,000,000 期間30 年利2)

仮の数値を入力します。(借入額10,000,000 期間30 年利2)

2.セルD4を選択します。 

セルD4を選択します

3.【数式】タブの【関数の挿入】をクリックします。

【数式】タブの【関数の挿入】をクリックします

4.【関数の分類】から【財務】を選択します。

【関数の分類】から【財務】を選択します

5.一覧から【PMT】を選択します。

一覧から【PMT】を選択します

6.OKをクリックします。

PMT

7.利率には月利率を入力します。年利を12で割って更に100で割ります。年利のセルを選びます。「(C4/12)/100」

利率には月利率を入力します。年利を12で割って更に100で割ります。「(C4/12)/100」

8.期間は月数になりますので返済年数に12を掛けます。期間のセルを選びます。「B4*12」

期間は月数になりますので返済年数に12を掛けます。「B4*12」

9.現在価値は「借入額」になります。借入額を入力するセルを選びます。

現在価値は「借入額」になります

10.将来価値は完済をするなら0になります。「0」

将来価値は完済をするなら0になります。「0」

11.OKをクリックします。

12.返済額がマイナス表示されています。

返済額がマイナス表示されています

13.マイナス表示を解消するために関数の頭に「-」を入れます。

マイナス表示を解消するために関数の頭に「-」を入れます

14.借入金額や返済年数、金利を色々入力すればシミュレーションできます。

おまけ

ChatGPTで住宅ローンの計算について聞いてみた

質問内容

「借入金額10000000 借入年数30年 金利3% 毎月返済額」

回答

借入金額が10000000円、借入期間が30年、金利が年利3%の場合、毎月返済額を計算するには、ExcelのPMT関数を使うことができます。

以下がPMT関数の構文です。

 
=PMT(rate,nper,pv,[fv],[type])
  • rate: 利率(年利÷12で月利を求める)
  • nper: 返済期間(月数)
  • pv: 現在価値(借入金額)

これを用いて、毎月の返済額を求める式は以下のようになります。

 
=PMT(3%/12,30*12,-10000000)

ここで、利率を月利にするために、年利の3%を12で割った値(0.0025)を使っています。また、期間を月数にするために、年数の30年に12を掛けた値(360)を使っています。最後に、借入金額を-10000000で表しています。これはExcelの関数が借入金額を負の値として扱うためです。

上記の式を計算すると、毎月の返済額は約 42,587円となります。

住宅ローンの返済シミュレーションをExcelで作ってみた(Excel2019)

PR

タイトルとURLをコピーしました