資産運用の算数 (2) 積み立て投資の利回り計算

積み立て預金をExcelで計算してみる

前回の記事(id:casualstartup:20110306)では、積立預金の受取額計算を、等比数列の和を使って手計算していましたが、表計算ソフトで計算させることもできます。

ここではExcel 2010を使って計算する方法を紹介しますが、他の表計算ソフトでも同様の機能はあるので応用可能です。

まずは、前回の積立預金の問題を再掲します。

【問】年利6%で1ヶ月複利の定期預金に、毎月5万円積み立てることにしました。5年間積立を続けた時点で、元利合わせていくらになるでしょうか(積立は今月から開始し、60回積み立てると解釈してください)。金利は5年間変わらないものとし、税金は考えないものとします。

まずは、最初の月(0ヶ月目とします)に積み立てた5万円がどうなるか考えてみましょう。5年後ということは、60ヵ月後です。したがって、積立終了時までに、60回利息がつくことになります。したがって、最初の月の預金の5年間積立終了時の元利合計金額は以下の式になります。

$\Large 5 \times (1+\frac{0.06}{12})^{60}$

さて、これをExcelで表現してみます。シートの作り方は色々とありますが、まずは定数を配置します。

次に、「満期までの月数」と「満期時の元利合計」のそれぞれに数式を入力します。図中の矢印は、セルの参照関係を表しています。なお、セル参照に"$"マークがついているのはExcelの「絶対参照」という機能です。数式を他のセルにコピーしたときに、このマークがついている参照はセルの位置によらず常に一定になります。

画像が見にくい方のために、C2とD2のセルの数式をテキストでも書いておくと、以下の通りです。

C2セル: =$G$3-A2
D2セル: =B2*(1+$G$1/$G$2)^C2

あとは、A列からD列の2行目を、コピーして下に貼り付けて、A列の「月」の欄を0から順に1ずつ増やして、59まで作成します。最後に、D列の一番下の次の行に、以下の数式を入れれば完成です。

D62セル: =SUM(D2:D61)

途中を省略していますが、以下の画像のようになり、最終的な合計金額は、3,505,944円であることが分かります。

このシートがあれば、ボーナス時に積立金額を割り増している場合や、昇給したときに積立金額を増やしたときなども、対応する積立額欄を変更するだけで、簡単に計算できます。また、金利が変わったときは、金利の数字を変えるだけで勝手に再計算してくれるので便利です。

預金以外にも適用することができる

ここまでの例では定期預金を想定しましたが、積み立て式の投資信託などでも、同じシートを使うことができます。また例題です。

【問】ある積み立て式の投資信託を、毎月5万円積み立てることにしました。この投資信託が、仮に60ヵ月後まで実質年利6%で運用されたとしたら、元利合わせていくらになるでしょうか。

ちょっと言葉は変えていますが、さきほどの例と、まったく同じExcelシートで計算して同じ結果になるはずです。

利回りを逆算してみる

さて、投資信託の情報を、たとえばモーニングスターなどで調べると、1年の騰落率が出ています。この騰落率を、作成したExcelシートの「年利」の欄に入力してみます。そして、現時点までの積み立て額を順に入力して、最終的な合計金額を出します(1)。

一方で、現時点の保有口数と、現在の基準価額から、自分の保有する投資信託の評価額を計算してみます(2)。

この(1)と(2)の金額は大抵、まったく一致しません。それは、モーニングスターなどに出ている1年騰落率は、その投資信託全体の騰落率であり、自分が購入した金額を反映したものではないからです。

では、自分にとっての利回りはどの程度になるでしょうか? Excelシートの「年利」欄を適当に変えてみて、一番下の合計金額が、さきほどの評価額(2)と一致すれば、それが利回りです。

この「適当に変えてみる」という作業をExcelに自動でやらせることができます。それが「ゴールシーク」という機能です。合計金額欄を選択して「ゴールシーク」を起動すると、「どのセルを変えることによって」「いくらに近づけるか」の2つを入力する欄が出てきます。ここで年利のセルと、評価額を指定すると、自動で「利回り」を求めることができます。