今回は、金利の計算方法についてのモヤモヤをクリアにしていきたいと思います。
家を購入するのに住宅ローンを組んだ際、銀行から返済表を「紙」でもらいませんでしたか？
でも「紙」の返済表は数字の羅列でしかなく、よく意味もわからないのでもはやどこにあるかわからない・・・という状態ではないでしょうか。
今回はエクセルを用いて、返済表を作成したいと思います。
金利の計算方法｜住宅ローンの返済額を求める
毎月の返済額を計算式で算出する方法はこちらです。。。。
これは高校の時にやった等比数列の和の公式を用いたものなのですが・・・どうでしょう？
頭が痛くなってきた・・・
小難しい数式、大っ嫌い！
って思いますよね～(^_^;)
なので、こういうアカデミックなことは置いておいて、ここではエクセルという便利なツールを使って算出する方法を説明していきます。
目的はあくまでも、毎月の返済額済額がいくらか算出し、返済表を作成することですからね。
PMT関数でローンの返済額を簡単に計算する
エクセルのPMT関数は、利率が一定であると仮定した場合の、元利均等の毎月の支払額を計算できる関数です。
計算式は以下の通りです。
=PMT（利率,期間,現在価値,将来価値,支払期日）
利率
「利率」はパーセントで入力します。
ここで重要なのが、「利率」と「期間」の単位を同じにするということです。
月払であれば、利率は月利（年率/12か月）で指定します。
年率1％の場合、利率は「1%/12か月」とします。
「利率」が年率であれば、期間は年数にします。
「利率」が月利であれば、期間は月数にします。
期間
「利率」に合わせて、期間の単位を変更します。
利率が月利であれば、期間も年数ではなく月数（年率×12か月）で指定します。
35年ローンの場合、期間は420か月（35年×12か月）とします。
現在価値
借入金額を入力します。
将来価値
借入金を完済するのでここは0となります。
ここを省略しても、エクセルの方で自動的0と判断してくれます。
支払期日
支払いがいつ行われるかを指定します。
期首払い=「1」
期末払い=「0」
ここを省略すると、エクセルの方で自動的に0（期末払い）と判断してくれます。
以上から、諸浦価値が0、期末払いを前提とすると、
返済額の算出に必要な情報は、
- 利率
- 期間
- 借入金額（現在価値）
の3つだけということになります。
この3つさえ分かって、関数に放り込めば返済額が算出できますので、便利で簡単ですよね。
なお、PMT関数では、手元に入る金額はプラスで示され、手元から出ていく金額（返済額）はマイナスで示されます。
ローンの返済表を作成する際、返済額がマイナスの表記だと、私の場合ちょっと違和感があるので、私が作成する場合は、PMT関数の前にマイナスをつけます。
こうすることで、PMT関数で算出されたマイナスと後から自分でつけたマイナスを掛けることになり、返済額はプラスの表記になります。
＝PMT(1%/12,35*12,30,000,000)
＝－84,685円・・・（マイナス表記）
↓
＝－PMT(1%/12,35*12,30,000,000)
＝84,685円・・・（プラス表記）
この返済額には、利息の部分と元本部分があるというとを忘れないでくださいね。
そしたら次は、エクセルで返済表を作成していきます。
1か月目の支払利息・支払元本・残元本を計算する
借入をしてから、どのように返済額（利息や元本の内訳）が動いていくのか、借入残高はどのように減っていくのかを確認してみましょう。
１ヶ月目はこちら。
1か月目の支払利息
支払利息の計算方法は、元本×月利です。
月利というのは、1か月あたりの利子です。
金利は、通常「年率（年利）」で表記されますから、これを月利に換算する必要があります。
年率を月利に換算するには、年率を12で割れば算出できます。
計算式で書くとこうなります。
月利＝年率÷12
割り算1回だから、そんなに難しくないですよね。
では実際に返済1か月目の利息を計算しますね。
元本3,000万円に対して、金利（年率）は1％ですから、
3,000万円×1％／12＝25,000円
1か月目の支払元本
返済額は、利息部分と元本相当分で構成されているという話をしました。
これを式で表すと、返済額＝利息＋元本です。
ココから、元本を計算するには、返済額から利息を差し引けば算出されますね。
つまり、支払元本＝返済額－支払利息です。
返済額は、最初にエクセルのpmt関数により、84,685円と計算できました。
次に、利息を25,000円と計算しました。
なので、返済額の内、元本部分は59,685円（84,685円－25,000円）で計算できます。
1か月目の残元本
初回返済後の残高は、借入額3,000万円から初回に返済する元金を引いた額となります。
3,000万円－5万9,685円＝2,994万315円
となります。
2か月目の支払利息・支払元本・残元本を計算する
１回目の返済をした結果、借入残高は変化しましたね。
２か月目はどのように、推移していくのかを具体的に確認していきます。
2か月目の支払利息
2か月目の支払利息の計算方法は、1回目のときと同様に、元本×月利で計算します。
注意するのは、残元本が変わっている（減っている）ということです。
1回目の返済を終えた時点で、残元本は2,994万315円になっているのでしたね。
なので、2か月目の支払利息は、以下のように計算されます。
2,994万315円×1％／12＝24,950円（端数切捨て）
2回目の支払元本
2回目の返済元本の計算方法は、1回目のときと同様に、返済額－（2回目）支払利息です。
返済額は、最初にエクセルのpmt関数により、84,685円と計算していました。
次に、2回目の支払利息を24,950円と計算しました。
なので、返済額の内、支払元本は59,735円（84,685円－24,950円）で計算できます。
2回目の残元本
2回目返済後の残高は、1回目返済後の残元本から2回目に返済する支払元金を引いた額となります。
2,994万315円－5万9,735円＝2,934万580円
となります。
3か月目以降の支払利息・支払元本・残元本を計算する
3か月目以降は、
- 直近の残元本×月利で支払利息を算出
- 返済月額－支払利息で支払元本を算出
- 直近の残元本－支払元本で元本返済後の残元本を算出
を繰り返していきます。
そうすると最終的に、借入金額は全て返済し残元本はゼロになります。
※返済最終月の返済額は、直前月の残元本と最終月の支払利息の合計となり、必ずしもこれまで毎月支払ってきた返済額とは一致しません。
ローン返済表のエクセルシート（無料ダウンロード付）
私の方で住宅ローンの返済表のエクセルシート（スプレッドシート）を作成してみました。
全体的なイメージはこんな感じです。
使い方はとても簡単です。
- 黄色の網掛け部分に入力する！
- 「返済表作成」ボタンをポチっとする！
たったこれだけです。
「返済表作成」ボタンを押せば、ローンの返済表を自動的に作成してくれます。
「クリア」ボタンを押せば、ローンの返済表のデータはパパっと消えます。
いろいろな値を入れてシミュレーションしてみてくださいね。
また、返済総額と支払利息総額も表示しておきました。
さらに、2つのグラフを表示しておきました。
１つ目は、借入残高の推移グラフです。
- 横軸は、返済の月数
- 縦軸は、借入残高（万円）
を示しています。
２つ目は、返済額の推移グラフです。
- 横軸は、返済の月数
- 縦軸は、返済額（万円）
を示しています。
返済額は、支払利息と支払元本で構成されます。
返済開始当初は、利息部分が多かったのが、返済が進むにつれその部分が少なくなり、元本部分が大きくなっていくのを確かめていただければと。
※パソコンでご使用ください。
なお、これはエクセルVBAを使って作っています。
もしセキュリティ警告が出てきたら、「マクロを有効化」とか「コンテンツを有効化」のボタンを使ってお使いくださいね。
え！？怪しい？（笑）
別にお願いしている訳ではないので、無理は言いません。
お使いいただければ役に立つかなと思ってアップしてるだけなので。
お使いいただいた方は、感想などをいただけると嬉しいです！
また「なかなかいいじゃん！」って思ってくれた方は、「いいね」ボタンもお願いしますm(_ _)m
ローンの返済方法には、今回ご紹介した元利均等返済の他に「元本均等返済」というのもあります。
こちらをご覧いただければ更に理解が深まりますよ。
この記事へのコメントはありません。