Page 1 of 1

Incorporating JanSz's Algorithms

Posted: Wed Dec 24, 2003 12:46 pm
by JWR1945
This tells you how to modify Retire Early Safe Withdrawal [Rate] Calculators (which includes Version 1.61 and all of my modified versions) to incorporate JanSz's withdrawal algorithm. The new calculators tell you how well the algorithm would have done in the past. This write up does not tell you how to monitor how closely your own withdrawals have matched the algorithm.

JanSz's Algorithm
reference = http://nofeeboards.com/boards/viewtopic.php?t=1891

I have extracted some of the key features of JanSz's algorithm while retaining as much of the capabilities of the existing calculators as possible. This does not quite incorporate JanSz's algorithm, but it comes close.

JanSz's approach is to withdraw 2% of a portfolio's current balance and one-half of its gains over six years. If the portfolio's balance has decreased, the withdrawal is 2% of the portfolio's current balance.

Instructions for Using This Implementation

I have made no changes to the existing algorithm. The Initial Balance (in cell B4), Stock Allocation (in cell B6) and the Fixed Income Series (in cell B7) are the same as before. You should put 0% into cell B9 for the Initial Withdrawal Rate. You should add 2% to your Investment Expenses in cell B15. I have personally decided to enter 2.20%. (I normally use 0.20% for expenses.)

I have written Capital Gain Percentage into cell A17. This is the percentage of gains that I withdraw. In this case, I have entered 50% into cell B17.

Everything else about the calculator remains as before. You can take advantage of all existing capabilities. For example, if you wanted to withdraw 1% of the initial balance and increase (or decrease) it to match inflation, you would write 1% into cell B9. If you wanted to reduce your withdrawals based upon the portfolio's current balance to compensate for this, you could choose to write 1.20% into cell B15 for your (total) investment expenses. You would start out withdrawing 2% and paying 0.20% in expenses. Half of that initial withdrawal amount would follow inflation and the other half would follow your portfolio's current balance.

You might prefer to withdraw 75% of any capital gains (compared to six years earlier). You would put 75% into cell B17. Or you might drop it down to 25%. You just enter whatever percentage that you wish into cell B17.

What I have kept fixed is the comparison that I make for the capital gains. It would not be difficult to come up with a more general algorithm, but entering it would be tedious.

Initial Steps

Make a copy of an existing calculator that you wish to modify. There is also the possibility that I have made some errors. I don't think so. I just don't want to start again from scratch if something has gone wrong. These modifications require enough effort that you will want to avoid corrupting your original versions if something goes wrong.

To make that copy, click on the gray (cell like) area to the left of column A and above row 1. That is a spreadsheet version of Select All.

I typed JanSz in front of Special V1.0 into the formula bar after highlighting (i.e., clicking on) cell E1. I wrote Capital Gains Percentage into the formula bar after highlighting cell A17. I highlighted cell B17. At the very top, I clicked Format, then Cells and finally the tab with the word Numbers. I selected Percentage and I set the number of decimal places to 0. Then I clicked OK.

In every case, clicking on an empty cell completes these entries. If in doubt, click the check mark (when available) in the formula area or press the enter key.

Entering the Algorithm

Scroll down to row 199. The numbers on this row are the balances on January 1st before any trading has taken place. They are identical to those from the year before on row 215. Row 215 has the balances after all trading has ended on December 31st. That is, C199 (from year 1872) is identical to B215 (from year 1871). All of the data summaries report what happens on December 31st of each year. Those correspond to the years listed by another calculator, the FIRECalc, which is exceptionally user friendly but which you cannot modify.

Notice that the initial balance for each year for starting a retirement sequence appears on the last row for that year's calculations. Each new start year is 18 rows below and one column to the right of the previous year. I am confident that there is an easy way to make use of this fact. To my dismay, I have yet to discover how to take advantage of it. As a result, changing the calculators takes much longer than it should.

Highlight cell I201. It will read =IF(I199<=0,0,-$B$15*I199)*$B$10 before you make any changes. Copy the formula. (Highlight the formula area, click Edit and then click Copy. Finally, click in the highlighted region to remove the highlight.) Now Paste the formula right behind the original. It will read =IF(I199<=0,0,-$B$15*I199)*$B$10 =IF(I199<=0,0,-$B$15*I199)*$B$10.

Change the second = sign to a +. Change $B$15 to $B$17. Add a close parenthesis ) to the end of the formula. Make additional changes (by introducing C199 twice). The new part of the formula (related to capital gains) is +IF(I199<=C199,0,-$B$17*(I199-C199)*$B$10).

The formula should now look like this:
=IF(I199<=0,0,-$B$15*I199)*$B$10+IF(I199<=C199,0,-$B$17*(I199-C199)*$B$10)

Notice that cell C199 has the first balance that we can reference and that cell I199 corresponds to six years later.

Highlight cell I199. Locate the fill handle on the lower right hand corner. Move your mouse over it. When the fat plus sign has changed to narrow cross hairs, click (down on your mouse button) and hold (the button down). Drag the fill handle to the right you have highlighted all of the remaining years in the sequence. Release your mouse button. Finally, click on any cell. I generally click on the last cell in the highlighted region. This process drags the formula (with appropriate changes in cell locations) onto the entire highlighted region.

You may drag the fill handle in several steps. Just start where you have left off, locate the fill handle and drag the formula farther to the right.

This process uses a minimal number of steps.

Now we have to do the same thing to cell I214. The formula is just a little bit different, but not much. It starts with =IF(I212<=0,0,-$B$15*I212)*(1-$B$10). Copy it and paste a copy just to the right of the original formula. Change the second = sign to a +. Change $B$15 to $B$17. Add a close parenthesis ) to the end of the formula. Make additional changes (by introducing C212 twice) to include capital gains: +IF(I212<=C212,0,-$B$17*(I212-C212)*(1-$B$10)).

The completed formula looks like this:
=IF(I212<=0,0,-$B$15*I212)*(1-$B$10)+IF(I212<=C212,0,-$B$17*(I212-C212)*(1-$B$10)).

Locate the fill handle, highlight it, drag it across all of the cells to the right and release the mouse button. Finally, click any cell to complete the task.

Be sure not to overlook the minus signs. Make sure that they are present. Otherwise, you will be making deposits instead of withdrawals. [Only the balances have to remain positive or equal to zero. The expenses are made equal to zero whenever the current balance is negative. You can make deposits by entering negative numbers for withdrawal amounts and/or expenses.]

Now for the tedious part: repeat all of these steps for every year that follows. You must remember the column in which the first withdrawal expense is made. It was column C for start year 1871. Then you must count six cells over to enter your first formula. Make changes similar to those that we have made before. Locate the fill handle and drag the formula to the right to cover the remaining years in the sequence.

Locate every row in which expenses are charged. There are two rows for you to change for every year that is listed. Repeat the steps until you have changed all of those rows.

Before too long, you will remember how the formula changes and things will go faster. Here is a hint: the row numbers in the formula are all the same. Each time, when you first change the formula, the column letters are for the first year in a sequence (at the top) and for six years later when you are first able to calculate your capital gains. You can check (and recheck, as needed) to make sure that all of the changes are right. You will be changing the second = to a +, $B$15 to $B$17 and adding a close parenthesis ) to the second part of the formula. You will be introducing the first year of the sequence into the formula twice.

This is not difficult, but it is tedious and time consuming.

Check your work every now and then. Make sure that you have dragged the formula across all of the years to the right of your first change. Make sure that you have written the first change for each start year in the correct column (six years after the start). Make sure that you have written the formula correctly.

This completes the changes to the calculator.

Copying the changes

Highlight cell EK2537 by clicking on it. Press down on the Shift key. Highlight cell B199. Release the Shift key. At the top, click Edit and then click copy.

On any other version of the calculator which you wish to change, highlight cell EK2537, press the Shift key, highlight cell B199 and release the Shift key. At the top, click Edit and then Paste. There will be a delay until the pasting operation has been completed. Then click any cell to complete the process.

You should make the changes listed as Initial Steps. Or you may prefer to copy cells A17, B17 and E1.

When all copying has been completed, check your original copy of the modified spreadsheet. You may have to click the equals sign or check mark. You will probably have to click in the formula area or on a blank cell.

Additional Comments

All of the data summary features still work. You will want to look at the Terminal Values in both nominal and in inflation adjusted (real) dollars in columns S through AK and rows 10 through 144. If you have made my data summary modifications, look at columns BH through BJ, rows 82 through 144. It shows balances in the year 2000. Without that information, the other summary data are likely to mislead you. (This issue has to do with partially completed sequences and dummy inputs for the years 2002-2010.)

Here is something worth knowing about. The entry in cell B14, Withdrawal for Inflation?, affects only withdrawals based upon the initial balance. Within the calculator itself, all balances are in nominal dollars. Only the data summaries report balances in terms of real dollars (i.e., adjusted to match inflation).

Depending upon your unique situation, you might be able to use this feature to learn something related to pensions and/or annuities that do not increase (or decrease) to match inflation. You would have to look at your investments as a whole. For example, you might wish to contribute part of your retirement pension into your overall portfolio. You could do this by putting a negative withdrawal rate (relative to the initial balance) into cell B9 and choosing not to match inflation in cell B14.

It is difficult to track your withdrawal amounts. They are not available in the summary data. There are at least two withdrawals, one made on January 1st and the other made on December 31st of each year. You have to scroll down to individual years to read their amounts.

It is much easier to monitor your portfolio balances since they are sampled at ten year intervals and reported in the summary data.

Have fun.

John R.