エクセルの財務関数 - 年利から適用利率を求める方法

　こんにちは。

　以前、エクセルの財務関数についてまとめました。財務関数は、金利が付く周期の利率と回数を用いて計算します。

　ですが、定期預金やローンなどは、期間にかかわらず、利率は年利で示されることが多いようです。例えば、3ヶ月ものや6ヶ月ものの定期預金でも適用金利は年利で示されています。

　今回は、複利が付く期間（周期）が半年や四半期(3ヶ月)、1ヶ月、日割りなどの場合に、年利から適用利率を求める方法についてまとめます。

利率を求める2つの方法

① 年利を1年間の支払回数で割る

　マイクロソフトのOfficeサポートページにも記されている方法で、年利を複利が付く期間の回数で割ります。例えば、年利 12% の 4 年ローンを月払いで返済する場合、利率には

  12% ÷ 12(ヶ月) = 1%

を、支払回数には

  12(ヶ月) × 4(年) ＝ 48

を指定します。

② 1年間の支払回数から複利の利率を求める

　エクセルのRRI関数を使います。RRI関数は、引数に、期間と元金、満期の額を指定します。例えば、年利12% の 4年ローンを月払いで返済する場合を考えます。100円を年利12%で運用すると、12ヶ月で100円が112円になるので、月払いの利率は

　　RRI(12,100,112) ＝ 0.949%

を指定します。支払回数は

  12(ヶ月) × 4(年) ＝ 48

を指定します。

2つの方法の使い分け

　まとまったお金を一括で投資する場合は、RRI関数を使えば正確に計算できます。例えば、100万円を年利6%相当で運用できるとする場合、年利6%なら12ヶ月で100円が106円になるので、1ヶ月の利率として

　　RRI(12,100,106) ＝ 0.487%

を指定します。すると、

　　FV(0.487%,12,0,-1000000,1) ＝ 1060031

と、1年(12ヶ月)でほぼ106万円になります。誤差があるのは、利率を0.487%に丸めたためで、より正確に0.48675506にすればピッタリ106万円になります。今回の例で、年利を複利が付く期間の回数で割る方法で利率を求めて

　　6% ÷ 12(ヶ月) ＝ 0.5%

を指定すると、

　　FV(0.5%,12,0,-1000000,1) ＝ 1061678

と、誤差が大きくなります。

　ローンを毎月返済する場合は、どちらの方法を使っても正確に計算することは難しいと思います。毎月返済していく中、どの時点で金利をつけるか、によるためです。

具体的な例

　年利を複利が付く期間の回数で割る方法と、RRI関数を使う方法の差を、具体例で見てみます。

年利6%、元金500円、期首に毎月200円つみたて、10ヶ月運用

年利12%、元金0円、期末に毎月1000円つみたて、12ヶ月運用

年利11%、元金0円、期首に毎月2000円つみたて、35ヶ月運用

年利6%、元金1000円、期首に毎月1000円つみたて、12ヶ月運用

まとめ

　定期預金やローン返済の場合は、銀行が金利の付き方を詳細に決めているため、財務関数を使ったシミュレーションは参考にするだけで、最終的には銀行の規約を確認するしかありません。

　一方、投資信託などにつみたて投資を行う場合は、金利の付き方にルールはなく、長期的に見て結果的にこれくらいの平均利率を期待できると考えるので、却って、財務関数によるシミュレーションが使いやすくなります。投資のアセットアロケーションを考える際は、エクセルの財務関数を活用しようと考えています。