SWR accounting

Research on Safe Withdrawal Rates

Moderator: hocus2004

Post Reply
JanSz
* Rookie
Posts: 5
Joined: Thu May 08, 2003 11:13 pm

SWR accounting

Post by JanSz »

Ok, I have reached conclusion and have my plan established.

My plan.
Spend all of my SS and pension as they come.
SS is inflation adjusted, pension is not.
Spend 2%(X) of my current port value plus half (Y) of my gains if any.
I am taking stock of my situation once a month and also yearly.
When I calculate my averages, to get better resolution I do it counting number of days.
My system will work on a 6 year (72 month(Z)) average basis to smooth out volatility.

What I need is a spreadsheet that will help me with all the accounting details.

Sometimes I may be over/under drawing, need estimate that will tell me where I stand at any given time. Sometimes (like now), I do not have enough gains, so I am spending 2% regardless. If I am forced to do that for a long time my port will be decreasing in actual $$s. My plan says that I have to make up for any time when I was withdrawing only 2% but my actual gains were less than that.
Preferable situation is when I would have a gains (on 6 y average), but if I do not, then I need to know how much gains would I need to break even.

I have created simple spreadsheet to accommodate my situation. I am not sure if I accounted for all the variables. Someone, more handy with spreadsheets may want to design better one.
We could also discuss needs of people with similar plans.

JanSz
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Welcome, JanSz.

I can adapt the Retire Early Safe Withdrawal [Rate] Calculator to do most of this. To get the one-half of the increase will require you to modify formulas for every year in the spreadsheet. If you are willing to do that, I am willing to come up with the formulas and detailed instructions. (This is likely to require you to modify one or two formulas for each start year. It will not require you to write a new formula for every year after a retirement has begun. The fill handle feature of Excel will do that for us.)

I will need to know whether you want the six-year gains to be based on nominal dollars (i.e., without inflation adjustments) or on real dollars (i.e., every year in the average is adjusted for inflation). I may be able to make both options available. My guess is that you would want to use nominal dollars. It would be easier for you to determine the amount to withdraw.

Here are procedures related to your Social Security income.

For Social Security, make these entries into the Retire Early Safe Withdrawal Calculator. No modifications are necessary. It can be used as is (whether as originally downloaded or with any of my modifications).

1) Put your first year's social security income amount into cell B4 (as the Initial Balance).
2) Set the Stock Allocation to zero percent. (Write 0 into cell B6.)
3) Select TIPS with a 0% coupon rate. (Write 4 in B7 and 0 in H8.)
4) Set the Initial Withdrawal Rate to 0%. (Write 0 in B9.)
5) Set the Investment Expenses to 0%. (Write 0 in B15.)

All of the summary data in columns S through AK and rows 15 through 144 are available to you. This gives the nominal amount of your social security income at 10, 20, 30, 40, 50 and 60 years after any start date beginning with 1871. The real income never changes.

Let me warn you: there was serious deflation in the late 1800s. The nominal dollar amount would have been much lower in 1881 than in 1871 (maintaining a constant level of buying power). Things will not seem normal to you until you scroll down to more recent start years.

You can also scroll down to read the balances after every start year.

I have come up with a method of summarizing the buying power of your fixed dollar amount pension (i.e., constant amount in nominal dollars). It requires a simple change that is applicable to any version of the Retire Early Safe Withdrawal Calculator. This includes any or all of my previous modifications.

I will post that shortly.

Have fun.

John R.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

I need to make it clear that my modifications of the Retire Early Safe Withdrawal [Rate] Calculator do not allow you to track your current portfolio. They let you see what would have happened if someone had used a similar approach in the past. This helps you understand what is typical with your approach. It helps to warn you if something is going wrong. It can help you improve results. Quite often, minor changes in a withdrawal strategy result in dramatic improvements.

Have fun.

John R.
JanSz
* Rookie
Posts: 5
Joined: Thu May 08, 2003 11:13 pm

Post by JanSz »

Thanks for quick response, John.

In a real time spending part of my retirement I am using actual gains/loses to make purchasing decisions.
At this point it is immaterial to me where the gains came from, Tips, stocks, lottery, SS, gifts whatever, they all are equal an put in a plus side of the ledger.
I know history and am not predicting anything.
I allow myself to spend SS, pension and 2% of my port value today unconditionally.
If I have any net investment gains from my investment portfolio over period of last 6 years, then I am allowed to spend half of them.
During time when I do not have gains I try to not make big purchases, buying cars, expensive vacations, fixing a house, but I would spend on health care.
When I am in a period of actually having investment gains to spend, then, I would spend on those expensive items.

I work with nominal $$, they are not changing much due to inflation within 6y horizon.
Also, we usually do not know current inflation rate, but it would be convenient to have it both ways, with and with out inflation.
If possible it would be nice to have the 2% withdrawal as a variable, also there could be provision for someone who would like to deal with 6, 5, or 4 y averages.
Also amount of spendable gains (50%) could be made as a variable.

At any time I would like to know where I stand if everything was in perfect alingnment, ie; if I was spending exactly 2% plus half of my gains.
I would know then by how much I am under or over drawing my port relative to perfect state.

How I would be able to get a hold of that spreadsheet, assuming you will have a time to produce it?

Happy holidays;
JanSz
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

This lets you see what would have happened to the buying power of a pension with a constant dollar amount. That is, payments do not change to match inflation.

What I have done is copy the data summary section in columns S through AK and rows 10 through 144. Then I have calculated the inflation adjusted value of the initial amount for each sequence. This new data summary section has the start year of a retirement and the buying power of the initial balance after 10, 20, 30, 40, 50 and 60 years.

This modification may be made to the Retire Early Safe Withdrawal [Rate] Calculator and/or all of my modified versions.

First bring up the calculator that you wish to modify. You may prefer to do your work on a copy instead of the original.

The first step is to copy and paste the data summary section into a new area. I chose to paste my copy in columns BS through CK and in rows 10 through 144. (The original summary is in columns S through AK and in rows 10 through 144.)

Highlight (i.e., click) cell S10. Press the Shift key. Highlight cell AK144. Release the Shift key.

Click on Edit, then Copy. Click on an empty cell.

[When you have finished all copying tasks, click on a blank cell then click on the formula bar to complete copying. You do not have to do this until the final step.]

Highlight cell BS10. Press the Shift key. Highlight cell CK144. Release the Shift key.

Click on Edit, then Paste (not Paste Special). Click on an empty cell.

The next step is to change the formulas in row 15.

Highlight cell BV15. In the formula bar near the top, change the formula from =BT15/BU15 to =$B$4/BU15 and click the check mark.

Highlight cell BY15. Change the formula to =$B$4/BX15 and click the check mark.

Highlight cell CB15. Change the formula to =$B$4/CA15 and click the check mark.

Highlight cell CE15. Change the formula to =$B$4/CD15 and click the check mark.

Highlight cell CH15. Change the formula to =$B$4/CG15 and click the check mark.

Highlight cell CK15. Change the formula to =$B$4/CJ15 and click the check mark.

Next, use the fill handle to drag the formulas down.

Start with cell BV15. Then repeat with cells BY15, CB15, CE15, CH15 and CK15.

Locate the fill handle on the lower right hand corner of cell BV15. Place the mouse cursor over the fill handle. The fat plus sign should change to narrow cross hairs. Click down on your mouse button and hold it down. Drag the fill handle (and the formula) down to row 144 (i.e., cell BV144). Release the mouse button. Click on any cell.

When you repeat this, drag the formula only as far down as it applies. That is, for column BY, drag it down to row 134. For column CB, drag it down to row 124. For column CE, drag it down to row 114. For column CH, drag it down to row 104. For column CK, drag it down to row 94. It is easy to tell how far a formula applies. It applies as long as the Inflation Factor in the column next to it (on the left) has data.

The final step is optional.

I wrote Buying Power of the Initial Balance into cells BU8, BX8, CA8, CD8, CG8 and CJ8.

This new data summary section always displays results based on the initial balance (that you enter in cell B4). Other settings do not affect these calculations.

Have fun.

John R.
Post Reply