人生で最も大きな買い物は住宅でしょう。
その時に、多くの人が住宅ローンを借ります。
借入金額や返済年数など色々なパターンがありとても迷います。
ローンの返済シミュレーションをExcelで作って無理のない返済額を考えてみましょう。
これは住宅だけでなく色々なローンの返済シミュレーションができます。
まず、住宅ローンの基本を理解しましょう。
住宅ローンは毎月の返済額を一定額にする元利均等返済と、毎月の返済額のうち元金が一定の元金均等返済があります。
元利均等返済は毎月の返済額が一定なので返済計画が立てやすくなります。
元金均等返済は毎月の返済額が少しずつ減っていきますが、当初の返済額がとても多くなります。
そのため、住宅ローンを借りる人の多くが元利均等返済を選びます。
ここでは、元利均等返済の住宅ローン返済シミュレーションを作成します。
例として、借入金額1,000万円 返済期間３０年 金利２％で作成してみます。
毎月の返済額を計算します。
Excelで住宅ローンシミュレーション
Ａ．表を作ります。
１．セルＡ１に「住宅ローン返済額」と入力します。
２．セルＡ３に「借入額」Ｂ３に「期間（年）」Ｃ３に「年利（％）」Ｄ３に「毎月の返済額」と入力します。
３．セルＡ３からＤ３までの幅を「１５」にします。
４．セルＡ３からＤ４まで枠線を引きます。
５．セルＡ３からＤ３まで色を付けます。
６．セルＡ３からＤ３まで中央揃えにします。
７．セルＡ３を【桁区切りスタイル】にします。
８．セルＢ４を選択して、書式設定の【表示形式】からの【ユーザー設定】で「#,##”年”」と入力します。
９．ＯＫをクリックします。
10．同様にセルＣ４を選択して【ユーザー設定】で「#,##”％”」と入力します。
Ｂ．計算式を入力します。
計算式は ＝ＰＭＴ（利率，期間，現在価値，将来価値，支払期日）
利率に月利 期間に返済期間（月数） 現在価値に借入金額 将来価値は０（完済する） 支払い期日は省略
１．仮の数値を入力します。（借入額10,000,000 期間３０ 年利２）
２．セルＤ４を選択します。
３．【数式】タブの【関数の挿入】をクリックします。
４．【関数の分類】から【財務】を選択します。
５．一覧から【ＰＭＴ】を選択します。
６．ＯＫをクリックします。
７．利率には月利率を入力します。年利を１２で割って更に１００で割ります。年利のセルを選びます。「（Ｃ４/１２）/１００」
８．期間は月数になりますので返済年数に１２を掛けます。期間のセルを選びます。「Ｂ４*１２」
９．現在価値は「借入額」になります。借入額を入力するセルを選びます。
10．将来価値は完済をするなら０になります。「０」
11．ＯＫをクリックします。
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円となります。
PR
