わずか5分でスキルアップ!　Excel熟達Tips(44) ローンや積立の複利計算をExcel関数で求める

今回は、ローン返済や積立預金の計算をExcel関数で行う方法を紹介する。これらは複利なので、手計算で答えを導き出すのは面倒だが、Excelに用意されている関数を使えば、簡単に答えを求めることができる。関数そのものの使い方は特に難しくないが、年/月などの単位を揃えなければならないため、少しだけ注意が必要だ。

定期支払額を計算する関数PMTの使い方

まずは複利計算の例として、住宅ローンの「毎月の返済額」を計算する方法から紹介していこう。今回は、以下のように表を作成した。

ローン計算用に作成した表

この例のように定期支払額を求めるときは、関数PMTを使用するのが一般的。その書式は以下の通りだ。

定期支払額の算出
=PMT(利率,期間,現在価値,[将来価値],[支払期日])

関数PMTには5つの引数を指定する必要がある。それぞれ指定する内容は以下の通り。

第1引数「利率」
利率を数値で指定する。たとえば、利率5%の場合は0.05を指定する。
第2引数「期間」
返済の「期間」(回数)を指定する。
第3引数「現在価値」
借入金額を指定する。
第4引数「将来価値」(省略可)
最終的な残高を指定する。ローンの場合、最終的な残高には0(ゼロ)を指定すればよい。入力を省略した場合は、0を指定したとみなされる。
第5引数「支払期日」(省略可)
支払期日を0または1で指定する。0を指定した場合は「各期の期末」、1を指定した場合は「各期の期首」として計算される。入力を省略した場合は、0(各期の期末)を指定したとみなされる。

これらの引数を指定する際に注意しなければならないのが、年や月の単位を統一すること。通常、「利率」や「期間」は年単位で示される。それに対して「毎月の返済額」は月単位の数値だ。よって、単位を「月」に統一して引数を指定していく必要がある。

順番に解説していこう。まずは関数PMTを入力し、第1引数に「利率」を指定する。利率は百分率(パーセント)で示される場合が多いため、100で割って通常の数値に換算する。さらに、年利を月単位に変更するために12で割る必要がある。今回は、C8セルに1.50(%)と金利が記入されているので、「C8/100/12」と入力する。

「利率」の指定

続いて、第2引数に「期間」を指定する。ローンの期間は、30年のように年単位で示されることが多い。これを月単位に変更するには12で乗算しなければならない。

「期間」の指定

次は、第3引数に「現在価値」を指定。こちらの単位は「円」なので、そのままセルを参照すればよい。

「現在価値」の指定

第4引数には最終的な「ローン残高」の0(ゼロ)、第5引数にも「各期の期末」を示す0(ゼロ)を指定する。この場合は引数の指定を省略できるので、そのまま関数のカッコを閉じても構わない。

関数PMTの入力完了(第4引数、第5引数の指定は省略)

「Enter」キーを押すと計算結果が表示され、「毎月の返済額」を求めることができる。

計算結果の表示

2500万円を年利1.50%、30年間で返済する場合、毎月の返済額は8万6280円だということがわかる。ちなみに、PMTなどの財務関数では、「支払う金額」をマイナスの値で示す仕組みになっているので、正の数にするには関数PMTの前に「-」(マイナス)を付けておく必要がある。

マイナス表示の修正
「毎月の返済額」を正の数で表示した場合

積立預金する場合の計算方法

関数PMTを使って、積立預金の計算を行うことも可能だ。この場合は、第3引数(現在価値)に0(ゼロ)、第4引数(将来価値)に目標金額を指定する。また、積立では支払いをした時点から利子が発生するので、第5引数(支払期日)に1(各期の期首)を指定するのが基本だ。

たとえば、年利0.80%で10年間の積立を行い、300万円を貯める場合は、以下のように関数を入力する。

毎月の積立額を計算する関数PMT

この計算結果は以下のようになり、300万円を10年間で貯めるには、毎月2万4006円の積立が必要だとわかる。

計算結果の表示

その他の財務関数

関数PMTのほかにも、Excelには数多くの財務関数が用意されている。基本的な使い方を理解しておけば色々な場面に応用できるので、この機会に試してみるとよいだろう。

積立預金をしたときに「将来、いくらになるか？」といった計算を行う場合は、関数FVを使用する。その書式は以下の通り。

将来価値の算出
=FV(利率,期間内支払回数,定期支払額,[現在価値],[支払期日])

たとえば、年利0.5%で毎月4万円ずつ6年間の積立を行う場合は、以下の例のように関数FVを入力する。第3引数の「定期支払額」は「支払う金額」なのでマイナスの数値で指定。また、第5引数(支払期日)には1(各期の期首)を指定する。すると、計算結果は292万4235となり、6年で約292万円を貯蓄できるとわかる。