目次に戻る

Excel関数で住宅ローン

というパターンを、返済期間30年、ローン額 1500万円を、元利均等払い・月払いのみで実行すること例に、

住宅ローンの３変数＝金利・返済回数・ローン額

を記述してみましょう。

　金利が２段階なので、住宅ローンの３変数も、ア 当初10年間と、イ 11年目以降 と２段階を分けて考えます。

ア　当初10年間

　住宅ローンの３変数を考えるとき、２段階金利であることにかかわらず、とりあえず返済期間を30年分と仮置きすることがミソです。金利は、当初10年間のものを使います。

金利 = 0.026 ÷ 12 = 0.00217

返済回数 = 30 × 12 = 360

ローン額 = 15,000,000

　さて、実際に考慮する期間は当初10年間に限られますから、CUMPRINC・CUMIPMT関数を使って累積の元金・利息を見る時には、10年 × 12ヶ月 = 120回分の計算を走らせます。

　すなわち、当初10年間では毎月約 6万円を返済することになります（当初10年間の支払総額は 120回 × 約 6万円 = 約 720万円）。

　ローン額1500万円のうち、元金を約 377万円だけ減らすことができますが、約343万円は利払いに消えてしまいます（約 377万円 + 約 343万円 = 約 720万円）。

イ　11年目以降

　ここまでの返済をここでいったん精算します。

　当初10年間の支払いの後、残った返済期間は20年になります。また、ローン残額は 1500万円 - 約 377万円 = 約 1123万円になります。

　これを引き継いで、改まった金利 4.0%のもと、住宅ローンの３変数を改めて組んでみます。

金利 = 0.040 ÷ 12 = 0.00333

返済回数 = 20 × 12 = 240

ローン額 = 11,228,926

　それではこの条件の下、関数を走らせましょう。

　11年目以降では、毎月の返済額が8000円ほど増えて、約 6.8万円になりました。（11年目以降の支払総額は 240回 × 約 6.8万円 = 約 1633万円）。

　ローン残額約 1123万円はすべて返済されますが、同期間で利息を約 510万円も支払っています。（約 1123万円 + 約 510万円 = 約 1633万円）。

　以上を総括して表にまとめると、こうなります。

	当初10年間	11年目以降	計
	2.6%	4.0%
毎月返済額	60,051	68,045
元金	3,771,074	11,228,926	15,000,000
利息	3,435,041	5,101,894	8,536,935
支払総額	7,206,115	16,330,820	23,536,935

　図を描くと、このようなイメージになります。

　元利均等の特性から、返済開始当初は、毎月返済額のうち利息が占める割合はかなりものがありますが、次第に、少なくなっていきます。ところが、11年目以降になると、毎月返済額が１割程度増え、うち利息が占める割合はぶり返して大きくなります。これは、金利が 2.6%から 4.0%に改定された影響です。

　固定２段階金利は、いわば、２つの金利のローンを接ぎ木したもの、といえるでしょう。

補足

　公庫サイトには、「100万円あたりのめやす表」というのがあります。これは、当初10年間の毎月返済額を、金利・全体返済期間の２つの変数で表の形にまとめたものです。

　これは、住宅ローンの３変数=金利（当初10年間のもの）・返済回数・ローン額のうち、ローン額を100万円に固定して PMT関数 を走らせてえられるものです。