11/17/2016

Excel。財務関数シリーズ。目標金額までの期間を求めるNPER関数


Excel。財務関数シリーズ。目標金額までの期間を求めるNPER関数

<NPER関数とROUNDUP関数とQUOTIENT関数とMOD関数>


今回取り上げる、財務関数シリーズは、目標金額までの期間を算出することが出来る、

NPER関数

NPERは、Number of PERiods で、ナンバー・オブ・ピリオドだそうです。

今回使う表は次のような表です。

年利は、0%ということはないのですが、今回もヘソクリということで算出してみようと思います。
年利を0%にすることによって、
算出される数値が合っている・いないがわかりやすくなりますので、最初の段階では、
年利は0%にすることをお勧めしております。

月額積立額ですが、今回は、頑張って、20,000円を積立てていくことにします。

そして、ゴールの積立目標額は、1,000,000円としました。

このような設定の場合、積立期間はどのくらいになるのか?を求めることが出来るのが、
今回ご紹介する【NPER関数】です。

さて、算出する前に財務関数の特徴として、
自分の手元から出ていく金額をマイナスにしておかないと、
最終的に算出される値もマイナスになってしまい、ピンときませんので、

B3の月額積立額を-20,000にしておきましょう。

では、B6をクリックして、NPER関数ダイアログボックスを表示しましょう。

利率には、B2/12 今回は年利ですので、月で算出する必要がありますので、
12で除算しておく必要があります。これは、PV関数やFV関数でもおなじみですね。

定期支払額は、毎月の支払額なので、B3ですね。

現在価値は、0。
この現在価値は、現時点で一括払いした時などの金額があれば入力します。

将来価値は、B4。目標金額ですね。

支払期日は、0。
支払いをいつ行うのか?ということを設定できます。0は各期の期末という意味になります。
また、1を入力しますと、各期の期首になります。

それでは、OKボタンをクリックしてみましょう。

B6には、50と算出されましたね。
1,000,000を20,000で除算すれば、当然50ですから合致していますね。
年利を0%にして算出したわけです。

B6の数式は、

=NPER(B2/12,B3,0,B4,0)

なのですが、年利を仮に0.1%にしてみましょう。

この数式のままでは、B6の積立期間が小数点表示になってしまいます。

これは、現実的にはおかしいので、繰り上げる必要が生じますので、
ROUNDUP関数を使う必要があります。

よってB6の数式を次のように修正する必要があります。

=ROUNDUP(NPER(B2/12,B3,0,B4,0),0)

このようにすれば、繰り上げりますので、現実的な期間になります。

また、このB6の数値は、「月単位」になっておりますので、年月にする場合には、
それぞれ、算出してあげる必要も生じます。

例えば、B7に年をB8に月を算出してみると、次のようになります。

B7には、除算して余りを除いた数を算出することが出来る。
【QUOTIENT関数】を使います。
数式は、

=QUOTIENT(B6,12)

最後にB8には、余りを求めますので、【MOD関数】を使います。
数式は、

=MOD(B6,12)

このように設定してあげることによって、年・月も算出することが出来るようになります。