エクセルを使って、住宅ローンの返済計画を作成する方法を紹介します。
「ローンの借入額」「返済年数」「金利(％)」の３つの項目を入力するだけで、「毎月のローン返済額」「利子総額」「毎月支払う元金」「毎月支払う利息」などを自動で算出してシミュレーションすることが可能です。
この記事では、上のExcelのローン返済シミュレーションの作成方法を紹介します。
加えて、エクセルに備わっている下の「ローン返済シート」という無料テンプレートのダウンロード方法と使い方も合わて紹介します。こちらもローンの返済条件を設定するだけで簡単にシミュレーション出来るのでお勧めです。
住宅ローンの返済方法
まず、ローン(借入金)の返済方法について紹介します。
返済方法には「元利均等返済」と「元金均等返済」という２種類の返済方法があります。
この記事で紹介するエクセルでのローン返済計画は「元利均等返済」の場合のシミュレーションです。まずこの２種類の返済方法の違いをご理解いただいた上で作成してください。
元利均等返済
「元利均等返済」は、毎回の返済額が一定となる返済方法です。
「元金均等返済」より返済する金額が多くなってしまいますが、毎回の返済額が一定のため、返済計画を立てやすいというメリットがあります。
計画の立てやすさや、「元金均等返済」より返済当初の負担額が小さいことから、一般的に「元利均等返済」を選択される利用者が多いです。
元金均等返済
「元金均等返済」は、毎回の支払う元金を一定にする返済方法です。
返済当初の返済額は多くなるというデメリットはありますが、支払う返済額の総額は「元利均等返済」より少なくなります。
PMT関数(ペイメント関数)の使い方
エクセルでローン返済シミュレーションを作成するために、PMT関数(ペイメント関数）という特殊な関数を利用するので、使い方を紹介します。
PMTは支払い意味する英語のペイメント(payment)のアルファベットからとってきています。
PMT関数は、一定利率の支払いが定期的に行われる場合の、ローンの定期支払額を算出しまするための関数です。
関数の構文と引数は以下の通りです。
＝PMT(利率, 期間, 現在価値, [将来価値], [支払期日])
利率：ローンの利率を指定します。
期間：ローンの支払回数の合計を指定します。
現在価値：ローンの現在価値、または元金を指定します。
[将来価値]：入力は任意です。最後の支払いを行った後に残る現金の収支を指定します。ローンの将来価値は 0 のため、今回この引数は利用しません。
[支払期日]：入力は任意です。いつ支払いが行われるのが期末か期首かを、数値の 0 (ゼロ) または 1 で指定します。今回この引数は利用しません。
ローン返済シミュレーションを作成する手順
それでは、ローン返済シミュレーションを作成する手順を紹介します。
ステップ１～ステップ５の各手順に沿って作成してください。
ボーナスでの返済を計画する場合は、この見出しでローン返済シミュレーションを作成した後に、『ボーナス返済のシミュレーション』の見出しを参考にしてください。
ステップ１：ローン返済の「条件」を入力
まずは、ローンの「借入額」「返済年数」「金利(％)」の３つの項目を入力します。
この項目は後で自由に条件を変更することが出来るので、仮の数値で問題ありません。
この記事の説明では「借入金」が20,000,000円、「返済期間」が30年、「金利」が1.0％という条件を入力します。
ステップ２：「毎月の返済額」を計算
ステップ１で入力した条件において、『毎月の返済額』を算出します。
F3セルに以下のPMT関数を挿入します。
=-PMT($C$7/12,$C$6*12,$C$5)
PMT関数にの先頭にマイナスを入力するのは、計算結果を＋で表示するためです。
PMT関数の結果として、「毎月の返済額」が64,328円と算出されます。
つまり、「借入金」が20,000,000円、「金利」が1.0％という条件で30年でローンを返済するためには、毎月定額で64,328円の返済が必要ということです。
ステップ３：「支払い回数」と「利息の合計」を計算
続いて、「支払い回数」と「利息の合計」を計算します。
「支払い回数」は１年で12回なので、返済期間(年)に12を掛けます。
=C6*12
「利息の合計」とは、元金以外に銀行などの金融機関に支払う利息の合計金額のことです。
そのため、「利息の合計」は、「毎月の返済額」×「支払回数」－「借入額」で算出することが出来るので、以下の数式を挿入します。
=F5*F6-C5
「利息の合計」は、3,158,045円と計算されました。
つまり、借入額20,000,000円以外に金融機関に利息として余分に総額3,158,045円を支払う必要があるということです。
ステップ４：１カ月目の「支払明細」を作成
次は、毎月の支払額のうち、「元金」と「利息」がいくらか、「ローン残高」がいくらかを算出します。
まずは、下の図の赤枠のセルに、１か月目(１回目）の支払の「返済額」「元金」「利息」「ローン残高」を算出します。
まず「返済額」のC12セルには、ステップ２で算出した「毎月の返済額」(F5)セルを参照します。
=$F$5
続いて「元金」のD12セルには、元金を算出することが出来るPPMT関数を挿入します。
PPMT関数の構文は「=PPMT(利率,期,期間,現在価値,[将来価値],[支払期日])」のように指定します。
=-PPMT($C$7/12,B12,$F$6,$C$5)
続いてE12セルの「利息」は、「返済額」から「元金」を引くことで算出出来ます。
=C12-D12
最後にF12セルの「ローン残高」は、「借入額」から「返済額」を引くことで算出出来ます。
=C5-D12
以上の作業により、１か月目(１回目）のローン返済額の内訳が完成しました。
ローン返済額64,328円は、元金が47,661円で利息が16,667円という内訳で、返済後のローン残高は19,952,339円ということが表から確認することが出来ます。
ステップ５：２カ月目以降の「支払明細」を完成
返済２カ月目(２回目)から最後の返済である360カ月目の「支払明細」を作成します。
１か月目のセル範囲(C12:F12)をコピーして、すぐ下のセル範囲(C13:F13)に数式を貼り付けします。
２か月目のローン残高のセル(F13)の数式だけ変更します。
=F12-D13
２か月目の支払明細のセル範囲(C13:F13)をコピーし、３か月目から最後の支払月のセル範囲(C14:F371)まで数式を貼り付けします。
そうすると、最後の支払月(360カ月目）のローン残高が「0円」となっていて、支払明細の計算が正しく処理されていることが確認出来ます。
以上の作業で毎月の支払明細の作成は完了です。
ボーナス返済のシミュレーション
毎月のローン返済以外に、年２回のボーナス時にローンを返済する場合のシミュレーションの作成方法を紹介します。
上の『ローン返済シミュレーションを作成する手順』の見出しで紹介した表をもう一つ作成し、下で紹介する赤枠のセルの赤枠の数式の数値だけ変更してください。
年２回のボーナスで4,000,000円の借入額を30年(計60回）で返済する計画を作成します。(金利1.0％）
「ボーナス月の返済額」のセル(L5)の数式を以下に変更します。(図①)(変更箇所：赤文字)
=-PMT($I$7/2,$I$6*2,$I$5)
「支払い回数」のセル(L6)の数式を以下に変更します。(図➁)(変更箇所：赤文字)
=I6*2
支払明細の「元金」列の数式を以下に変更します。(図③)(変更箇所：赤文字)
=-PPMT($I$7/2,H12,$L$6,$I$5)
下の数式は１か月目の「元金」セルの数式ですが、２か月目以降の「元金」セルの数式もコピペを使って数式を変更してください。
以上により、ボーナス返済のローン返済シミュレーションが完成します。
ローン返済シミュレーションの無料テンプレート
ローン返済シミュレーションを一から作成せずに、エクセルの無料テンプレートを利用することも出来ます。
エクセルの無料テンプレートをダウンロードする方法
エクセルのテンプレートとは、エクセルソフトにあらかじめ用意された無料のひな形のことです。
たたき台やフォーマットと思ってもらえればイメージしやすいかもしれません。
では、さっそくテンプレートをダウンロードしてみましょう。
エクセルを新規で開いた後に、多くの場合は「空白のブック」を選ばれると思いますが、その右側にも選べる項目があるのはご存じですか？
下の図の「その他のテンプレート」ボタン。これが、エクセルのテンプレートを使うための入口です。
「その他のテンプレート」をクリックすると、下の図の画面に移ります。画面下側に検索ボックスがありますので、「ローン」と入力して「Enter」をクリックします。
検索で見つかったテンプレートの一覧から、今回ご紹介する「ローン計算シート」のテンプレートをクリックします。
「作成」ボタンをクリックするとテンプレートを開くことが出来ます。
開いたら、任意の場所に名前を付けて保存しましょう。
無料テンプレートの使い方
ダウンロードした「ローン計算シート」の使い方を紹介します。
使い方は簡単です。左上の「ローンの値」(D4:D7)のセルに、「借入額」「年間利子率」「返済年数」「借入日」の４項目の条件を入力するだけです。
そうすると「毎月の返済額」「総利息」「ローン総額」などが自動で算出されます。
下の図のとおり、上の見出しで紹介した手作りのローン返済シミュレーションと同じ結果が得られます。
変動金利と固定金利の相場は？
ローン返済シミュレーションを利用する際、金利を何％にするか悩む方は多いはずです。
2023年３月現在においても、住宅ローン金利は、変動金利は1.0%を下回っており、全期間固定金利（フラット35）も非常に低い水準にあります。
以下の外部リンク記事で『住宅ローン金利（132銀行・1000商品）の金利推移・動向』について詳しく解説されているので、金利のシミュレーションに悩む方は参考にしてみてください。
https://diamond-fudosan.jp/articles/-/127188
参照先：ダイヤモンド社のサイト>住宅ローン金利（132銀行・1000商品）の金利推移
我が家の資金シミュレーションを紹介
住宅購入のためローン契約を検討している方は、ローン返済シミュレーションと一緒に、将来の資金計画表を作成してみましょう。
「将来見込める収入」や「子供の養育費や教育費」「ローン返済額」「食費代」などを年単位で計画を立てることで、家庭の貯金残高や仕事はいつまで続けるべきか,、老後にいくら必要かなど見通すことが出来ます。
以下の記事でExcelで資金シミュレーションを作る方法を紹介していますので、興味のある方は覗いてみて下さい。
