NPER関数を使って目標貯蓄額は何歳で達成できるかを計算する

質問

30歳から毎月5万円づつ積み立てると、目標である3,000万円は何歳で達成できるのでしょうか? (平均運用利回りは2%とする)

 

イメージ図

 

どうやって積立額をアップさせるかは別の問題として、それでは計算してみましょう。

平均運用利率は2%とします。

利率が一定であると仮定した場合の、目標貯蓄に達するための投資に必要な期間を求めるにはエクセルのNPER関数を使います。

エクセルのセルに、次のように入力して下さい。

= NPER ( 0.02/12 , -50000 ,0, 30000000 ,  1 )

それぞれの項目の意味は次のとおりです。

=NPER(利率, 定期支払額, 現在価値,将来価値, 支払期日)
 

すると、答えは

416

となります。

これは、もちろん416年ではなくて、416ヶ月を意味します。

なぜなら、NPER関数に入力する値で、利率を「0.02/12」と月利で入力し、定期支払額を「ー50,000」と月額で入力しているからです。

よって、年利では 416÷12=34.6 年

となり, 30歳から34.6年後の64.6歳でようやく目標額に達成します。

 

回答

30歳から毎月5万円づつ積立てた場合、目標貯蓄額3,000万円に到達するのは34.6年後の64.6歳となります

 

NPER関数の各項目の意味は次のとおりです

NPER(利率, 定期支払額, 現在価値,将来価値, 支払期日)

【利率】
定期支払額が月額 (5万円)で設定していますので、利率も月利で設定します

【定期支払額】
毎月の積立額を指定します
期間中の積立額の変更はできません
積立額(支払額)はマイナスで入力します

【現在価値】
30歳の現在貯金がゼロなので「0」としました。
もし、貯蓄があるならば、ここにその額を入力して下さい。

たとえば今100万円を持っているならば、「-1000000」と入力して下さい。
(マイナスをつけることを忘れずに)

【将来価値】
目標貯蓄額は30,000,000円ですので、30000000と入力します。

【支払期日】
毎月、積立てをいつ行うかを指定します。
月初に積み立てる場合は1を設定します。

1 各期の期首に積み立てる場合
0 各期の期末に積み立てる場合

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます

C3からC7に、次のように該当する数値を入力して、C9で各セルを参照した式を入力しても計算できます

各セルを変更してシミュレーションしてみて下さい。

 

NPER関数を使って何歳で貯金はゼロになるかを計算する

質問

65歳時点で3,000万円の貯蓄があります。毎月15万円づつ生活費として取り崩したとしたら、何歳で貯金はゼロになるでしょうか?(平均運用利率は2%とします)

 

イメージ図

 

 

では、計算してみましょう。

平均運用利率は2%とします。

利率が一定であると仮定した場合の、元利均等ローンの返済期間を求めるにはエクセルのNPER関数を使います。

エクセルのセルに、次のように入力して下さい。

= NPER ( 0.02/12 , -150000 ,30000000 , 0 , 1 )

それぞれの項目の意味は次のとおりです。

=NPER(利率, 定期支払額, 現在価値,将来価値, 支払期日)
 

すると、答えは

243

となります。

これは、243 年ではなくて、243 ヶ月を意味します。

なぜなら、NPER関数に入力する値で、利率を「0.02/12」と月利で入力し、定期支払額を「150,000」と月額で入力しているからです。

よって、年に直すと 243 ÷12=20.25 年

となります。

65歳+20.25年=85.2 歳まで取崩しができます。

回答

65歳で3,000万円の貯金があり、以後毎月15万円づつ生活費として取り崩したら、85.2歳で貯金はゼロとなる

 

NPER関数の各項目の意味は次のとおりです。

NPER(利率, 定期支払額, 現在価値,将来価値, 支払期日)

【利率】
定期支払額が月額 (15万円)で設定していますので、利率も月利で設定します。

【定期支払額】
毎回の取崩し額を指定します。
期間中の支払額の変更はできません。
支払額はマイナスで入力します。

【現在価値】
65歳時点での貯蓄額は30,000,000円ですので、30000000と入力します。

【将来価値】
貯金がゼロになるまで計算するので、将来価値は0とします。

【支払期日】
毎月、取崩しをいつ行うかを指定します。
月初に貯金を取り崩すので、1を設定します。

1 各期の期首に支払いの場合
0 各期の期末に支払いの場合

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます

C3からC7に、次のように該当する数値を入力して、C9で各セルを参照した式を入力しても計算できます

各セルの数値を変更してシミュレーションしてみて下さい。

 

RATE関数を使ってどれくらいの利率で運用すれば目標貯蓄額に達するのかを計算する

質問

現在30歳ですが、今から毎月5万円づつ積立てて、60歳で3,000万円を貯蓄目標としています。運用利回りとして年利何パーセントで運用したら目標に到達できるでしょうか?

 

イメージ図

 

 

将来の目標貯蓄まで、何%で運用したらよいかを計算するには、エクセル関数のRATE関数を使用します。

エクセルのセルに、次のように入力して下さい。

   = RATE ( 30*12, -50000, 0 , 30000000 , 1) 

それぞれの項目は次の通りです。

    =RATE(期間, 定期支払額, 現在価値, 将来価値, 支払期日, 推定値)
 

すると答えは

0.26%となります.

しかし、ここでは毎月積み立てることとして算出しているので、答えは月利になっています。

これを ×12 として年利に直すと

3.15%となります。

 

回答

30歳から、毎月5万円づつ30年間積み立て、60歳で3,000万円貯蓄するには 3.15%の平均運用率が必要である

 

それでは、それぞれの項目について説明していきます。

 

RATE関数の使い方

定期的に積立貯蓄の払込を行うときの利率を求めるにはRATE関数を使います。

=RATE(期間, 定期支払額, 現在価値, 将来価値, 支払期日, 推定値)

 

【期間】
求めたい利率と[期間]の単位は同じにします。
たとえば、月利を求めるのであれば、[期間]や[定期支払額]の値も月単位で指定します。

【定期支払額】
毎月積立額5万円はマイナスで入力します。

【現在価値】
30歳現在の貯蓄額はゼロのため、ゼロを入力します。

もし、貯蓄があるならば、ここにその額を入力して下さい。
たとえば今100万円を持っているならば、「-1000000」と入力して下さい。
(マイナスをつけることを忘れずに)

【将来価値】
60歳(将来)時点での貯蓄目標額を設定しますので、30,000,000円と入力します。

【支払期日】
積立額の払い込みが期首に行われるか期末に行われるかを指定します。
期首に払い込むので1を入力します。

1 期首に払い込む場合
0 期末に払い込む場合

【推定値】
省略可
利率の推定値を指定します。
推定値を省略すると10%が指定されたものとみなされます。

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます

C3からC7に、次のように該当する数値を入力して、C9で各セルを参照した式を入力しても計算できます

各セルの数値を変えてシミュレーションしてみて下さい。

 

PV関数を使って希望の生活費を取り崩すにはいくら貯蓄すればよいのかを計算する

質問

65歳から90歳まで毎月20万円づつ生活費として取り崩すには、65歳時点でいくらあればよいのか?
貯蓄残金は2%で運用していくものとする。

 

 

イメージ図

 

 

65歳から毎月20万円づつ取り崩して行きますが、残っている貯金は運用をしていきます。
つまり、運用しながら取り崩していきます。
その運用は何%で運用できるのかを考える必要がありますが、これは、年利2%としておきます。

年金資産のためいくらあればよいかを計算するには、エクセル関数のPV関数を使用します。

エクセルのセルに、次のように入力して下さい。
=PV ( 0.02/12,25*12, -200000, 0 ,1 )

それぞれの意味は次の通りです
=PV(利率, 期間, 定期支払額, 将来価値, 支払期日)

すると答えは
47,264,665
となります

 

回答

65歳から90歳まで、毎月20万円づつ取り崩すには、65歳で47,264,665円必要です。

 

それでは、それぞれの項目について説明していきます。

=PV(利率, 期間, 定期支払額, 将来価値, 支払期日)

 

【利率】
運用利率を指定します(元本は運用しながら取り崩すため)
生活費の取崩しが月単位のため、利率(2%)も月利で指定し、0.02/12と入力します。

【期間】 
取崩し期間全体の支払回数の合計を指定します。
月単位なので、25*12と入力します。
(300か月なので300と入力してもOKです)

【定期支払額】 
毎月取り崩す金額をマイナスで入力します。
例では毎月20万円づつ取り崩すので、ー200000と入力します。

【将来価値】 
将来価値、つまり最後の取崩しを行った後に残る現金の収支を指定します。
最後の取崩しは90歳を設定しており、残る金額はゼロと指定しています。

(実際は、90歳以後も生活費を残さなければならない場合や遺産などの必要性もある場合には、その金額をマイナスで入力して下さい)
将来価値を省略すると、0 (ゼロ) を指定したと見なされます。

【支払期日】 
最後に、「支払期日」ですが、
取崩しを月初に行うのであれば「1」を入力します。
取崩しを月末に行うのであれば「0」を入力します。

(支払期日を省略すると、0 を指定したと見なされます)

 

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます

C3からC7に、次のように該当する数値を入力して、C9で各セルを参照した式を入力しても計算できます

各セルの数値を変えてシミュレーションしてみて下さい。

 

PMT関数を使って毎月いくらづつ取り崩せるかを計算する

質問

65歳で3,000万円貯蓄したとします。
その後90歳まで、生活費として毎月いくらづつ取り崩せるでしょうか?
貯蓄残金は2%で運用するものとします。

 

 

イメージ図

 

では計算してみましょう。

・・・・・・

いや、ちょっと待って下さい。

年金額(取崩額)を算出するには、いつまで生きるのかを考えなければなりません。

それは計画通りにはいきませんが、とりあえず90歳まで生きるものとして計算してみましょう。

相続も残す金額も考えないものととして。

 

65歳で貯蓄した30百万円は、毎月取り崩して行きますが、残っている貯金は運用をしていきます。

つまり、運用しながら取り崩していくのですね。

だから、その運用は何%で運用できるのかを考える必要があります。
これを2%としておきましょう。

 

年金としていくらずつ取り崩せるか(年金取崩計算)はエクセル関数のPMT関数を使用します。

エクセルのセルに、次のように入力して下さい。

=PMT(0.02/12, 25*12,  -30000000, 0, 1)

それぞれの意味は次の通りです。
=PMT(利率,期間,現在価値,将来価値,支払期日)

すると答えは

126,945

となります。

 

回答

65歳で3,000万円貯蓄したら、その後90歳まで毎月126,945円づつ取り崩せる
(ただし貯蓄残金は2%で運用するものとする。)

 

それでは、それぞれの項目について説明していきます。

=PMT(利率,期間,現在価値,将来価値,支払期日)

 

【利率】
貯蓄残金の運用利率を指定します。

生活費の取崩しが月単位のため、利率(2%)も月利で指定し、0.02/12と入力します。

期間
  取崩し期間全体の取崩回数を指定します。
月単位なので、25*12と入力します。
(300カ月なので300と入力してもOKです)

現在価値
  65歳時点の貯蓄額(取り崩す金額の元金)-30000000と入力します。
(元金は年金のために投資する金額なので、出ていく資金とみなしマイナス表示します。)

将来価値
  将来価値、つまり最後の取崩しを行った後に残る現金の収支を指定します。
最後の取崩しは90歳を設定しており、残る金額はゼロと指定しています。

(実際は、90歳以後も生活費を残さなければならない場合や遺産などの必要性
もある場合には、ここにその金額を入力して下さい)

  将来価値を省略すると、0 (ゼロ) を指定したと見なされます。

支払期日
  支払いがいつ行われるかを、数値の 0 または 1 で指定します。
生活費の取崩しは、月初に行うため1を入力します。

0  各期の期末
1  各期の期首

支払期日を省略すると、0 を指定したと見なされます。

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます

C3からC7に、次のように該当する数値を入力して、C9で各セルを参照した式を入力しても計算できます。

各セルの数値を変えて、シミュレーションしてみて下さい。

 

PMT関数を使って目標貯蓄額のため今からいくらづつ積み立てればよいかを計算する

質問

現在30歳であり、定年後の生活のため60歳で3,000万円の貯蓄を目標としています。今から毎月いくらづつ貯蓄したらよいでしょうか?(平均運用利回りは3%とします)

 

イメージ図

将来の目標額(将来価値)のために、いくらづつ積み立てればよいかは
エクセル関数のPMT関数を使用します

エクセルのセルに、次のように入力して下さい
=PMT( 0.03/12, 30*12, 0, 30000000, 1 )

それぞれの意味は次の通りです
=PMT(利率,期間,現在価値,将来価値,支払期日)

すると答えは
-51,353
となります(マイナスがついているのは、支払うからです)

 

回答

60歳で3,000万円貯蓄するには、今(30歳)から毎月51,353円づつ積み立てる必要がある

 

PMT関数の項目については次のようになります。
=PMT(利率,期間,現在価値,将来価値,支払期日)

「利率」
投資の運用利率を指定します。

積立額が月単位のため、利率も月利で指定し、0.03/12と入力します。

「期間」
積立期間全体での積立回数の合計を指定します。
月単位なので、30*12と入力します。
(360と入力してもOKです)

「現在価値」
「現在価値」ですが、30歳の現在貯金がゼロなので「0」としました。
もし、貯蓄があるならば、ここにその額を入力して下さい。

たとえば今100万円を持っているならば、「-1000000」と入力して下さい。
(マイナスをつけることを忘れずに)

「将来価値」
  将来価値、つまり60歳時点での貯蓄目標額を指定します。

30,000,000円必要ですので、この金額を入力します。

「支払期日」
積立てがいつ行われるかを、数値の 0 または 1 で指定します。

積立ては月初に行われるため1を入力します。

0(省略)各期の期末
1    各期の期首

支払期日を省略すると、0 を指定したと見なされます。

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます。

C3からC7に、次のように該当する数値を入力して、C9で各セルを参照した式を入力しても計算できます。

各セルの数値を変えてシミュレーションしてみて下さい。

 




 

FV関数を使って積立てによる将来の貯蓄額を計算する

質問

現在30歳ですが、今から毎月2万円づつ年利3%で運用しながら貯蓄したら、60歳のときいくらになっているのでしょうか?

 

 

イメージ図

 

 

エクセル関数を使って計算してみましょう。

積立てによって将来いくらになるかを計算するときは、エクセル関数の「FV関数」を使用します。

どこかのセルに、次のように入力して下さい。
= FV  ( 0.03/12 , 30 * 12, -20000 , 0 , 1)

それぞれの意味は次の通りです。
=FV(利率, 期間, 定期支払額, 現在価値, 支払期日)

ると、答えは
11,683,875 
となります。

 

回答

今(現在30歳)から、毎月2万円づつ貯蓄していくと、60歳で11,683,875円の貯蓄ができます。 (平均運用利回り3%として)

 

それでは、FV関数に入力する項目の意味を説明していきます。

関数項目の意味

=FV(利率, 期間, 定期支払額, 現在価値, 支払期日)

利率
この例では、運用平均年利3%でとしています。(生涯の運用の平均利回りが3%)
年間1回の投資であれば、「0.03」と入力すればよいのですが、投資する金額が月単位ですので、利率も月にあわせる必要があり、「0.03/12」と月利で入力します。

期間
「期間」の意味ですが、これも月単位なのか年単位なのか、期間を合わせなくてはなりません。

毎月の投資であるため、ここも月数で「30*12」と入力する必要があります。
(360ヶ月ですので、360と入力してもOKです)

定期支払額
「定期支払額」は、毎月2万円づつ積み立てるので「ー20000」と入力します。(出金するときは、マイナスをつけます。)

現在価値
「現在価値」ですが、30歳の現在貯金がゼロなので「0」としました。
もし、貯蓄があるならば、ここにその額を入力して下さい。
たとえば今100万円を持っているならば、「-1000000」と入力して下さい。(ここも出金なのでマイナスをつけます)

支払期日
最後に、「支払期日」ですが、
積立てを月初に行うのであれば「1」を入力します。
積立を月末に行うのであれば「0」を入力します。

 

セル参照による入力

数式に直接数字を入力しましたが、セルの番号を選択(参照)することもできます。

C3からC7に、次のように該当する数値を入力して、C9で各セルを参照した式を入力しても計算できます。

各セルの数値を変えて、シミュレーションしてみて下さい。

エクセルFV関数利用の注意点