資産運用全般

資産運用に役立つ計算。~必要な貯蓄・積立額、将来の資産額、取り崩し額など~ (EXCEL FV、PV、PMT、RATE関数) 

投稿日:

ある利率で運用したら将来いくらになるの? 

目標とする金額を貯めるには毎月どれだけ積立すればよいの、

一生懸命貯めた老後資産、これからどれだけ受け取れるの?

等々、

資産運用、そして将来のライフプラン、キャッシュフローを考えるうえで必要となる計算方法について解説します。

ここでは、直接数式で計算する方法、EXCELの関数を使う方法、二つの方法を紹介します。

スポンサーリンク

見出し

一定の利率で複利運用した場合の将来の資産額

今ある資金(元本)を、ある利率で運用したら将来いくらになる? ~終価係数~

例えば、100万円を年利2.0%(1年複利)で運用したら10年後にいくらになる? といった計算です。

元本P円、年利をr%、運用年数をn年とすれば、将来の資産額Fは、

F = P x ( 1 + r ) n   (式1)

で簡単に計算できます。

下線部の係数をファイナンシャルプランナーの世界では終価係数と言います。

これぐらいなら電卓でも計算できますね。

 

EXCEL FV関数

次は、EXCEL関数を使って計算してみます。

FV関数を使います。

F = FV (r, n, 0, -P)   (式2)

元本Pにはマイナスをつけて下さい。

これだけで式1と同じ計算ができます。

 

積立貯蓄・投資した場合の将来の資産額

前章で、ある元本が将来いくらになるかの計算を解説しましたが、実際の貯蓄・運用では毎月・毎年、積立てていく場合が多いですね。

 

毎年(毎月)積立しているけど、将来いくらになるの? ~年金終価係数~

毎年10万円ずつ積立してるけど、年利2%だと10年後いくらになるの? といった計算です。

毎年の積立額をT円、年利をr%、運用年数n年とすれば、n年後の資産額Fは、

F= T x { ( 1 + r ) n - 1 } / r   (式3)  *詳しくは本記事最後の備考欄参照

下線部の事を年金終価係数と言います。

 

EXCEL FV関数

または、今回もFV関数を使って、

F= FV (r, n, -T)   (式4)

式3,4とも積立は期末に行う場合です。要は年間の積立を12月31日に行う、即ち、積立を開始した年の利息は付かないとして計算しています。

もし、期首(1月1日)に積立を行って、その年も1年分の利息が付く場合は、下記のように計算してください。

F= T x { ( 1 + r ) n - 1 } / r x (1+ r)    (式5)

F= FV (r, n, -T,  0, 1)   (式6)

 

毎月積立の場合

実際は、毎月、積立する場合が多いですよね。その時は、

式3~式6の年利を月利に、運用年数を月数に、そして毎月の積立額に変えるだけです。

例えば、(式6)の場合、

F= FV (r/12, n年 x 12 , -T/12 ,  0, 1)   (式7)

でOKです。

月利ですが、(式7)では単純に年利を12で割ってます。多くのサイトでもこのように紹介されているようです。しかし、複利での計算では、

月利 = (1 + 年利r )1/12 -1     (式8)

となりますので、注意して下さい。

 

将来の資産を用意するのに必要な元本

将来必要な資金、ある利率で運用する場合、元本いくら必要? ~現価係数~

例えば10年後に100万円必要。年利2.0%(1年複利)で運用したとして、元本がいくら必要? といった場合です。

将来必要な資金をF円、年利をr%、運用年数をn年とすれば、元本Pは、

P= F x ( 1 + r ) -n   (式9)

(式1)のn乗にマイナスが付くだけです。下線部を現価係数といいます。終価係数の逆数です。

 

EXCEL PV関数

あるいは、EXCELのPV関数を使って、

P= PV (r, n, 0, -F)   (式10)

今回もFの前にはマイナスをつけてください。

スポンサーリンク

将来の資産を用意するのに必要な積立額

前章で解説した現価係数の積立版です。

 

将来必要な資金、ある利率で運用する場合、毎年(毎月)いくら積立てれば良いの? ~減債基金係数~

何か具体的な目標、例えば住宅資金、教育資金などの決まった額が将来必要になる、その為に毎年(毎月)いくら積立しなければならないか、資産運用するにあたって最も重要な計算ですね。

例えば、10年後にマンション購入の頭金1,000万円が必要、毎月いくら積立てれば良いの? といったケースです。

将来必要な資金をS円、年利をr%、運用年数をn年とすれば、毎年の積立額Tは、

式5から、

T = S x  r / { ( 1 + r ) n - 1 }   (式11)

これで毎年(毎月)の積立額がわかります。下線部を減債基金係数と言います。年金終価係数の逆数です。

 

EXCEL PMT関数

または、EXCELのPMT関数を使います。

T = PMT (r, n, 0, -S)   (式12)

式11,12は期末に積立を行う場合ですが、期首の場合は、

T= S x  r / [{ ( 1 + r ) n - 1} x (1+ r) ]    (式13)

T = PMT (r, n, 0, -S, 1)   (式14)

となります。

毎月積立で計算する場合は、年利を月利、運用年数を運用月数に変えてください。

 

資産の取り崩し、可能な取り崩し額

貯めた資産、これから毎年いくらずつ受け取れる?  ~資本回収係数~

例えば老後資金として1000万円貯めました。これからも年利2.0%(1年複利)で運用しながら取り崩す(おろしていく)場合、毎年、いくらもらえる? 年金みたいなものですね。

現時点での資産額P円、年利をr%、これからn年かけて取り崩すとすれば、毎年受け取れる金額Wは

W = P x r x (1+r)n / {(1+r)n -1}      (式15)  *詳しくは本記事最後の備考欄参照

下線部の係数を資本回収係数と言います。

 

EXCEL PMT関数

またはEXCELのPMT関数を使うと、

W = PMT(r, n, -P, 0)       (式16)

Pの前にはマイナスをつけます。

この計算、実は、

Pが借入額、rがローン金利、nは返済年、Wが毎年の返済額と考えると、ローンの返済額の計算にもなります。

尚、上記計算 式15,16は、毎年期末におろしていった(年金を受取った)場合です。

期首におろす(受取る)場合は、

W = P x r x (1+r)n / [(1+r) x {(1+r)n -1}]      (式17)

または、

W = PMT(r, n, -P, 0, 1)       (式18)

となります。

 

一定金額(年金)を一定の期間、取り崩す(受け取る)為に必要な資産額 

ある年金額をもらうためには、いくら貯める必要があるの? ~年金現価係数~

例えば毎年100万円、10年間の私的年金が欲しい、その為には、いくら貯めておく必要があるの? 老後の資金計画に必要な計算ですね。

毎年受取る年金をW, 年利をr%、これからn年かけて取り崩すとすれば、現時点で必要な元本Pは、

P = W x {(1+r)n -1} / {r x (1+r)n }       (式19) 

下線部の係数を年金現価係数と言います。資本回収係数(式15)の逆数です。

 

EXCEL PV関数

あるいはEXCEL PV関数を使い、

P = PV(r, n, -W)           (式20)

となります。

この計算もローンの場合にも使え、毎年の返済額をW円とした場合の借入可能総額となります。

尚、式19,20とも、期末に年金を受け取る時の計算です。

期首に受取る場合には、

P = W x (1+r) x {(1+r)n -1} / {r x (1+r)n }       (式21)

P = PV(r, n, -W, 0, 1          (式22) 

となります。 

 

将来目標とする資産額、決まった元本(積立額)、必要な運用利回りは? ~EXCEL RATE関数~

あるいは、元本、積立額、将来の受取額がわかっている金融商品、この商品の利率っていくら? って場合も同じです。

ここでは、すべてEXCELのRATE関数を使います。

EXCELのヘルプによると
RATE(期間, 定期支払額, 現在価値, [将来価値], [支払期日], [推定値])
  []は省略可能

目標とする金額に増やしたい、必要な年利まわりは?

元本Pをn年後、資産額Fに増やしたい場合の必要年利回りです。(積立ではありません)

 年利r = RATE(n, 0, -P, F)   (式23) 

 

積立貯蓄・運用で目標とする金額に増やしたい、必要な年利まわりは?

今回は積立の場合です。

毎年の積立額T、n年後の目標資産額F、必要な年利回りはいくらかを求める計算です。

 年利r = RATE(n, -T, 0, F)   (式24) 

 毎月の積立の場合、*Tは年間の積立額です。

 月利 = RATE(n x 12, -T/12, 0, F)   (式25) 

 年利になおすには、
    (1) 月利 x 12  : 単利
            (2) (1+月利)12 -1  : 複利
 のどちらかで計算してください。

 

既に蓄えた資産、一定期間・金額受け取るために必要な年利まわりは?

蓄えた元本P、これを運用しながらn年間、年額W円ずつ取り崩す場合に必要な年利回りもRATE関数で計算出来ます。

年利r = RATE(n, -W, P)   (式26) 

 

最後に

以上、資産運用に必要となる計算方法について解説しました。

電卓で計算できる事もありますが、やはりEXCELが便利。

EXCELのFV関数、PV関数、PMT関数、そしてRATE関数を使うだけで、多くの計算が出来ます。

是非、皆様の資産運用に役立ててください。

スポンサーリンク

 

備考 ~数式の解説~

数学が苦手な方は読み飛ばしてください。

年金終価係数(式3)の解説 

毎年の積立額をT円、年利をr%、運用年数n年とすれば、n年後の資産額Fは、

F = T x (1+r)n-1 + T x (1+r)n-2 + T x (1+r)n-3 ・・・ T x (1+r)0
        
n
   = Σ T(1+r)k-1
      K=1
          m=1+rとして  
   =  T (mn-1 + mn-2 + mn-3 + ・・・m0)
   =  T / (1-m)  x (1-m) x (mn-1 + mn-2 + mn-3 + ・・・m0)   
   =  T / (1-m)  x (mn-1 + mn-2 + mn-3 + ・・・m- mn - mn-1 - mn-2 - ・・・- m1
   = T / (1-m) x (1 - mn )
   = T {(1+r)n-1} / r 

 という事で式3になります。

 所謂、等比級数の公式ってやつです。

 

資本回収係数(式15)の解説 

現時点での資産額P円、年利をr%、これからn年かけて取り崩すとすれば、毎年受け取れる金額Wは

1年目の残金 P x (1+r) - W

2年目の残金 {P x (1+r) - W)} x (1+r) - W = P x (1+r)2 - W x (1+r) -W

3年目の残金 P x (1+r)3 - W x (1+r)2 - W x (1+r) - W

n年目の残金 P x (1+r)n - W x (1+r)n-1 - W x (1+r)n-2 - W x (1+r)0
 
                                              n
                 = P x (1+r)n -Σ W(1+r)k-1
                                     K=1
                          前回の記事の備考で示した等比級数の公式を使って
       = P x (1+r)n + W x {1-(1+r)n)} / r
 n年目の残金が0なので、 W = P x r x (1+r)n / {(1+r)n -1}     

 式15になります。

 

スポンサーリンク

応援お願いします。
にほんブログ村 投資ブログ 資産運用へにほんブログ村 株ブログ 投資信託へにほんブログ村 その他生活ブログ 家計管理・貯蓄へ

-資産運用全般

Copyright© しんたろうのお金のはなし , 2020 All Rights Reserved.