• BPnet
  • ビジネス
  • IT
  • テクノロジー
  • 医療
  • 建設・不動産
  • TRENDY
  • WOMAN
  • ショッピング
  • 転職
  • ナショジオ
  • 日経電子版
  • PR

  • PR

  • PR

  • PR

  • PR

森本家のワークシート

資産を取り崩すなら

森本 篤徳=日経パソコン 2010/03/24 日経パソコン
出典:日経パソコン 2009年11月9日号
(記事は執筆時の情報に基づいており、現在では異なる場合があります)
目次一覧

 大金を手にすると人格が変わってしまうことがあります。カジノで得た大金を使い果たしてついには破産、などという報道を目にすることもあります。せっかく得られたお金で不幸になるのは悲しいことです。退職金、遺産、万馬券、宝くじの当せん金など、大金に目がくらまないようにExcelで合理的な取り崩し方を研究しておきましょう。

 図1は今ある資産を一定金額ずつ取り崩す場合に使える金額を計算したものです。投資などで運用して利息を得ているものとします。現在の資産額、何年にわたって取り崩すのか、最終的に残しておきたい資産額、それと運用利率を表の1~2行目の白いセルに入力します。4行目で1年間に使える金額とそれを12で割った月額が求められます。

図1 今ある資産を運用しながら一定金額ずつ取り崩す場合、年間いくら使えるのかを表の上部で計算した。毎年の年初と年末の資産額、およびその年に得られる利息の一覧も表示した
[画像のクリックで拡大表示]

 使うのはPMT関数です。住宅ローンでよく用いられる元利均等払いの計算に使う関数ですがこの場合も使えます。

 図の数値は4000万円を20年で1000万円まで取り崩す例です。3000万円を20年間で使うわけですから、運用利率が0なら単純な計算で年間150万円になります。運用利率を5%とすると276万円もの金額になっています。

 もちろん現在の金融情勢で5%の利率を確保するのは簡単ではありません。しかし株式投資や投資信託などを組み合わせて将来に向けての長期運用ならば決して不可能ではないでしょう。

 図1の7行目以降は1年ごとの年初・年末の資産額とその年に受け取れる利息を計算しています。この部分は関数は使わず、4則計算だけで求めています。PMT関数を使って求めた年額を検算するためのものと考えてください。

 この表では1年間に取り崩す額を年初に引き出して12等分して毎月使うという前提にしています。

グラフの数が追従する

 1年目の年初の資産はB1セルに入力した金額そのものです。そこから取り崩す額(B4)を引き算すると年末の資産になります。年末の資産に利率を掛け算すると利息になります。年末の資産と利息を足すと翌年の年初の資産になります。この計算を繰り返して50年分作成してあります。D1セルで指定した期間より後は条件付き書式機能で文字色を白にして見えなくしてあります。

 毎年の資産額と利息額をグラフにしたのが図2です。20年なのでグラフも20本です。Excelでは通常、グラフの元データの数を固定的に決めますが、それだと取り崩しの期間を変えたときにグラフの本数が変わりません。そこでウラワザ的な仕掛けで、取り崩す期間(図1のD1セル)に合わせてグラフの本数が追従する仕組みを備えています。

図2 年末の資産額と得られる利息を積み上げ棒グラフで表示した。図1のD1セルで指定した期間に従ってグラフの本数が自動的に変化する仕掛けを組み込んである
[画像のクリックで拡大表示]

 詳細はファイルをダウンロードしてご確認ください。Excelの「挿入」メニューから「名前」→「定義」を選ぶといくつかの名前が定義されています。このうち「資産のグラフ範囲」と「利息のグラフ範囲」という名前で使っているOFFSET関数がポイントです。

■解説で使用したExcelファイルを以下からダウンロードできます。

 Excelファイルと集約実験用のサンプルファイルを圧縮した自己解凍形式(58KB)
 Excelファイル形式(45KB)

 うまくダウンロードできない場合は日経パソコン講座ファイルのダウンロード方法をご覧ください。

あなたにお薦め

連載新着

連載目次を見る

今のおすすめ記事

  • 【ニュース解説】

    副業解禁のインパクト

     働き方改革の一環として、企業で働く社員の「副業」に注目が集まっている。副業は新たな収入源の確保、転職しなくても新しい仕事にチャレンジできるといったポジティブな側面がある。一方、過重労働や本業と副業での利益相反といった懸念もある。

ITpro SPECIALPR

What’s New!

経営

アプリケーション/DB/ミドルウエア

クラウド

設計/開発

サーバー/ストレージ

クライアント/OA機器

ネットワーク/通信サービス

セキュリティ

もっと見る