エクセル(Excel)で資本回収係数

退職後の資金について一定の利回りで運用しつつ、そこから一定の資金を引き出し、予定年数に到達したら残高がゼロとなるような定期取り崩し額を知りたい場合に資本回収係数が使われる。

当初の手元資金Pを利回りiで複利運用(年1回)しつつ毎年、定額のMを引き出しn年に達すると残高がゼロとなるような場合には

M=P×k となる資本回収係数 kが下記のような算式で求められる。

資本回収係数 k= i・(1+i)ⁿ/((1+i)ⁿ-1) 

以下に示すように、エクセルで計算式を記入して求めてもよいし、エクセルの財務関数PMTを使って求めることもできる。年数と利回りによる係数表にしておくと使い勝手が良いので、その一例を作ってみた。

 

資本回収係数

 

この表の計算式の一部を表示すれば下記のようになる。

 

資本回収係数

例えば セルC11には =C$10*(1+C$10)^$B11/((1+C$10)^$B11-1)
セルC22には =PMT(C$21,$B22,-1,0)  などと記入して
行と列の絶対参照を使い分ける。

使い方の一例としては以下のような状況が考えられる。

Aさんが定年になり退職金を1000万円受け取ったとする。今後20年間にわたって年利2%でこの資金を運用しつつ、しかも毎年一定額を生活費として取り崩し、20年後にはすべて資金を使い果たす計画を持っているとする。その場合にはAさんは毎年いくらを取り崩せるだろか。その時に登場するのが資本回収係数で利回り2%で年数が20年の係数を表から探すと、k=0.06116が見つかる。この係数を当初資金1000万円に乗じると61万円と計算できる。つまり年に61万円を引き出せるという計算になる。

ただし注意しなければいけないことは、残高そのものが時価により変動しないことが前提となる。株式投信などで運悪く評価損が生じれば資金計画通りにはゆかなくなる恐れがある。評価損を避けたければ定期預金や国債等の満期保有で運用せざるを得ないが、その場合には1%の利回りどころか限りなくゼロ%に近い運用しかできない。そこで何らかの分散投資をするなどの工夫が必要になるが、これは誰も事前に最適解を求めることができない難しい問題である。結局、あれこれ考えて悩んでいても仕方ないので、たとえば上、中、下の3つシナリオを用意して、上のシナリオは週一回すき焼き、中のシナリオは月見うどん、下のシナリオは特売の湯豆腐としておき、流行り言葉で言えば、事態の推移を注視しつつ自らの支出(消費)行動をシナリオにあわせてスイッチしていくことになるだろう。予め資本回収係数を使って計算しておいた資金計画残高と比較しつつ、幸にも時価運用残高が増えているようであれば、すき焼きを食し、残念ながら時価運用残高が計画を下回っていれば特売の湯豆腐の生活に切り替えるような対応しか出来ないだろう。

 

余談として資本回収係数の導出の考え方について少しまとめてみる。

P=初期投資額 M=定額の引き出し額、i=利回り として、話を単純にするため3年間の運用と仮定する。

時点           投資残高

0                 P

1              P(1+i)-M

2           (P(1+i)-M)(1+i)-M

3           ((P(1+i)-M)(1+i)-M)(1+i)-M

3年末での残高がゼロとなるようなMを求めることになる。

(1+i)=r と置き換えると

((Pr)-M)r-M)r-M=0  これを展開してゆくと

Pr³-Mr²-Mr-M =0  Mについて解くと

M=P×r³/(1+r+r²)  ここで分母の(1+r+r²)は等比数列の和になるので等比数列の和の公式を使って(1-r³)/(1-r) で表せる。

従って、M/P=r³(1-r)/(1+r³)   r=1+i を代入して整理すれば

M/P=-i・(1+i)³/(1-(1+i)³) =i・(1+i)³/((1+i)³-1)

任意の年数nについて求めるなら類推で上式の3をnに置き換えれば

資本回収係数 i・(1+i)ⁿ/((1+i)ⁿ-1)  を得る。

 

財務入門目次へ