ライフプラン・キャッシュフロー表の作り方2

(1)暦年、年齢を引き伸ばす。

<連続データ>コピーの詳細なやり方は、前回の記事を参照してください。

ライフプラン・キャッシュフローをExcelで作ろう

(2)収入と支出の小計を計算する

合計や残高欄に、色を塗って見分けが付くようにしておきます。

まずは、収入と支出の小計欄に数式を入れます。

  1. D25のセル(下画像➁の位置)を選択して、オートSUMボタン(➀)を押す。
  2. 足し算をする範囲を選択します。今回は、夫婦の給与収入2つだけなので、➂の箇所を選択して、確定(enterを押す)。

ライフプラン・キャッシュフローをExcelで作ろう

(3)収支差(収入-支出)の計算

収支(➂)は、収入合計(➀D15)から支出合計(➁D23)を引き算して求めます。

D24のセルに、「=D15-D23」と記入すれば収支差が計算されます。

(下の画像のように「D15-D23」と頭に+と入れると、「=」を書かなくても計算式として処理されます。手間は変わりませんが、「+」ボタンの方が個人的に押しやすい位置にあるので「+」を使っています。よもやま話でした。)

ライフプラン・キャッシュフローをExcelで作ろう

(4)残高の計算

残高は、「前年末の残高+当年の収支差」で計算します。

1年目と2年目では計算方法が異なります。

1年目の残高

「貯蓄残高(D25)=今の残高(この表を作成した時の残高)+1年目の年間収支(D24)」

今の残高が300万円だとすると、下の画像のように+3,000,000+D24という数式になります。円単位ですと桁が多く見辛い表になるため、以降万円単位にします。

ライフプラン・キャッシュフローをExcelで作ろう

2年目の残高

2年目以降は、前年の残高+年間収支となっていきます。

D25+E24とすれば、2年目の残高になるのですが、

ライフプラン・キャッシュフローをExcelで作ろう

ここに運用利回りを反映させます。貯蓄残高の右隣の欄にある「3%」が、貯蓄を運用する時に期待する利回りです。

当然、利回りが見込まれる運用(投資)にはリスクが伴います(値下がりする可能性がある)。減るのは絶対に嫌だという人は、定期預金の利息(0.01%)を入れるか、0としてください。

計算式は、「=前年残高+利回り+年間収支」となります。利回りは、前年残高だけを運用したとみなして、年間収支はそのままの金額を残高に反映します。

ライフプラン・キャッシュフローをExcelで作ろう

Excelの計算式は、「=D25*(1+$C$25)+E24」となります。

数値を入れてみます。

D25は、前年残高で300万円です。

$C$25は、利回りで3%です。

「$C$25」:キーボードの「F4」ボタンを押すと、C25→$C$25に、他のセルにこの数式をコピーしても、参照元が固定されたままになります。

(1+$C$25)は、元本(前年末残高)+利回りです。

数値を入れると、「=300万円×(1+3%)+0」となり、1年後の結果は309万円となります。

2年後は、309万円×(1+3%)+0=318.27万円となり、

3年後以降も同じ計算をします。

(5)数式を最終年までコピー

2年後時点の数式を最終年までまとめてコピーして、今回の下準備は完了です。

小数点以下が統一感なく表示されていて見栄えが悪いので、体裁整えます。

「,」桁区切りで見た目を整える

桁区切り「,」で整える

「,」区切りを押すと、整数の場合は、3桁ごとに「,(コンマ)」がつけられ、小数点以下は非表示になります。

「,」桁区切りで見た目を整える

小数点以下を整える

少数点以下

小数点以下の桁数を増やしたり、減らしたりすることができるボタンです。

小数点以下を揃える

小数点以下を消す

上の2つは、小数点以下を非表示にする機能でした。

小数点以下を消したい場合は、次の関数を使います。

四捨五入:ROUND

切り上げ:ROUNDUP

切り捨て:ROUNDDOWN

=ROUND(数値、桁数)

という形で使います。数値の部分は、数値を入れても良いし、セルを参照しても良いです。

例えばROUND関数で、1.555という数字の小数点以下を四捨五入したいときは、=ROUND(1.555,0)とします。

すると2という結果が表示されます。

判定に使う桁数は、小数点以下の時は正数、整数にしたいときは負数で桁数を記入します。

55,555の100円未満を切り捨てたいとき

ROUNDDOWN(55,555,-2)→55,500

11.5555の小数点以下第3位を切り上げたいとき

ROUNDUP(11.5555,2)→11.56

上の数式にROUND関数を使ってみます。万円単位なので、小数点以下第5位を四捨五入して、4桁(円単位)まで表示させたいとき、数式は

=ROUND(D25*(1+$C$25)+E24,5)

キャッシュフロー表の下準備は完了です。

次回は、ライフイベント表を作成します。

戻る ライフプラン・キャッシュフロー表の作り方1

FP講座TOPへ戻る

スポンサードリンク




スポンサードリンク
1




フォローする

スポンサードリンク
1