IRR関数を使わない 個人年金保険の利回り計算

以前の記事「個人年金保険の利回り計算について」に関して、個人年金保険の利回り計算の方法についてお問い合わせを受けました。

簡単なのはエクセルのIRR関数を使う方法です。

しかし、キャッシュフローが長期にわたるとIRR関数では計算できないようです。

今回は、IRR関数を使わない個人年金保険の利回り計算について


個人年金保険の利回り


利回り計算には「内部収益率」(IRR)を用います。

そもそも内部収益率とは・・・との説明は割愛します。ともかく、個人年金保険の利回り計算には内部収益率(IRR)を使い、エクセルではIRR関数がそれに対応しています。

ただ、IRR関数はキャッシュフローが多くなると計算できないようです。

なので、まずIRR関数を使ったやり方を説明して、そのあとでIRR関数を使わないやり方を説明します。


個人年金保険の例


シンプルな例にします。

1年目:10,000円 拠出
2年目:10,000円 拠出
3年目:据え置き
4年目:据え置き
5年目:12,000円 受け取り
6年目:12,000円 受け取り

という個人年金保険(らしきもの)を想定します。拠出と受け取りのタイミングは期末とします。

このときの利回りは?


IRR関数を使った計算


irr_01.png

拠出額をマイナス値、据え置きはゼロ、受け取りはプラスの値でキャッシュフローをエクセルの表に入れます。

そして、IRR関数を使って計算。

irr_02.png

結果は4.66%です。


IRR関数を使わない計算


IRR関数を使わない場合、「ソルバー」を使います。

ソルバーを使う前に、準備段階としての計算方法を説明します。

irr_05.png

まず、将来のキャッシュフローを現在価値に割り引きます。その計算は、セルC5:C10です。D列にあるのはC列のセルの計算式です。

C5には、「=B5/(1+$C$2)^A5」の式が入ります。C6以降も同様です。

セルC12は、セルC5:C10の合計です。

IRR関数で計算された4.66%をセルC2に置くと、C12はゼロになります。正確に言うと、「C12がゼロになるように、C2のIRR数値が決まる」という関係です。

さて、IRR関数が使えないとき、C2の数字が分からないわけです。

どうやって探り当てるか。


IRR関数を使わないで


irr_06.png

IRR関数を使わない場合、C2の利回りが分からないわけです。

なので、適当な数値を入れてみました。

そうするとC12はゼロになりません。

困りました。

C12がゼロになるように、C2の数字を手あたり次第に変えてみて、それでC12がゼロになったら「ビンゴ!」ですね。

ただ、手動でやるのは大変です。

そこでソルバーの出番です。


ソルバーを使った計算


エクセルの「データ」→「ソルバー」でソルバーを呼び出し、パラメーターを設定します。

irr_07.png

C12を、指定値のゼロとなるように、C2を変更していく、という意味です。

IRRがマイナスになる可能性があるので、「制約のない変数を非負数にする」のチェックは外しておいたほうがいいです。

なお、解決方法の選択は気にせず、デフォルトのままで大丈夫です。


ソルバーの結果


ソルバーで説いた結果はこうなります。

irr_08.png

ピッタリゼロではなく、微妙に-0.0000xxとかになりますが、実用上は問題ないです。

キャッシュフローが多数あるとか不定期に発生するなど、IRR関数で対応しきれないものにはソルバーが威力を発揮します。


月次と年次


年金保険のように月次払い、月次受け取りの場合はどうしたらいいか。

さきほどと同じように、キャッシュフローをエクセル上に再現して、IRRを求めれば月率のIRRが求まります。

そうして求めた利回りを年率換算すればいいのです。

月率0.6%と出たら、「(1+0.006)^12-1」で年率換算できます。

計算すると7.44%です。

今回はマニアックな内容でした。最後までお読みいただきありがとうございます。 (^^)

ブログ村:よろしければ一押しをお願いします。
にほんブログ村 株ブログ 投資信託へ にほんブログ村 ライフスタイルブログ セミリタイア生活へ
 

管理者にだけ表示を許可する