エクセルの財務関数－老後資金の取り崩し計画立案
こんにちは。
前回は、つみたて投資の計画を立案する場合を例にして、エクセルの財務関数の具体的な使い方をまとめました。今回は、老後資金の取り崩し計画を立案する場合を例にして、エクセルの財務関数の具体的な使い方をまとめます。
老後資金を期末に取り崩す例
条件
以下の条件で老後資金を取り崩す場合を例に考えます。
- 利率4%で運用できる
- 老後資金の取り崩しは3期行う
- 毎期100万円ずつ取り崩す
- 老後資金として、最初に2000万円を運用している
- 3期取り崩した後、1937万5680円残っている
- 期末に取崩しを行う（その期分の金利が付いてから取り崩す）
将来価値の推移
期末取崩しと期首取崩しの違いを分かりやすくするために、一つの期における金利分とつみたて分を分けてグラフ化してみます。
エクセルの財務関数の引数
収支は、手持ちのお金が減るとマイナス、増えるとプラスと考えます。
運用している老後資金はすぐに使える手持ちのお金では無いので、運用している老後資金はマイナスと考えます。
投資した資金を取り崩す（売却する）と手持ちのすぐに使えるお金が増えるので、つみたて投資を売却する価値はプラスと考えます。
したがって、今回の条件をエクセルの財務関数を使う場合に当てはめると以下の表になります。
エクセルの財務関数の計算式
エクセルの財務関数を使って、今回の条件を計算してみます。
利率
老後資金の当初金額2000万円を、期末に100万円ずつ取り崩して、3期後に1937万円5680円を得るのに必要な利率は？
RATE = RATE(nper, pmt, pv, fv, type, guess)
利率 = RATE(期間, 定期支払額, 現在価値, 将来価値, 支払期日, 推定値)
なので
RATE(3,1000000,-20000000,19375680,0) = 4%
回数
老後資金の当初金額2000万円を、金利4%で運用しながら、期末に100万円ずつ取り崩して、1937万円5680円になるのは何期後か？
Number of PERiods = NPER(rate,pmt,pv,fv,type)
期間 = NPER(利率,定期支払額,現在価値,将来価値,支払期日)
なので
NPER(4%,1000000,-20000000,19375680,0) = 3
取り崩し額
老後資金の当初金額2000万円を、金利4%で運用しながら期末ごとに取り崩して、3期後に、1937万円5680円を残したい場合は、毎期どれだけ取り崩しても良いか？
PayMenTs = PMT(rate, nper, pv, fv, type)
定期支払額 = PMT(利率, 期間, 現在価値, 将来価値, 支払期日)
なので
PMT(4%,3, -20000000,19375680,0) = 1,000,000
現在価値
老後資金を、金利4%で運用しながら期末に100万円ずつ取り崩して、3期後に、1937万円5680円を残したい場合、当初どれだけ用意すればよいか？
Present Value = PV(rate, nper, pmt, fv, type)
現在価値 = PV(利率, 期間, 定期支払額, 将来価値, 支払期日)
なので
PV(4%,3,1000000, 19375680,0) = -20,000,000
将来価値
老後資金の当初金額2000万円を、金利4%で運用しながら期末に100万円ずつ取り崩すと、3期後に、どれだけ残っているか？
Future Value = FV(rate,nper,pmt,pv,type)
将来価値 = FV(利率,期間内支払回数,定期支払額,現在価値,支払期日)
なので
FV(4%,3,1000000,-20000000,0) = 1,000,000
老後資金を期首に取り崩す例
条件
以下の条件で老後資金を取り崩す場合を例に考えます。
- 利率4%で運用できる
- 老後資金の取り崩しは3期行う
- 毎期100万円ずつ取り崩す
- 老後資金として、最初に2000万円を運用している
- 3期取り崩した後、1925万0816円残っている
- 期首に取崩しを行う（その期分を取り崩した後に金利が付く）
将来価値の推移
期末取崩しと期首取崩しの違いを分かりやすくするために、一つの期における金利分とつみたて分を分けてグラフ化してみます。
エクセルの財務関数の引数
今回の条件をエクセルの財務関数を使う場合に当てはめると以下の表になります。
エクセルの財務関数の計算式
エクセルの財務関数を使って、今回の条件を計算してみます。
利率
老後資金の当初金額2000万円を、期首に100万円ずつ取り崩して、3期後に1925万円0816円を得るのに必要な利率は？
RATE = RATE(nper, pmt, pv, fv, type, guess)
利率 = RATE(期間, 定期支払額, 現在価値, 将来価値, 支払期日, 推定値)
なので
RATE(3,1000000,-20000000,19250816,1) = 4%
回数
老後資金の当初金額2000万円を、金利4%で運用しながら、期首に100万円ずつ取り崩して、1925万円0816円になるのは何期後か？
Number of PERiods = NPER(rate,pmt,pv,fv,type)
期間 = NPER(利率,定期支払額,現在価値,将来価値,支払期日)
なので
NPER(4%,1000000,-20000000, 19250816,1) = 3
取り崩し額
老後資金の当初金額2000万円を、金利4%で運用しながら期首に取り崩して、3期後に、1925万円0816円を残したい場合は、毎期どれだけ取り崩しても良いか？
PayMenTs = PMT(rate, nper, pv, fv, type)
定期支払額 = PMT(利率, 期間, 現在価値, 将来価値, 支払期日)
なので
PMT(4%,3, -20000000, 19250816,1) = 1,000,000
現在価値
老後資金を、金利4%で運用しながら期首に100万円ずつ取り崩して、3期後に、1925万円0816円を残したい場合、当初どれだけ用意すればよいか？
Present Value = PV(rate, nper, pmt, fv, type)
現在価値 = PV(利率, 期間, 定期支払額, 将来価値, 支払期日)
なので
PV(4%,3,1000000, 19250816,1) = -20,000,000
将来価値
老後資金の当初金額2000万円を、金利4%で運用しながら期首に100万円ずつ取り崩すと、3期後に、どれだけ残っているか？
Future Value = FV(rate,nper,pmt,pv,type)
将来価値 = FV(利率,期間内支払回数,定期支払額,現在価値,支払期日)
なので
FV(4%,3,1000000,-20000000,1) = 19,250,816
今回は、老後資金の取り崩し計画を立案する場合を例にして、エクセルの財務関数の具体的な使い方をまとめました。次回は、住宅ローンの返済計画を立案する場合を例にして、エクセルの財務関数の具体的な使い方をまとめます。
