確定拠出年金相談ねっと認定FP 加藤 博

2016年 11月 18日

Excel(エクセル)で個人年金保険の年利回りを計算する方法

こんにちは。
確定拠出年金 相談ねっとわーく 認定ファイナンシャル・プランナーの加藤博です。

portal_icon

 

個人年金保険の商品研究をするときに、【年利回り】をExcelの関数を使って計算しました。

 

参照コラム

「個人年金保険(たのしみワンダフル)の年利回りを計算する」

「個人年金保険(年金ひとすじ)の年利回りを計算する」

 

「どうやって算出するのですか?」

とお問い合わせを頂きましたので、計算方法をお伝えします。

 

Excelのシートに次のように入力します。

excel%e5%85%a5%e5%8a%9b%e8%a1%a8

列Bに日付、列Cにはお金の流れを入力します。

 

モデルケース

 

①2016年10月31日 から5年間 10万円を支払います

②2020年11月1日から5年間 11万円を受け取ります

 

積立期間、受取期間中の年利回りを計算してみます。

 

このような計算はExcelの関数を使用すれば、簡単に算出できます。

 

XIRR関数を使います。IRR関数との違いは「日付」を指定していることです。

お金の流れの日付が分かっているときは、XIRR関数の方が良いと思って使っています。

 

XIRR関数の結果  2.408%

 

計算式   ‘=XIRR(C2:C11,B2:B11)’                    

日付と支払額と受取額から年利回りを計算しています                     

C2:C11 → お金の流れです

B2:B11→ 日付です

 

結果があっているか?

検算してみましょう。

電卓カタカタ

①2020年10月31日時点の積立元本+運用益を計算します

ExcelのFV関数で検算してみます。

 

FV関数の結果 524,667

 

計算式   “=FV(2.408%,5年,-100000円,0)”      

              FV(利率,期間,金額,現在価値)

 

              利率はXIRR関数の結果     2.408%

              期間       5年なので5

              金額       100,000  支払なので-で入力

              現在価値         頭金などないので0円

 

 

2020年10月31日時点での年金現価が計算できました

→ 524,667円

 

②2020年11月1日から毎年110,000円を5年間で受け取ります

(年金現価を5年間で分割受け取りするので、分割金額を計算します)

 

ExcelのPMT関数で検算してみます。

PMT関数の結果   -109,986円 

14円 誤差があります。だいたいあっています。

 

計算式 ”=PMT(2.408,%,5年,524667円,0円,期首払)”                               

              PMT(利率、期間、現在価値、将来価値、期首払)

 

利率はXIRR関数の結果     2.408%

              期間            5年なので5

              現在価値               年金現価の524,667円

              将来価値                  全額取り崩して受け取るから0円

              期首払                      期首に受け取る

 

少し、誤差がありますが、おおよそ計算結果とお金の流れがあっています。

ご参考になればうれしいです。

ばんざーい

 

 

 

 

Tags:

コメントは受け付けていません。