個人年金保険などの年利回りを計算する方法

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


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

 

参照コラム

 

「住友生命の個人年金保険(たのしみワンダフル)を研究する」

「明治安田生命の個人年金保険(年金ひとすじ)を研究する」

 

「どうやって算出するのですか?」とお問い合わせを頂きましたので、計算方法をお伝えします。

 


 

モデルケース

①2016年10月31日 から5年に渡って 10万円を支払います

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

 

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

 


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

 


 

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

 

このような計算は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円

              期首払                               期首に受け取る

 

 

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

 

 

家計の見直しのご相談を承っております

家計の見直し・住宅ローン借り換えのご相談はコチラのフォームでご連絡ください。

ご相談希望の方は、こちらのフォームから問い合わせください。

家計の見直しお問い合わせフォーム

 

個人型確定拠出年金(iDeCo イデコ)の相談受け付け中です

個人型も企業型のどちらもご相談承っております

ご相談希望の方は、こちらのフォームから問い合わせください。

iDeCoお問い合わせフォーム

 

マイベストプロでコラム発信中