2012年05月17日

エクセルで年金保険の利回りを一発で計算する方法

「40歳から20年間、毎月3万8000円ずつ払い続けると、60歳時点で1000万円の一時金を受け取れる」という金融商品があるとします。この商品の利回りをエクセルで計算するには、RATE関数を使うと一発で計算できます。

=RATE(20*12,-38000,0,10000000)*12

答えは年0.91%です。養老保険や学資保険など貯蓄性のある保険はこの方法で実質的な利回りを計算できます。詳しくはこちらのページを(http://moneylab.ldblog.jp/archives/cat_50051805.html)。

年金保険の利回りはIRR関数で求める

今回考えたいのは年金保険の利回りです。たとえば、ソニー生命保険の「個人年金保険」を例に考えてみます(http://www.sonylife.co.jp/examine/lineup/list/pension/)。この商品は次のような内容です。

40歳男性が20年間、毎月4万4640円ずつ払い続けると、60歳からの10年間、毎年120万円の年金を受け取れる。

image

この利回りをRATE関数で計算するのは難しい。60歳まで積み立てながら一定利率で運用を行います。そこで積み上がった原資を10年にわたって受け取るのですが、その間も運用は続くのです。60歳時点の原資を単純に10等分して受け取るわけではありません。

こうした出入りがある金融商品の利回りを計算するときはIRR関数を使います。下の画面写真を見てください。セルに毎年(もしくは毎月)のお金の出入りを並べて入力していきます。支払うべき保険料(自分から出て行くお金)はマイナスで、受け取る年金(自分に入ってくるお金)はプラスで入力するところがポイントです。

「-535,680」というのは一年間に支払う保険料ですね(40~59歳)。「1,200,000」は一年間に受け取る年金の額です(60~69歳)。列Aの年齢は分かりやすいように入力しただけで、IRR関数の計算に必要なわけではありません。

利回りを求めるには、IRR関数の引数として、お金の出入り(毎年の収支)を入力した範囲を指定します。下の例では、セルB2からB31に収支を入力しているので、次のように指定します。

=IRR(B2:B31)

image

これだけで利回りが求まります。期間や現在価値、将来価値などの引数は必要ありません。年単位でお金の出入りを指定したときは年利ですが、月単位だと月利になるので12倍して年利に直してください。もしあり得ない数字(10%とかマイナスとか)が出てきたら、「=IRR(B2:B31,1%)」と式を修正します。IRR関数では複数の答えが存在することがあり、「,1%」と追加することで「1%近辺の答えを求める」という意味になります。

IRR関数で先ほどの個人年金保険の利回りを計算すると0.754%になります。メガバンクの定期預金が年0.1%を切っている現状からすればそこそこの利回りに見えますが、30年も固定されることを考えるとちょっと微妙な数字ですね。

計算結果をFV関数とPMT関数で検証

念のために0.754%という数字が正しいかどうかを他の関数で確かめてみましょう。まずFV関数を使い、年0.754%で20年間、年53万5680円(月4万4640円)ずつ積み立てたときの将来価値を計算します。

=FV(0.754%,20,-535680)

この結果から、60歳時点では年金原資が1151万6783円になっていることが分かります。次にPMT関数を使って、「利率は0.754%、期間は10年、現在価値は1151万6783円」の条件で60歳以降の毎年の受取額を計算します。

=PMT(0.754%,10,-11516873)

答えは119万9986円となります。ちょっと誤差が出ていますが、商品の設計通りに毎年120万円を受け取れます。確かに計算が合っていますね。



トラックバックURL

コメントする

このブログにコメントするにはログインが必要です。