Excelを使って将来の資産残高の推定値を簡単にシミュレーションする方法

近い将来に自動車を購入したい！買い替えたい！
だとか、10年後に住宅を購入したい！と考えたときに、こんな風に思うことがあります。

将来の資産残高の推移・推定値を知りたい！

自動車や住宅は高額であり、買いたいと思ったときに、すぐに買えるほどのお金を手元に持っているとはかぎりません。

特に住宅については、手元資金だけで買えるという方は非常に稀であり、多くの方が頭金がたまった時点で住宅ローンを組んで購入。
その後購入した住居に住みながら、ローンを返済していく方がほとんどでしょう。

そして自動車や住宅の購入資金・頭金を貯蓄していく中で気になるのが、将来の資産残高の推移や推定値です。

今現在の貯金のペースで、期日(購入予定日)までに目標金額を貯金できるのか。
毎月仮に5万円ずつ貯金をし続けたら、10年後に資産残高はいくらになっているのか。
といった内容は、購入資金や頭金を貯蓄している最中には、特に気になることでしょう。

この、将来の資産残高の推移や推定値は、Excelを使えば簡単にシミュレーションすることが可能です。
そこで今回は、Excelを使って将来の資産残高の推定値を簡単にシミュレーションする方法をご紹介します！

自動車や住宅の購入を予定していて、将来の資産残高の推移や推定値が気になる方は、ぜひ参考になさってください！

Excelを使って、毎月定額の貯金(資産の増額)をした場合の資産残高の推移・推定値一覧表を作る方法

まずは毎月定額の貯金(資産の増額)をした場合の資産残高の推移・推定値一覧表を作る方法から、ご紹介しましょう！

Excelを起動したら、以下画像の例を参考に、表を作成してください。

この例では、2018年1月を現在の月としてシミュレーションを開始し、そのときの資産残高の合計を350万円としています。
開始月の資産残高の合計には、預貯金などの資産の合計額を記入してください。

表の体裁は上記例と異なっていても、もちろん構いません。

必要な要素は、年月と資産残高、数十万円以上の日常的には発生しない大きな支出を記入する箇所。
そして毎月定額の増額分を記入・設定する箇所です。

また備考欄があると、大きな支出についての説明書きなどをメモ書きできるので、併せて用意しておくことをおすすめします。

表の用意ができたら、資産残高の計算式を設定しましょう。

毎月定額の貯金(資産の増額)をした場合の資産残高の推移・推定値を計算するため、C6(シミュレーションの2か月目の資産残高)セルには『=C5+$C$2-D5』という計算式を入力してください。

この計算式では、当月の資産残高 = 前月の資産残高 + 毎月の増額金額 – 前月の大きな支出という計算を行っています。

たとえば前月の資産残高が350万円であり、毎月の増額(貯金など)が3万円。
前月に10万円の冷蔵庫を購入した場合には、その月の資産残高は343万円(= 350 + 3 – 10)となります。

毎月の増額セルの指定に$マークを付与しているのは、セルの絶対参照を行うことで、翌月以降の計算が正しく行われるようにするためです。

C6セルに計算式の入力が終わったら、C19セルまでオートフィルを行い、以下のように計算式をコピーしてください。

次に毎月の増額金額をC2セルに入力します。

下記例では毎月預貯金などの現金資産が3万円増えることとして、30000と入力しています。

毎月の増額金額の入力を終えるとただちに計算が行われ、1年後の2019年1月では資産残高が386万円となることが一目瞭然。

毎月3万円増額した場合、年間では36万円資産が増えることになります。
2018年1月の資産残高は350万円で、これに36万円を足すと386万円です。

これはExcelの一覧表で計算した結果と一致しており、Excelによるシミュレーションでも正しく計算されていることが分かります。

シミュレーション期間中に高額家電や自動車の購入・買い替えなど、数十万円以上の日常的には発生しない大きな支出が予想される場合には、これを加味することで、より正確な資産残高の推移・推定値を算出可能です。

仮に2018年6月に250万円の自動車を購入する予定の場合には、以下のように『大きな支出』列に支出金額を書いておくことで計算に加味されます。
また『備考』列にその理由を書いておけば、後で支出理由を把握しやすいでしょう。

大きな支出に特記する金額をいくら以上の支出とするかは、お好みで決めていただいて構いません。

このシミュレーション(資産残高の推移・推定値一覧表)の目的は、将来的な資産残高の推移・推定値を知ることです。
したがって、将来的な資産残高の推移・推定値に大きな影響を与える可能性のある日常的には発生しない大きな支出について、記入をおすすめします。

一般的には、10万円を超えるような支出は将来の推移・推定値に大きな影響を与える支出だと考えられるため、10万円以上の支出について、記入しておくと良いでしょう。

自動車購入の他には、たとえば10万円を超えるような高機能型炊飯器やドラム型洗濯乾燥機の購入。
PCの買い替え、子供の大学の入学金などが、大きな支出の例として挙げられます。

Excelを使って、毎月の資産増額(貯金など)を入力する形式の資産残高の推移・推定値一覧表を作る方法

ここまでにご紹介したExcelを使って、毎月定額の貯金(資産の増額)をした場合の資産残高の推移・推定値一覧表を作る方法では、毎月一定の金額の貯金(資産の増額)をした場合のシミュレーションを行っています。

これは計算・入力が簡単な反面、正確性は高くはありません。
より正確な推移・推定値を知りたい場合には、毎月の資産増額(貯金など)を入力する形式の推移・推定値一覧表への記入をおすすめします。

毎月の資産増額(貯金など)を入力する形式の推移・推定値一覧表では、まずは以下のような表をExcelで用意してください。

毎月定額の貯金(資産の増額)をする場合の表と比べ、毎月定額の増額分を記入・設定する箇所をなくしています。
その代わりに、毎月の増額分を記入する列を追加しています。

この例でも、2018年1月を現在の月としてシミュレーションを開始し、そのときの資産残高の合計は350万円としています。

表の作成が終わったら、C4(シミュレーションの2か月目の資産残高)セルには『=C3+D3-E3』という計算式を入力してください。

この計算式により、当月の資産残高 = 前月の資産残高 + 前月の毎月の増額金額 – 前月の大きな支出という計算を行っています。

C4セルに計算式の入力が終わったら、C17セルまでオートフィルを行い、C4セルの数式をC5～C17セルに複写しましょう。

最後に、毎月の増額と大きな支出、備考欄の記入を行います。

仮に毎月の増額を3万円とし、2018年3月に年に1回の医療保険の支払いがあり、2万円の赤字が発生。
2018年6月に250万円の自動車を購入し、翌月(2018年7月)から自動車保険の支払いを始め、毎月の増額が5,000円減る(3万円 → 2.5万円に減額した)と仮定した場合、以下のようなシミュレーション結果となります。

長期的な推移・推定値一覧表やグラフも作ってみて！

今回ご紹介した推移・推定値一覧表では、説明スペースの関係上、2018年1月～2019年3月までの15か月間しか作成していません。
ですがこの表を使えば表の計算式を下方にコピーするだけで、3年や5年、10年、20年、30年といった中・長期的な資産残高の推移・推定値を知ることができます。

20代のころに、40～50年分程度の資産残高の推移・推定値一覧表を作成。
そして想定される資産の毎月の増額や大きな支出類を記入するだけで、老後の資産残高の推定値を知ることができるので、今後の人生設計・資金計画の参考となるでしょう。

また年月列と資産残高列を選択した状態で折れ線グラフを作成すれば、以下のようなグラフが簡単に作成でき、資産残高の推移を視覚的に見ることも可能です。

資産残高の推移・推定値が気になる方は、ぜひ今回ご紹介した方法で一覧表を作ってみてくださーい！