住宅ローンの仕組みを知ろう！Excelで計算できるローンの返済額

2020年02月12日

住宅ローンに関する計算をしたい場合、インターネット上のさまざまなサイトに「住宅ローンシミュレーション」とか「ローンシミュレーター」といったページがあるので、それらを利用すれば、返済額などの計算はカンタンです。

それでも、住宅ローンの組み方や返済計画を比較するために、自分で計算できるようになりたいという人もいるでしょう。実は、エクセル（Excel）の関数を1つ知っているだけで、カンタンに計算ができ、以下のような償還予定表（返済予定表）も作ることができてしまうのです。慣れれば5分もあればできるようになるでしょう。

サンプルはこちら　⇒　元利均等返済の償還予定表サンプル

自分で償還予定表を作れるようになると、ローンの仕組みがよくわかるので、有利な組み方もわかるようになります。エクセル初心者でもカンタンにできる方法を解説していきますので、PCやスマホにエクセルが入っている方は、手順に沿ってやってみてください。

（元利均等返済の償還予定表の例）

今回は、例にあるような「元利均等返済」の返済額と償還予定表について解説します。「元金均等返済」の償還予定表もカンタンですが、別の機会に解説します。

（用語解説）

元利均等返済	毎回の返済額が一定で、期間の経過に応じて利息と元金の割合が変化していく返済方法。最も利用者の多い返済方法。固定金利型であれば、返済終了まで毎回の返済額が変わらないので返済計画は立てやすい。
元金均等返済	毎回の元金の返済を一定にし、利息を上乗せして返済していく方法。期間の経過に応じて毎回の返済額（元金と利息の合計額）が少なくなっていく方法。借入金額、借入金利、返済期間という条件が同じ場合、総返済額は元利均等返済よりも少なくなる。

ではまず、エクセルの新規ページを立ち上げたら、以下のように条件を入力できる枠と、償還予定表の項目を書いておきましょう。ローンの条件を変えた場合でもすぐに計算結果が表示できるようにするためです。

ここまでできたら、毎月返済額（上の例だとC5のセル）を計算します。
元利均等返済の返済額を計算するには、財務に関する関数である「PMT関数」を使います。初めて聞く人にとっては、「なにそれ」という感じかもしれませんが、この使い方を知っておくと、どんな条件のローンでも、元利均等返済ならカンタンに計算できるようになります。

最近のバージョンのエクセルは、昔と違ってとても親切です。関数を検索することもできますし、その関数を選ぶと、入力すべき項目を教えてくれます。わからない場合は、「関数のヘルプ」を見てみましょう。

PMT関数は、「PMT(利率,期間,現在価値,将来価値,支払期日)」というように入力すると、元利均等返済の場合の返済額がわかる関数です（「将来価値」と「支払期日」は入力しなくても大丈夫なので今回は省略します）。

例えば、借入金額1,000万円、金利2％、返済期間35年だった場合の元利均等返済（ボーナス返済なし）の毎月返済額は、次のようになります（関数の頭には必ず「＝」を付けます）。
=PMT(2％/12,35*12,10000000)
これをエクセルのセルに入力してエンターキーを押すと、「－33,126」という数値になるはずです。これが毎月返済額です。

入力項目の中の「2％/12」というのは、利率（月利）を表しています。毎月返済額を求めるためには、年利ではなく月利（＝年利÷12）を使う必要があるからです。エクセルでは「÷」は「／」で入力します（入力時はすべて半角英数）。

ちなみに、ボーナス返済を利用する場合のボーナス1回あたりの加算額を求めたい場合は、月利ではなく半年利（＝年利÷2）にします。今回の例だと「2％÷2」となります。

入力項目の「期間」は「返済回数」と同じ意味なので、35年×12ヵ月＝420回とするために「35*12」としています。エクセルでは「×」は「＊」で入力します。ボーナス返済の場合は、返済回数が年2回なので、今回の例だと「35×2」となります。

と、ここまでできれば、どんな条件のローンでも計算できますので十分でしょう。ですが、せっかくなので、償還予定表が作れるレベルまでいっちゃいましょう。

C5のセルに私が実際に入力している式は、次のようなものです。
=INT(PMT(C3/12,C4*12,-C2*10000))
PMT関数の前にINT関数を付けているのは、1円未満の端数の切り捨て処理をするためです。ROUNDDOWN関数やTRUNC関数でも同様の切り捨て処理は可能です。

計算式中のC3やC4やC2はセルの場所を示しています。このようにしておくと、C2、C3、C4の数値を変えたときに瞬時に計算結果がわかります。ちなみに、計算式中のC2の前に「－」を付けているのは、借入金額をマイナスで入力することで返済額がプラスの数値になるためです。

ここまでできたら、次は償還予定表を作っていきましょう。
1回目の返済額を示すB8のセルには「=C5」と入力し、算出された返済額をそのまま表示させます（実際に住宅ローンを組んだ場合の初回返済額は、融資実行日から初回返済日までの日数に応じて利息が計算され、2回以降の返済額と異なる場合があります）。

そして、返済額のうちの利息部分を示すC8のセルには「=INT(C2*10000*C3/12)」と入力します。住宅ローンの初回返済額に占める利息額は、借入金額に対して利息が計算されるので、端数を切り捨てにするINT関数をつけて、借入金額（C2*10000）に月利（C3/12）をかけるわけです。

元金部分を示すD8のセルには「=B8-C8」、ローン残高を示すE8のセルには「=C2*10000-D8」を入力します。初回返済額のうちの元金部分は、初回返済額から利息額を差し引いたもの。そして、初回返済後のローン残高は、借入金額から初回の元金部分を差し引いた金額となるためです。

これで1回目の返済部分は終了です。2回目の返済部分に移ります。
返済回数の表示の部分A9には「=A8+1」と入力して、2と表示させます（あとでコピーするときに楽なので）。毎月返済額の部分B9には「=B8」としておきます。

2回目の利息部分C9の部分はどうするかというと、切り捨て処理のINT関数を付けて、1回目のローン残高（E8）に月利（C3/12）をかけます。このとき、C3を絶対番地「$C$3」にしておきます。こうすると、あとでコピー＆ペーストをしたときに計算式中の$C$3の部分は絶対に変わらないようにできるからです。

2回目の元金部分D9には「=B9-C9」を入力し、2回目のローン残高E9には「=E8-D9」を入力すれば、2回目の部分は終了です。
ここまで来たら、償還予定表の完成までは、あと数秒です。

2回目の部分のセル（A9～E9）を選択してコピー（Ctrl+C）をし、そのままShiftキーを押しながら矢印キーで選択範囲を真下に広げていきます。今回の例だと427行までを選択して、Enterキーを押せば償還予定表は完成です。

また別の方法としては、2回目の部分のセル（A9～E9）を選択し、選択した部分の右下の緑の小さな四角のところにマウスで十字ポインタを持っていき、十字の形が変わったところで下方向にドラッグしていくと、同様に償還予定表が完成します。どちらの方法でもOKです。やりやすい方法でお試しください。

いかがだったでしょうか。作れましたか。償還予定表が完成した方は、是非さまざまな条件を入力して計算してみてください。やはり住宅ローンは、返済期間が短いほうが総返済額は少なくなるとか、金利が0.5％違うだけで総返済額はかなり違ってくるなど、いろいろなことに気づくことができるでしょう。そのことが、賢いローンの組み方を知ることにもつながるはずです。

サンプルはこちら　⇒　元利均等返済の償還予定表サンプル

あわせて読みたい

執筆者：菱田雅生（ひしだまさお）

ファイナンシャル・プランナー（CFP、1級FP技能士）、1級DCプランナー、住宅ローンアドバイザー

1969年東京生まれ。早稲田大学法学部卒業後、山一證券に入社し支店営業を経験。山一證券自主廃業後、独立系ＦＰに。以後20年以上にわたり、資産運用や住宅ローンを中心とした相談、執筆、講師業に従事。2000年以降の講演回数は4,000回を突破！（2020年2月現在）。近著には「お金を貯めていくときに大切なことがズバリわかる本」（すばる舎リンケージ）がある。