Is it this simple? My calculation

[dex]

I'm always playing around with Excel models to see if my RE is realalistic - intellectually I know it is but I don't always believe it. The house is paid it full and not included in the calculations - current net market value 260K
So is this really true? Is there any flaws in my thinking?
A = Pension - start when I reach 60 grow at 1%
B = Social security - start at 62, grow at 1%
C = Net Asset growth after expenses
D = Assets - excluding house - subtracting out total expenses at the beginning of the year and then growing at 5% - assume growth after tax
E = Spending, after taxes - growth 3.5%
F = Effective withdrawl rate from Assets
G = Age
85 = assume death
#### ignore just there to have everything line up
Aaaaaa Bbbbbb Cccccc Dddddd Eeeeee Fffff Gggg
abcde - Assets Total - - -
Pension Social Growth Rate Assets Spending Withdrawl Age
##### Security Cash 1,198,678 ##### Rate -
##### ##### ##### ##### ##### ##### -
##### ##### Total 1,198,678 ##### ##### -
101.0% 101.0% ##### 105.0% 103.5% Growth Rates
Aaaaaa Bbbbbb Cccccc Dddddd Eeeeee Fffff Gggg
##### ##### -3.7% 1,154,678 (44,000) -3.7% 50
##### ##### 0.86% 1,164,595 (45,540) -3.8% 51
##### ##### 0.75% 1,173,334 (47,134) -3.9% 52
##### ##### 0.63% 1,180,778 (48,784) -4.1% 53
##### ##### 0.51% 1,186,801 (50,491) -4.2% 54
##### ##### 0.38% 1,191,270 (52,258) -4.4% 55
##### ##### 0.23% 1,194,042 (54,087) -4.5% 56
##### ##### 0.08% 1,194,965 (55,980) -4.7% 57
##### ##### -0.09% 1,193,876 (57,940) -4.8% 58
##### ##### -0.27% 1,190,604 (59,967) -5.0% 59
10,360 - 0.40% 1,195,325 (62,066) -5.2% 60
13,814 - 0.51% 1,201,455 (64,239) -5.4% 61
13,952 14,321 1.54% 1,219,989 (66,487) -5.5% 62
14,092 19,095 1.80% 1,241,920 (68,814) -5.7% 63
14,232 19,285 1.68% 1,262,750 (71,223) -5.9% 64
14,375 19,478 1.55% 1,282,340 (73,715) -6.1% 65
14,519 19,673 1.42% 1,300,538 (76,295) -6.4% 66
14,664 19,870 1.28% 1,317,185 (78,966) -6.6% 67
14,810 20,069 1.13% 1,332,107 (81,730) -6.8% 68
14,958 20,269 0.98% 1,345,120 (84,590) -7.1% 69
15,108 20,472 0.81% 1,356,028 (87,551) -7.3% 70
15,259 20,677 0.63% 1,364,620 (90,615) -7.6% 71
15,412 20,883 0.44% 1,370,670 (93,787) -7.8% 72
15,566 21,092 0.24% 1,373,939 (97,069) -8.1% 73
15,722 21,303 0.02% 1,374,171 (100,466) -8.4% 74
15,879 21,516 -0.22% 1,371,092 (103,983) -8.7% 75
16,038 21,731 -0.49% 1,364,413 (107,622) -9.0% 76
16,198 21,949 -0.78% 1,353,821 (111,389) -9.3% 77
16,360 22,168 -1.10% 1,338,988 (115,288) -9.6% 78
16,523 22,390 -1.45% 1,319,562 (119,323) -10.0% 79
16,689 22,614 -1.85% 1,295,169 (123,499) -10.3% 80
16,856 22,840 -2.30% 1,265,411 (127,821) -10.7% 81
17,024 23,068 -2.81% 1,229,864 (132,295) -11.0% 82
17,194 23,299 -3.40% 1,188,078 (136,925) -11.4% 83
17,366 23,532 -4.08% 1,139,577 (141,718) -11.8% 84
17,540 23,767 -4.89% 1,083,851 (146,678) -12.2% 85
17,715 24,005 -5.86% 1,020,362 (151,812) -12.7% 86
17,893 24,245 -7.04% 948,536 (157,125) -13.1% 87

 

[Telly]

Dex,

I was expecting your withdrawal rate to decline some after the start of your pension, and also after the start of SS.  Why isn't it?

On my spreadsheets I take income required, minus my puny non-inflation adj. pension, minus SS benefit.  And that is the actual withdrawal that I compute Withdrawal Rate against.

I also "plan" for age 95, though I know there's not a snowball's chance in hell that I will see it.  But my wife... who knows?

I also added an inflation-adjusted dollars column next to my assets column. Just so I remember that XXXXXX today is not the same $ as XXXXXX in the far future.

I have inflation rate as a variable, along with SS inflation. And I can insert nominal return rate on a year-by-year basis if I so choose.

 

[Telly]

Also notice a jump in pension $ from age 60 to 61, same thing with SS from age 62 to 63. The SS $ jump is too small for a spouse claiming their 50% on your account (if that was your account alone at age 62). So it makes me wonder if you are using calendar years instead of age years as your rows.

 

[haha]

Is it this simple? I would say not. I don't see that you have allowed for asset volatility. Perhaps you did, and I didn't understand. At any rate, that is an important factor, which can upset your plans.

Mikey

 

[dex]

Telly & Mikey
Thanks for the reply.
Re: WR not declining for pension & SS - formula error
Re: Inlation adjusted dollars of assets - I understand that the total assets will be less in current dollars. I'll have to work on a formula for that
Re: SS & Pension - I prorated it for the month it starts - the first years are not full years
Re: market volatility - I use FireCal for that aspect of the equation. This exercise is another confidence exercise.

Telly I used your calculation for the Withdawl rate %. It did change the WR - Thanks - see below

I really think I only want or need to start with $40K after tax income. Time will tell

Aaaaaa Bbbbbb Cccccc Dddddd Eeeeee Fffff Gggg
abcde - Assets Total - - -
Pension Social Growth Rate Assets Spending Withdrawl Age
##### Security Cash 1,198,678 ##### Rate -
##### ##### ##### ##### ##### ##### -
##### ##### Total 1,198,678 ##### ##### -
101.0% 101.0% ##### 105.0% 103.5% Growth Rates
Aaaaaa Bbbbbb Cccccc Dddddd Eeeeee Fffff Gggg
##### ##### -3.7% 1,154,678 (44,000) -3.67% 50
##### ##### 0.86% 1,164,595 (45,540) -3.94% 51
##### ##### 0.75% 1,173,334 (47,134) -4.05% 52
##### ##### 0.63% 1,180,778 (48,784) -4.16% 53
##### ##### 0.51% 1,186,801 (50,491) -4.28% 54
##### ##### 0.38% 1,191,270 (52,258) -4.40% 55
##### ##### 0.23% 1,194,042 (54,087) -4.54% 56
##### ##### 0.08% 1,194,965 (55,980) -4.69% 57
##### ##### -0.09% 1,193,876 (57,940) -4.85% 58
##### ##### -0.27% 1,190,604 (59,967) -5.02% 59
10,360 - 0.44% 1,195,843 (51,706) -4.34% 60
13,814 - 0.57% 1,202,690 (50,425) -4.22% 61
13,952 14,321 1.66% 1,222,699 (38,214) -3.18% 62
14,092 19,095 1.94% 1,246,425 (35,628) -2.91% 63
14,232 19,285 1.82% 1,269,156 (37,705) -3.03% 64
14,375 19,478 1.70% 1,290,759 (39,862) -3.14% 65
14,519 19,673 1.57% 1,311,088 (42,104) -3.26% 66
14,664 19,870 1.44% 1,329,988 (44,432) -3.39% 67
14,810 20,069 1.30% 1,347,295 (46,851) -3.52% 68
14,958 20,269 1.15% 1,362,829 (49,362) -3.66% 69
15,108 20,472 1.00% 1,376,401 (51,971) -3.81% 70
15,259 20,677 0.83% 1,387,808 (54,679) -3.97% 71
15,412 20,883 0.65% 1,396,832 (57,491) -4.14% 72
15,566 21,092 0.46% 1,403,242 (60,411) -4.32% 73
15,722 21,303 0.25% 1,406,791 (63,442) -4.52% 74
15,879 21,516 0.03% 1,407,213 (66,588) -4.73% 75
16,038 21,731 -0.21% 1,404,228 (69,853) -4.96% 76
16,198 21,949 -0.48% 1,397,535 (73,242) -5.22% 77
16,360 22,168 -0.77% 1,386,814 (76,760) -5.49% 78
16,523 22,390 -1.09% 1,371,725 (80,409) -5.80% 79
16,689 22,614 -1.44% 1,351,905 (84,196) -6.14% 80
16,856 22,840 -1.84% 1,326,968 (88,126) -6.52% 81
17,024 23,068 -2.30% 1,296,503 (92,203) -6.95% 82
17,194 23,299 -2.81% 1,260,075 (96,432) -7.44% 83
17,366 23,532 -3.40% 1,217,218 (100,820) -8.00% 84
17,540 23,767 -4.09% 1,167,439 (105,371) -8.66% 85
17,715 24,005 -4.90% 1,110,215 (110,091) -9.43% 86
17,893 24,245 -5.88% 1,044,989 (114,988) -10.36% 87

 

[John Galt]

Man. don't post that stuff during cocktail hour.
Makes my head hurt

John Galt

 

[trumpeting_angel]

I like the smilies that appear when the parentheses end with an 8. 8)

Anne

 

[dex]

On my spreadsheets I take income required, minus my puny non-inflation adj. pension, minus SS benefit.  And that is the actual withdrawal that I compute Withdrawal Rate against.

Thanks for that formula change. It does change the Withdrawl Rate calculation. Not getting over 4% until 72

 

[Telly]

Hi Dex,

Sometimes instead of wasting time with internet, I'll go play Scrooge McDuck with my spreadsheets
Putting in different scenarios and seeing what happens.
This is what mine have mutated to, at least at present:

Above the grid area, I have a starting constants area. These are the start values that I can play with:

Income (the income required, in other words, expenses).
Income Inflation (I have the default as .03%)
SS Amount (my monthy amount, it annualizes right below it, my spouse's 50% additional of mine adds in in the spreadsheet itself).
SS Inflation (I have the default as .02%)
Pension (mine is fixed and very small).
Fund (the starting value)

So these can be easily twiddled with on top.

In the grid area, I presently have columns for:

Age
Income (the required amount).
Fund pull (this is Fund - ( SS1 + SS2 + Pension).
Fund Pull % (this is Fund Pull / Fund Amount).
Pension (pitiful!).
SS1 (mine).
SS2 (spouse).
% SS (this is what % of Income is being met by the total of SS1 + SS2 amounts).
Fund Return % (I have 6.0 in there now as the default, it is easy to change it and drag it down over any amount of years, or insert different values year by year).
Fund (this is where the actions at!).
Constant $ (this is Fund with Income inflation effects removed. Can be sobering over a long time, or with a high inflation rate! If I want an end survivor value of Fund, then looking at Constant $ gives me an idea of what the buying power conversion of that Fund amount in the far future will be).

the details on the Fund calculation:

Fund = (( Prev. years Fund $ - Income ) + Pension + SS1 + SS2 ) * ( 1 + Fund Return / 100 )

Within the first set of parens, I could have just as well subtracted out Pension, SS1, SS1 from Income before subtracting Income out of Prev. years Fund $. Same thing mathematically.

So the way it is now, a full years Income (minus Pension and SS, if applicable) is pulled out at the beginning of an Age year. And the residual fund value grows by the Fund Return amount, and the Fund value represents the value at the end of the year.

Since we don't really pull out one year's Income up front, this has a negative effect. Have thought of changing the spreadsheet to two pulls 6 months apart to better approximate. Haven't tackled all the math required to do it yet. Sometime!

I like building things up myself, rather than just buying some software program. Building it up myself really gets me to understand it. That coupled with a natural suspicion of other's work (are they really doing that right? Let me work it out myself and check it!)

 

[dex]

Telly,
Your spread sheet sounds very much like mine.

Like you, I like to do the computations several different ways as a reality check.

 

[unclemick]

I don't spreadsheet except for my 40 DRIP dividend stocks. One outside the box cross check I monitor is the SEC yield of my portfolio/stocks. Heh, heh, heh - the Norwegian widow considers that real money.

Hand grenade wise - I play with FIREcalc and sometimes ORP.

The old rule of thumb - 25 times that part of income needed from the portfolio is my even rougher ballpark check - although volitility has to be considered - say a three year average port. value.

I also split the budget into core and fun - the fun being the variable part in 'hard times'.

Keep things flexible - the last eleven years did not go as orginally planned - actually turned out way better than expected in our case.