逃げ切り計算機のEXCEL版関数説明編
逃げ切り計算機のEXCEL版を作成してみた話を以前に書きました。
今回はその際の関数説明をしていきたいと思います。
とはいっても単純な関数しか使用していません。
前回書いた疑問点は解消されていないのですが、まあ、大勢に影響はないかなと判断しています。
パラメタ入力部分
まずは入力部分についてです。
- C3：現在の年齢
- C4：現在の貯金額
- C5：年間利息
- C6：年金支給開始までの年間支出額
- C7：年金受給開始後からの年間支出額
- C8：年金受給開始年齢
- C9：受給年金の月額
- C10：年間インフレ率
- C11：受給年金のインフレ連動
C11のインフレ連動についてはインフレ連動ありなら1、なしなら0を入力するイメージです。個人使用ですので、特に入力チェックはしていません。
あとは必須ではないですが、毎回計算すると関数が煩雑になる為、計算用の一時エリアとして以下を設定しています。
- 利息単位(万円)をC14に1000を保持
- 年金迄の期間としてC15にC8-C3の結果を保持
計算テーブル部分
メインの計算部分についてです。
まずはヘッダについてですが、以下のような項目にしています。
- M2：年齢
- N2：経過年
- O2：元本
- P2：利息
- Q2：利息加算後
- R2：年間支出額(前)
- S2：年間支出額(後)
- T2：受給年金額
- U2：現在資産額
年齢
M列は年齢のエリアになります。
M2がヘッダですので、M3～実際の値となりますが、M3は現在の年齢＋1となりますので、関数としては=C3+1になります。
M4以降は＋1ずつ増やして、M4なら=M3+1、M5なら=M4+1・・・・となります。
一応、M152ぐらいにしていますが、どこまで続けるかは任意です。
経過年
N列は経過年のエリアになります。特に計算では使用していませんが、見た目で分かりやすくするために1から連番で振っています。
元本
O列は元本のエリアになります。
利息の計算元となる金額になりますが、最初のO3は入力した現在の貯金額となりますので、=C4を設定します。
あとは前年の現在資産額となり、O4は=U3、O5は=U4・・・・・とします。
利息
P列は利息のエリアになります。
利息計算は本家に倣って、1,000万単位とします。
元本を1,000万単位にしてから年間利息を計算する感じです。
P3ですと=(ROUNDDOWN(O3/$C$14,0)*$C$14)*$C$5/100となります。
以降、O3の部分がP4ならO4、P5ならO5と変わっていきます。
利息加算後
Q列は利息加算後のエリアです。
このエリアも必須ではないですが、計算の一時エリアとして使用しています。
単純に元本＋利息の値です。Q3なら=O3+P3ですね。
年間支出額(前)
R列は年間支出額(前)のエリアになります。
年金受給開始迄の支出額をインフレ率を加味して設定します。
つまり、R3は最初なので、=C6となりますが、
R4は$R3*(1+$C$10/100)、R5は$R4*(1+$C$10/100)・・・・となります。
これに年金受給開始迄という判定を加えて
R4なら=IF($C$8+1>M4,$R3*(1+$C$10/100),0)、R5なら=IF($C$8+1>M5,$R4*(1+$C$10/100),0)・・・・とします。
年間支出額(後)
S列は年間支出額(後)のエリアになります。
年金受給以降の支出額をインフレ率を加味して設定します。
まず、年金受給開始迄は0になります。
また、年金受給開始年齢の時にはそれまでの期間のインフレ率を計算する必要があります。これは本家のQAでも述べられている部分です。
Q.「受給年金のインフレ連動」とは何ですか。
A.例として現在50歳で10年後の60歳から年金8万円を受け取るとして、インフレ率2%とした場合。60歳のときに受け取る年金額(1ヶ月分)は、「インフレ連動なし」であれば8万円となります。 一方、「インフレ連動あり」であれば、8万円×(1.02の10乗)となります。
受給開始年齢＋1～は年間支出額(前)と同様に前年分にインフレ率を加味すればよいですから、計算式としては以下のようになります。
S3を例にすると
=IF($C$8+1>M3,0,IF($C$8+1=M3,$C$7*(1+$C$10/100)^$C$15,S2*(1+$C$10/100)))
となります。
正確にいうと、S3でS2*(1+$C$10/100)はあり得ませんけどね。
S4なら=IF($C$8+1>M4,0,IF($C$8+1=M4,$C$7*(1+$C$10/100)^$C$15,S3*(1+$C$10/100)))
S5なら=IF($C$8+1>M5,0,IF($C$8+1=M5,$C$7*(1+$C$10/100)^$C$15,S4*(1+$C$10/100)))
といった感じです。
受給年金額
T列は受給年金額のエリアになります。
考え方は年金支出額(後)と同じですが、受給年金のインフレ連動の加味と年額(×12)にする必要があります。
T3なら=IF($C$8+1>M3,0,IF($C$8+1=M3,$C$9*(1+$C$11*$C$10/100)^$C$15*12,$T2*(1+$C$11*($C$10/100))))
T4なら=IF($C$8+1>M4,0,IF($C$8+1=M4,$C$9*(1+$C$11*$C$10/100)^$C$15*12,$T3*(1+$C$11*($C$10/100))))
といった感じです。
現在資産額
U列は現在資産額のエリアになります。
利息加算後＋受給年金額から年間支出額を減算したものです。
U3なら=Q3-R3+T3-S3
U4なら=Q4-R4+T4-S4
ですね。
試算結果部分
最後に試算結果を表示する部分になります。
G列～J列を判定用に使用しています。
G列は現在資産額のU列をみて、プラスかマイナス直前であれば表示対象の判断をしています。具体的にはG25～G152ぐらいまでを使用していますが、
G25なら=IF(OR(U3>=0,AND(U2>=0,U3<0)),"1","0")
G26なら=IF(OR(U4>=0,AND(U3>=0,U4<0)),"1","0")
という感じです。
H列～J列は表示内容判定用です。3列も要らないのですが、複雑になり過ぎないように3つに分けてます。
H25なら="=>(利息"&P3&" 支出"&ROUNDDOWN(R3,0)&")=>"&M3&"歳で"&ROUNDDOWN(U3,0)&"万円"
H26なら="=>(利息"&P4&" 支出"&ROUNDDOWN(R4,0)&")=>"&M4&"歳で"&ROUNDDOWN(U4,0)&"万円"
I25なら="=>(利息"&P3&" 支出"&ROUNDDOWN(R3,0)&")=>"&M3&"歳で"&ROUNDDOWN(U3,0)&"万円【年金受給開始】"
I26なら="=>(利息"&P4&" 支出"&ROUNDDOWN(R4,0)&")=>"&M4&"歳で"&ROUNDDOWN(U4,0)&"万円【年金受給開始】"
J25なら="=>(利息"&P3&" 支出"&ROUNDDOWN($S3,0)&" 年金"&ROUNDDOWN(T3,0)&")=>"&M3&"歳で"&ROUNDDOWN(U3,0)&"万円"
J26なら="=>(利息"&P4&" 支出"&ROUNDDOWN($S4,0)&" 年金"&ROUNDDOWN(T4,0)&")=>"&M4&"歳で"&ROUNDDOWN(U4,0)&"万円"
といった感じです。
上記を踏まえて、B列を以下のように設定。
B25なら=IF(G25="1",IF($C$8>M3,H25,IF($C$8=M3,I25,J25)),"")
B26なら=IF(G26="1",IF($C$8>M4,H26,IF($C$8=M4,I26,J26)),"")
最後にB21に以下を設定して終わりです。
="試算の結果、あなたは"&$C$3+COUNTIF(G25:G149,"1")-1&"歳まで生きられそうです。"
さいごに
WEBなら本家の逃げ切り計算機を使用できますし、もう少し複雑な逃げ切り計算機2もあります。ですから、EXCEL版の使用用途は限られるかなとは思います。
尚、出来上がったEXCELをアップするのは、本家もありますので、止めておき、あくまでEXCEL関数を使って、同じような計算ができることの説明にとどめました。
興味がある方は試してみてはいかがでしょうか。
作成したのは個人用で冗長になっていますので、もっとスマートな形にしたり、自分なりにカスタマイズするのも面白いかと思います。
