資金計画を立てる際の６つの係数を計算するexcelシートを作成しました。計算方法についても解説します。
FPの勉強をしていると
資金計画を立てる際の６つの係数って出てきたぞ。
これって自分で計算できるのかな？
6つの係数はそれぞれエクセルの範囲で計算できます。この記事では、任意の期間、利率における６つの係数(終価係数、現価係数、年金終価係数、減債基金係数、資本回収係数、年金現価係数）の計算式とエクセルでの計算方法について解説します。
資金計画を立てる際の６つの係数と、それぞれの計算式
はじめに
資金計画を立てる際の６つの係数について以下に解説します。さらに、期間をｙ、利率をrとおき、それぞれの係数を↓の（）内の文字式として、それぞれの計算式を紹介します。調べれば手に入る数字だと思いますが、手元に置いておいていつでも計算できるようにしておくと便利です。
終価係数（ｓ）
この投資商品、何年後にどれぐらいになるんだろう？
「1万円を利率rで、y年運用した後の金額(終価)」=s(万円)
$$s=(1+r)^y$$
現価係数（g）
子供の教育費として18年後に500万必要になりそうなんだけど、
この投資商品（年率)だとどれぐらい買っておけばよいのかな？
「利率rで、y年運用した後に、1万円とするための元本(現価)」=g(万円)
$$g=1/s$$
年金終価係数（ns）
毎年40万円ぐらいなら、積立できそうだぞ。
10年続けたらいくらぐらいになるのかな？
「毎年(年金)1万円ずつ積立てて、利率rで、y年間運用した後の金額」=ns（万円）
$$ns={(1+r)^y-1}/r$$
減債基金係数（gk）
退職する25年後に2000万円ぐらいあってほしいんだけど
月々どれぐらい入れていったらいいのかな？
「毎年積立てて、利率rで、y年運用した後、1万円とするための毎年の積立額」=gk(万円)
$$gk=1/ns$$
資本回収係数（sk)
定年退職時の資産を30年で運用しつつ取り崩そう。
毎年いくらぐらい引き出せるのかしら？
「1万円を、利率rでy年運用しつつ取り崩す場合の、毎年に受け取れる金額」=sk(万円)
$$sk=\frac{1-(1+r)^{y-1}}{r}$$
年金現価係数（nk）
定年退職時の資産を30年で運用しつつ取り崩そう。毎年100万円は欲しいな。退職時にどれぐらい用意しないといけないんだろう。
「利率rでy年運用しつつ取り崩す場合、毎年に1万円受け取れるようにするための元本」=nk(万円)
$$nk=1/sk$$
以上の各項目における”1万円”の部分を自分の目標額に置き換えて、その数値に各係数をかければ目的とする金額が得られます。6つの係数はこういう使い方をします。
エクセルでの計算
上の式をエクセルに入れて計算してみよう。
Excel関数による計算
VBAによる計算(年金終価係数、資本回収係数)
最初、等比級数の和という考え方が抜けていて、年金終価係数、資本回収係数の式が出てこなかったのでVBAで計算していました。折角書いたので、参考までに以下にコードを記載します。
Option Explicit
Sub Coefficient()
Dim y As Long
Dim rate(4) As Double
Dim i As Integer
Dim j As Integer
Dim FPPC As Double
Dim CRC As Double
Dim buf As Double
Dim rate_culc As Double
y = Sheets("Coefficient").Cells(1, 3)
'********配列rate()へ利率の値を入力********
For i = 0 To 4
rate(i) = Sheets("Coefficient").Cells(2, i + 3)
Next i
'********配列rate()へ利率の値を入力(完了)********
For j = 0 To 4
'*****FPPC(Final pension price coefficient) (年金終価係数)の計算部****
FPPC = 0
rate_culc = rate(j)
For i = 0 To y - 1
FPPC = FPPC + (1 + rate_culc) ^ i
Next
Sheets("Coefficient").Cells(5, 3 + j) = Format(FPPC, "0.000000")
'*****FPPC(Final pension price coefficient) (年金終価係数)の計算部(完了)****
'*****CRC(Capital recovery coefficient)（資本回収係数）の計算****
CRC = 0
rate_culc = rate(j)
buf = 1
For i = 0 To y - 2
buf = buf / (1 + rate_culc) + 1
Next
CRC = (1 + rate_culc) / buf
Sheets("Coefficient").Cells(7, 3 + j) = Format(CRC, "0.000000")
'*****CRC(Capital recovery coefficient)の計算(完了)****
Next j
End Sub以上のようにして作成したエクセルが次の画像になります。
ダウンロード
最初に作った、VBAありのバージョンです。
後から作った、VBAなしのバージョンです。
コメントを入力