資産構築・運用・管理

金利の計算方法|住宅ローンの返済表をエクセルで作ってみた

3,000万円の住宅ローンを返済期間35年・金利1%で借りた場合、月々の返済額はどうやって計算するんだろう?

今回は、金利の計算方法についてのモヤモヤをクリアにしていきたいと思います。

そもそも金利って何?という場合はこちらで復習をどうぞ。

家を購入するのに住宅ローンを組んだ際、銀行から返済表を「紙」でもらいませんでしたか?

でも「紙」の返済表は数字の羅列でしかなく、よく意味もわからないのでもはやどこにあるかわからない・・・という状態ではないでしょうか。

今回はエクセルを用いて、返済表を作成したいと思います。

金利の計算方法|住宅ローンの返済額を求める

毎月の返済額を計算式で算出する方法はこちらです。。。。

これは高校の時にやった等比数列の和の公式を用いたものなのですが・・・どうでしょう?

頭が痛くなってきた・・・
小難しい数式、大っ嫌い!

って思いますよね~(^_^;)

なので、こういうアカデミックなことは置いておいて、ここではエクセルという便利なツールを使って算出する方法を説明していきます。

目的はあくまでも、毎月の返済額済額がいくらか算出し、返済表を作成することですからね。

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か月目の支払利息

支払利息の計算方法は、元本×月利です。

月利というのは、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か月目の支払利息・支払元本・残元本を計算する

1回目の返済をした結果、借入残高は変化しましたね。

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か月目以降は、

  • 直近の残元本×月利で支払利息を算出
  • 返済月額-支払利息で支払元本を算出
  • 直近の残元本-支払元本で元本返済後の残元本を算出

を繰り返していきます。

そうすると最終的に、借入金額は全て返済し残元本はゼロになります。

※返済最終月の返済額は、直前月の残元本と最終月の支払利息の合計となり、必ずしもこれまで毎月支払ってきた返済額とは一致しません。

ローン返済表のエクセルシート(無料ダウンロード付)

私の方で住宅ローンの返済表のエクセルシート(スプレッドシート)を作成してみました。

全体的なイメージはこんな感じです。

使い方はとても簡単です。

  1. 黄色の網掛け部分に入力する!
  2. 「返済表作成」ボタンをポチっとする!

たったこれだけです。

「返済表作成」ボタンを押せば、ローンの返済表を自動的に作成してくれます。

「クリア」ボタンを押せば、ローンの返済表のデータはパパっと消えます。

いろいろな値を入れてシミュレーションしてみてくださいね。

また、返済総額と支払利息総額も表示しておきました。

さらに、2つのグラフを表示しておきました。

1つ目は、借入残高の推移グラフです。

  • 横軸は、返済の月数
  • 縦軸は、借入残高(万円)

を示しています。

2つ目は、返済額の推移グラフです。

  • 横軸は、返済の月数
  • 縦軸は、返済額(万円)

を示しています。

返済額は、支払利息と支払元本で構成されます。

返済開始当初は、利息部分が多かったのが、返済が進むにつれその部分が少なくなり、元本部分が大きくなっていくのを確かめていただければと。

ダウンロードはこちらから無料でできます!

※パソコンでご使用ください。

なお、これはエクセルVBAを使って作っています。

もしセキュリティ警告が出てきたら、「マクロを有効化」とか「コンテンツを有効化」のボタンを使ってお使いくださいね。

え!?怪しい?(笑)

別にお願いしている訳ではないので、無理は言いません。

お使いいただければ役に立つかなと思ってアップしてるだけなので。

お使いいただいた方は、感想などをいただけると嬉しいです!

また「なかなかいいじゃん!」って思ってくれた方は、「いいね」ボタンもお願いしますm(_ _)m

ローンの返済方法には、今回ご紹介した元利均等返済の他に「元本均等返済」というのもあります。

こちらをご覧いただければ更に理解が深まりますよ。

 

関連記事

  1. 不動産の共有名義を解消する7つの方法|相続トラブルを回避するために

  2. 戸建ての固定資産税はいくら?新築と中古で比較したときの金額は?

  3. 変動金利と固定金利|住宅ローンはどっちを選ぶ?リスクの違いを比較してみ…

  4. 月3万円使えるお金を増やす(稼ぐ)には?預金の必要元本を試算した結果・…

  5. 100万円貯金の利息計算!年利0.4%の3ヶ月定期に入れたら満期にいく…

  6. 複利運用(積立)の計算方法は?年金終価係数の求め方をわかりやすく解説!…

  7. サラリーマンも確定申告を!オーナー会社の節税手法を知って唸った話

  8. 不動産仲介手数料の相場は?計算方法や値引き交渉の方法・言い方

コメント

  1. この記事へのコメントはありません。

  1. この記事へのトラックバックはありません。

最近の記事

  1. 自己紹介

    会社員不適合の社内ニートが不動産事業を立ち上げる…
  2. 自己紹介

    母が投資詐欺の沼にハマっていた!セミナーに乗り込…
  3. 資産構築・運用・管理

    老後資金の準備方法を「アリとキリギリス」の3つの…
  4. 資産構築・運用・管理

    高金利通貨の新興国債券に手を出した男の末路から学…
  5. 資産構築・運用・管理

    外貨投資の損益の計算方法は?為替の基礎知識をシミ…
  6. 資産構築・運用・管理

    分散投資で資産を分散する投資対象・種類には何があ…
  7. 資産構築・運用・管理

    資産運用における分散投資の考え方を初心者向けにわ…
  8. 資産構築・運用・管理

    老後資金を一瞬で657万円溶かした話|資産を失っ…
  9. 資産構築・運用・管理

    金融商品のリスクとは?種類の一覧と意味をわかりや…
  10. マインドセット

    自分へのご褒美は意味不明?資産形成に必要な決断・…
  11. 資産構築・運用・管理

    投資と投機の違い・区別は?図解・シミュレーション…
  12. 資産構築・運用・管理

    投機とギャンブルの違いは?事例・図解で意味をわか…