PR

 前々回は合理的に資産を取り崩す計算を、前回は目標の金額を設定して積み立て、利息で生活ができるかなどを計算しました。今回は原点に戻ってコツコツと資産を積み上げるためのワークシートを作成しました。

 図1がそのシートです。白いセルの部分に数値を入力します。灰色になっているセルには数式が入っています。

図1 B~D列に毎月積み立てる金額と、年2回ボーナス時に上積みする金額と、その年の利率を入力する
図1 B~D列に毎月積み立てる金額と、年2回ボーナス時に上積みする金額と、その年の利率を入力する
[画像のクリックで拡大表示]

 前回までは金額が毎年一定でした。今回は1年ごとに金額と利率を変えられます。年2回のボーナスにも対応しました。B列の月額を12倍した額とC列のボーナス分を2倍した額の合計をD列の利率で毎年積み立てます。

 一番最初の手持ち資産はF4セルに入力します。これで最長50年間の計画が立てられます。月額などを入力した行にだけ計算結果が表示されます。そしてその結果が図2のグラフになります。月額などを入力した部分だけがグラフ化され、自動的に棒の本数が増減します。

図2 資産の推移グラフ。図1のB~D列に数値を入力した年数分のグラフが現れる。最大50年分
図2 資産の推移グラフ。図1のB~D列に数値を入力した年数分のグラフが現れる。最大50年分
[画像のクリックで拡大表示]

 今回のポイントはB1セルです。ここにはB~D列の「月額」「ボーナス分」「利率」がすべて入力されている行の数を求める計算式が入っています。これでグラフ化する範囲などを決めています。

配列数式で判定する

 B1セルの数式を図に示しています。この数式を入力した後[Ctrl]キーと[Shift]キーを押しながら[Enter]キーを押して確定してください。これで配列数式と呼ばれる形式になって、複数のデータを一気に計算できます。

 ISNUMBER関数はセルが数値かどうかを判定します。B列の「月額」、C列の「ボーナス分」、D列の「利率」のセルをそれぞれ判定してそれらを掛け算しています。ある年について3つとも数値が入っていたら「1」となり、1つでもブランクだったり数値ではない値が入っている場合は「0」になります。これを50年分合計すると何年分のデータが入力されているかが分かります。

 ただし、1年目のデータから途中の年を空けることなく順次入力してください。途中に空きがあったり一部しか入力されていない部分があると正しい期間は求められません。

 図2のグラフは基本的に前々回のグラフと同様の手法で作成しています。Excelの「名前」機能を活用しています。少しだけ説明しておきましょう。

 図1のF4セルに「年初の資産先頭」、B1セルには「期間」という名前を付けてあります。そして、「年初の資産のグラフ範囲」という名前には「=OFFSET(年初の資産先頭,0,0,期間)」という数式を入力してあります。名前を付ける操作で通常セルやセル範囲を指定する部分に数式を書いておく、というのがミソです。これでグラフ化するデータ範囲が期間に応じて変化します。

 グラフを作成するときはグラフ系列の値に「='20091214morimoto-ke.xls'!年初の資産のグラフ範囲」と指定します。ここで「20091214morimoto-ke.xls」はこのシートのファイル名です。

サンプルファイルの公開は終了しました。