ローンの返済額や返済期間をExcelでシミュレーション

もうすぐ6月。いまから夏のボーナスを期待している方もいるだろう。自動車の購入など、大きな出費を予定している方もいるかもしれない。そこで今回は、ローンの計算に役立つ関数として「関数PMT」と「関数NPER」の使い方を紹介していこう。電卓では計算できない複利計算も、Excel関数を使えば手軽に計算できるようになる。

Excelで複利計算を行える関数

今回は、Excelで複利計算を行う方法を紹介していこう。Excelには複利計算を行うための関数が何種類か用意されている。その中でもよく使われるのが「関数PMT」と「関数NPER」だ。

• 

  関数PMTと関数NPERでローンをシミュレーション

これらの関数を使って、ローンの簡易シミュレーションを作成することも可能だ。その具体的な手順を紹介していこう。

毎月の返済額をシミュレーションする

まずは、関数PMTで「毎月の返済額」を計算する方法を紹介する。この計算を行うには、「金利」、「返済期間」、「借入金額」といった3つの条件を指定する必要がある。それぞれの値を手軽に変更できるように、以下のような表を作成する。

• 

  計算用の表を作成

続いて、この表に「金利」、「返済期間」、「借入金額」の数値を入力し、適切な表示形式を指定する。以下に示した図では、「金利」の表示形式に「パーセンテージ」を指定している。つまり、「金利」が2.50％と表示されている場合、実際に入力されている数値は0.025となる。

• 

  金利、返済期間、借入金額の入力（表示形式の指定）

あとは、これらの数値を参照しながら関数PMTを入力していくだけだ。関数PMTの書式は、以下のようになっている。

◆関数PMTの書式
　=PMT(利率, 期間, 現在価値, [将来価値], [支払期日])

第1引数には「利率」（金利）、第2引数には「返済期間」を指定する。続いて、第3引数に「借入金額」を指定する。

第4引数（将来価値）には「最終的な金額」を指定する。引数の指定を省略することも可能で、この場合は自動的に0（ゼロ）が指定される。ローンの場合、将来価値は0になるのが普通なので、この引数は省略しても構わない。

第5引数（支払期日）は「いつ支払いが行われるか？」を数値の0（各期の期末）または1（各期の期首）で指定する。この引数も省略することが可能だ。省略した場合は、0（各期の期末）が指定されたとみなされる。

これだけの説明では理解しにくいと思うので、具体的な例を紹介しておこう。関数PMTを使って「毎月の返済額」を計算するときは、「利率」や「返済期間」の単位を月に統一しておく必要がある。この部分が関数PMTを使用するときの重要なポイントになる。

まずは、第1引数（利率）を指定する。通常、「金利」は年X.XX％という形で示されるのが一般的だ。これを月単位にするには、「金利」を12で割った値を指定しなければならない。

• 

  関数PMTの入力（第1引数：利率）

続いて、第2引数（期間）を指定する。「返済期間」も年単位になっているケースが多い。これを月単位にするには、「返済期間（年）」に12を掛け算しておく必要がある。

• 

  関数PMTの入力（第2引数：期間）

最後に、第3引数（現在価値）を指定する。この値は、ローンの「借入金額」をそのまま指定すればよい。

• 

  関数PMTの入力（第3引数：現在価値）

ローン計算の場合は第4引数と第5引数は省略できるので、そのまま「かっこ閉じ」を入力して「Enter」キーを押す。すると、「毎月の返済額」の計算結果として以下のような数値が表示された。

• 

  関数PMTの計算結果

つまり、金利2.5％で150万円を借りて5年間で返す場合、「毎月の返済額」は2万6621円になるという計算だ。ただし、この数値はマイナスの値として表示されている。

これをプラスの数値として表示したい場合は、絶対値を返す「関数ABS」を利用するとよい。具体的には、「関数ABS」のカッコ内に「関数PMT」を記述すればよい。

• 

  関数ABSを利用する場合

もしくは、第3引数（現在価値）をマイナスの値で指定してもよい。つまり、ローンで150万円を借りる、ということは「現在の価値はマイナス150万円」と考える訳だ。

• 

  「現在価値」をマイナスで指定する場合

上記に示したいずれかの方法で関数を記述すると、「毎月の返済額」をプラスの数値で表示できるようになる。

• 

  「毎月の返済額」をプラス表示にした例

ここからは、ローンの条件を色々と変更してシミュレーションしていこう。たとえば、「返済期間」を7年に変更すると、「毎月の返済額」は1万9484円に減少する。返済期間は長くなるが、毎月の支払いを2万円以下に抑えられる、ということを把握できるだろう。

• 

  「返済期間」を変更した例

次は、もっと低金利のローンを見つけた場合の例だ。たとえば、「返済期間」を5年に戻して、「金利」を2.00％に変更すると、「毎月の返済額」は2万6292円になる、という結果が得られた。

• 

  「金利」を変更した例

金利が2.5％の場合（2万6621円）と比べると、「毎月の返済額」が329円だけ減少したことになる。150万円で5年間という条件の場合、金利が0.5％ほど低くなっても「毎月の返済額は大きく変わらない・・・」ということを把握できる。

このように、金利、返済期間、借入金額といった条件を色々と変化させながら「毎月の返済額」を計算するシミュレーターをExcelで作成することも可能だ。

端数の処理方法の違いにより1円程度の誤差が生じるケースもあるが、自動車や住宅などの大きな買い物をするときの“おおまかな目安”を調べる、簡易シミュレーターとして十分な役割を果たしてくれるだろう。

返済期間をシミュレーションする

続いては、毎月2万円のように、一定金額ずつ返済していく場合に「完済までに何ヶ月かかるか？」を調べるシミュレーターを作成してみよう。

先ほどと同様に、ローンの条件を入力する表を作成する。条件として必要な値は、「金利」、「毎月の返済額」、「借入金額」の3つだ。

• 

  計算用の表を作成

「期間」を求める複利計算を行うときは「関数NPER」を使用し、以下のような書式で関数を記述する。

◆関数NPERの書式
　=NPER(利率, 定期支払額, 現在価値, [将来価値], [支払期日])

第2引数が「期間」から「定期支払額」に変化していること以外は、「関数PMT」と同じ使い方と考えてよい。具体的な例を示していこう。

関数NPERで求める値は「返済期間（月）」であり、その単位は「月」となる。よって、第1引数（利率）には、「金利」を月単位に換算した数値を指定する必要がある。第2引数（定期支払額）には「毎月の返済額」をそのまま指定すればよい。第3引数（現在価値）には「借入金額」を指定するが、計算結果がプラスの数値で表示されるように、マイナスの数値に変換して指定する。

• 

  関数NPERの入力

「Enter」キーを押して計算を実行すると、81.636・・・という数値が表示された。つまり、金利2.5％で150万円を借りて、毎月2万円ずつ返済する場合、完済までの期間は約81.6ヶ月になる、という訳だ。

• 

  関数NPERの計算結果

約81.6ヶ月といわれても、すぐに理解しづらいので「△年△ヶ月」という形に換算しておこう。月単位で示された期間を「年」に換算するときは、12で割り算すればよい。さらに「関数INT」で小数点以下を切り捨てると、「△年」の部分を算出できる。

• 

  関数INTで返済期間の「△年」を求める

「△ヶ月」の部分は、12で割り算したときの「余り」と考えることができる。この計算は「関数MOD」で実行できる。具体的には、第1引数に「割り算される数値」、第2引数に「割り算する数値」を指定すればよい。これで「余り」の数値を求めることができる。

• 

  関数MODで返済期間の「△ヶ月」を求める

これで「返済期間（月）」を「△年△ヶ月」という形に換算できた。今回の例の場合、おおよそ「6年と9.6ヶ月」で完済できる、ということになる。もっと大雑把にみれば、「6年と10ヶ月」とも考えられるだろう。

• 

  「△年△ヶ月」で表示した返済期間

なお、「△ヶ月」を示すセルには「小数点以下の表示桁数」を1桁にする表示形式を指定してある。

これで期間を求める簡易シミュレーターは完成だ。ここからは、ローンの条件を色々と変更した例を紹介していこう。

たとえば、「毎月の返済額」を3万円に増額すると、「返済期間」を約4年5ヶ月にまで短縮できる。つまり、5年以内の完済が可能という訳だ。

• 

  「毎月の返済額」を変更した例

今度は、「毎月の返済額」を2万円に戻して、「借入金額」を120万円に減らした例を計算してみよう。この場合、「返済期間」は約5年4ヶ月になる、という結果が得られた。

• 

  「借入金額」を変更した例

今回の連載で紹介したように、「関数PMT」や「関数NPER」を使うと、ローン計算をExcelでシミュレーションできるようになる。「※※※が欲しいけれど、ローンを組むと返済はどうなるのだろう？」と思案している方は、実際に計算してみるとよいだろう。結果を知ることで、より具体的な購入計画を立てられるかもしれない。

また、「関数PMT」や「関数NPER」を資産形成の計算に活用することも可能だ。こちらはローンの逆になる計算で、積立投資をするときのシミュレーションとなる。最近、話題のFIRE（早期退職）を実現するには「毎月いくらの積立投資が必要か？」などを計算できるようになる。これについては、次回の連載で詳しく紹介していこう。