Deluxe Calculator V1.1A

Research on Safe Withdrawal Rates

Moderator: hocus2004

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

Deluxe Calculator V1.1A

Post by JWR1945 »

Deluxe Calculator V1.1A Notes

I have just replaced my Deluxe Calculator V1.0A with an newer, but not entirely checked out, version: Deluxe Calculator V1.1A. Glance through this list of tables and data summary boxes to get an idea of what it can do. BTW, if you thought that V1.0A was a memory hog when it expanded to 8MB, wait until you download V1.1A. It expands to take up 12MB. It is in a self extracting zip file for downloading. It takes 3.72MB or 3.9MB (IINM, depending upon whether you are using octal or decimal numbers).

Deluxe Calculator V1.1A Locations:

These are main data summary tables in the first 9 rows.

PORTFOLIO BALANCE in columns V through Z.

TOTAL AMOUNT WITHDRAWN in columns AB through AF.

DIVIDENDS WITHDRAWN in columns AH through AL.

INTEREST WITHDRAWN in columns AN through AR.

STOCK BALANCE in columns AT through AX.

BOND BALANCE in columns AZ through BD.

These are the table locations.

A2600 through BJ2600: Current Balances (Nominal)
BN2600 through DW2600: If current balances (Nominal) below threshold, display 1. Otherwise, zero.
DZ2600 through EF2600: If current balances (Nominal) fell below threshold, within the number years indicated, display 1. Otherwise, zero.
EI2600 through EO2600: If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.
ER2600 through EV2600: Summary box for PORTFOLIO BALANCE (nominal).

A2800 through BJ2800: Nominal Rates of Return (Annualized).

A3000 through BJ3000: Inflation Multipliers (Deflators).

A3200 through BJ3200: Current Balances (Real).
BN3200 through DW3200: If current balances (Real) below threshold, display 1. Otherwise, zero.
DZ3200 through EF3200: If current balances (Real) fell below threshold, within the number years indicated, display 1. Otherwise, zero.
EI3200 through EO3200: If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.
ER3200 through EV3200: Summary box for PORTFOLIO BALANCE (real).

A3400 through BJ3400: Real Rates of Return (Annualized).

A4000 through BJ4000: Investment Expenses (nominal). [Investment Expenses 1 and Investment Expenses 2 are both listed.]

A4300 through BJ4300: Annual Withdrawals (nominal). [Annual Withdrawals 1 and Annual Withdrawals 2 are both listed.]

A4600 through BJ4600: Initial Stock Balance (nominal).
BN4600 through DW4600: If current balances (Nominal) are below threshold, display 1. Otherwise, 0.
DZ4600 through EF4600: If current balance (Nominal) fell below threshold within the number years indicated, display 1. Otherwise, 0.
EI4600 through EO4600: If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.
ER4600 through EV4600: Summary box for STOCK BALANCE (nominal).

A4800 through BJ4800: Initial Bond Balance (nominal).
BN4800 through DW4800: If the current balance (Nominal) is below threshold, display 1. Otherwise, 0.
DZ4800 through EF4800: If current balances (Nominal) fell below threshold within the number years indicated, display 1. Otherwise, 0.
EI4800 through EO4800: If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.
ER4800 through EV4800: Summary box for BOND BALANCE (nominal).


A5000 through BJ5000: Dividends Withdrawn (nominal).
BN5000 through DW5000: If current dividend withdrawals (Nominal) are below threshold, display 1. Otherwise, 0.
DZ5000 through EF5000: If dividend withdrawals (Nominal) fell below threshold within the number years indicated, display 1. Otherwise, 0.
EI5000 through EO5000: If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.
ER5000 through EV5000: Summary box for DIVIDENDS WITHDRAWN (nominal).

A5200 through BJ5200: Interest Withdrawn (nominal).
BN5200 through DW5200: If interest withdrawals (Nominal) are below threshold, display 1. Otherwise, 0.
DZ5200 through EF5200: If interest withdrawals (Nominal) fell below threshold within the number years indicated, display 1. Otherwise, 0.
EI5200 through EO5200: If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.
ER5200 through EV5200: Summary box for INTEREST WITHDRAWN (nominal).

A5400 through BJ5400: Investment Expenses (nominal). [This has Investment Expenses 1 and Investment Expenses 2 separately.]

A5700 through BJ5700: Expenses+Withdrawals 1+2 (nominal). This totals Expenses and Withdrawals for each year. It does not include dividends and interest removed. It does include the amount by which Portfolio Gains are Removed since that is treated as an expense.

A5900 through BJ5900: Total Withdrawals (nominal).
BN5900 through DW5900: If current amounts (Nominal) below threshold, display 1. Otherwise, zero.
DZ5900 through EF5900: If current amounts (Nominal) fell below threshold, within the number years indicated, display 1. Otherwise, zero.
EI5900 through EO5900: If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.
ER5900 through EV5900: Summary box for TOTAL AMOUNT WITHDRAWN (nominal).

A6100 through BJ6100: Total Withdrawals (real).
BN6100 through DW6100: If the total amount (real) is below threshold, display 1. Otherwise, 0.
DZ6100 through EF6100: If the total amount (real) falls below threshold within the number years indicated, display 1. Otherwise, 0.
EI6100 through EO6100: If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.
ER6100 through EV6100: Summary box for TOTAL AMOUNT WITHDRAWN (real).

A6300 through BJ6300: Expenses+Withdrawals 1+2 (real).

A6500 through BJ6500: Dividends Withdrawn (real).
BN6500 through DW6500: If the dividends withdrawn (real) are below threshold, display 1. Otherwise, 0.
DZ6500 through EF6500: If the dividend withdrawal (real) falls below threshold within the number years indicated, display 1. Otherwise, 0.
EI6500 through EO6500: If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.
ER6500 through EV6500: Summary box for DIVIDENDS WITHDRAWN (real).

A6700 through BJ6700: Interest Withdrawn (real).
BN6700 through DW6700: If the interest withdrawn (real) is below threshold, display 1. Otherwise, 0.
DZ6700 through EF6700: If the interest withdrawn (real) falls below threshold within the number years indicated, display 1. Otherwise, 0.
EI6700 through EO6700: If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.
ER6700 through EV6700: Summary box for INTEREST WITHDRAWN (nominal).

A7000 through BJ7000: Initial Stock Balance (real).
BN7000 through DW7000: If current balances (Real) are below threshold, display 1. Otherwise, 0.
DZ7000 through EF7000: If current balances (Real) fall below threshold within the number years indicated, display 1. Otherwise, 0.
EI7000 through EO7000: If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.
ER7000 through EV7000: Summary box for STOCK BALANCE (real).

A7200 through BJ7200: Initial Bond Balance (real).
BN7200 through DW7200: If current balances (Real) are below threshold, display 1. Otherwise, 0.
DZ7200 through EF7200: If current balances (Real) fall below threshold within the number years indicated, display 1. Otherwise, 0.
EI7200 through EO7200: If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.
ER7200 through EV7200: Summary box for BOND BALANCE (real).

Have fun.

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

Post by JWR1945 »

Deluxe Calculator V1.1A

This is a description of a recent set of modifications to the Retire Early Safe Withdrawal [Rate] Calculator, Version 1.61, 7 November 2002. It adds numerous data analysis and data summary capabilities to the Deluxe Calculator V1.0A.

I have extracted the first and last sections of a lengthy write up. I have left in the headings of the intermediate subjects

Initial Changes

There are new data summary tables in rows 1 through 9 starting at columns V, AB, AH, AN, AT and AZ. Each one of these summarizes the number of start years in which a particular balance has dipped below a specified dollar amount in the first 10, 20, 30, 40, 50 and 60 years. [The beginning and ending start years for the new summary tables are the same as before. That is, they are set in cells M1 and M2 (actually, in fixed locations $M$1 and $M$2).]

The summary in columns V-Z tells how often the PORTFOLIO BALANCE falls below its threshold. You enter the portfolio balance's threshold in cell X1 (actually, in cell $X$1). You choose whether to use real or nominal dollars in cell Z2 (actually, in cell $Z$2).

The summary in columns AB-AF tells how often the TOTAL AMOUNT WITHDRAWN falls below its threshold. You enter threshold for the total amount withdrawn in cell AD1 (actually, in cell $AD$1). You choose whether to use real or nominal dollars in cell AF2 (actually, in cell $AF$2).

The summary in columns AH-AL tells how often the DIVIDENDS WITHDRAWN fall below their threshold. You enter the threshold for dividends withdrawn in cell AJ1 (actually, in cell $AJ$1). You choose whether to use real or nominal dollars in cell AL2 (actually, in cell $AL$2).

The summary in columns AN-AR tells how often the INTEREST WITHDRAWN falls below its threshold. You enter the threshold for the interest withdrawn in cell AP1 (actually, in cell $AP$1). You choose whether to use real or nominal dollars in cell AR2 (actually, in cell $AR$2).

The summary in columns AT-AX tells how often the STOCK BALANCE falls below its threshold. You enter the threshold for the stock balance in cell AV1 (actually, in cell $AV$1). You choose whether to use real or nominal dollars in cell AX2 (actually, in cell $AX$2).

The summary in columns AZ-BD tells how often the BOND BALANCE falls below its threshold. You enter the threshold for the bond balance in cell BB1 (actually, in cell $BB$1). You choose whether to use real or nominal dollars in cell BD2 (actually, in cell $BD$2).

New Tables related to Nominal Balances (near Row 2600)

New Tables for Total Amount Withdrawn in Nominal Dollars

New Tables for Initial Stock Balance and Initial Bond Balance in Nominal Dollars

Dividends Withdrawn and Interest Withdrawn

Investment Expenses

New Tables for Real Balances (near Row 3200)

New copies of tables with Real Balances.

Total Withdrawals in Real Dollars.

Dividends Withdrawn in Real Dollars

Interest Withdrawn in Real Dollars

Stock Balances in Real Dollars

Bond Balances in Real Dollars

Main Data Summary Tables

These are main data summary tables in the first 9 rows.

PORTFOLIO BALANCE in columns V through Z. I wrote =IF($Z$2>1,ES2603,ES3203) in cell W4. I used the fill handle to drag the formula down to cell W9.

[The formulas for cells W4 through W9 are, as follows:
W4: =IF($Z$2>1,ES2603,ES3203)
W5: =IF($Z$2>1,ES2604,ES3204)
W6: =IF($Z$2>1,ES2605,ES3205)
W7: =IF($Z$2>1,ES2606,ES3206)
W8: =IF($Z$2>1,ES2607,ES3207)
W9: =IF($Z$2>1,ES2608,ES3208)]

TOTAL AMOUNT WITHDRAWN in columns AB through AF. I wrote =IF($AF$2>1,ES5901,ES6101) in cell AC4. I used the fill handle to drag the formula down to cell AD9.

DIVIDENDS WITHDRAWN in columns AH through AL. I wrote =IF($AL$2>1,ES5002,ES6502) in cell AI4. I used the fill handle to drag the formula down to cell AI9.

INTEREST WITHDRAWN in columns AN through AR. I wrote =IF($AR$2>1,ES5202,ES6702) in cell AO4. I used the fill handle to drag the formula down to cell AO9.

STOCK BALANCE in columns AT through AX. I wrote =IF($AX$2>1,ES4602,ES7002) in cell AU4. I used the fill handle to drag the formula down to cell AU9.

BOND BALANCE in columns AZ through BD. I wrote =IF($BD$2>1,ES4802,ES7202)
in cell BA4. I used the fill handle to drag the formula down to cell BA9.

Have fun.

John R.
Last edited by JWR1945 on Sun Aug 01, 2004 4:46 am, edited 1 time in total.
JWR1945
***** Legend
Posts: 1697
Joined: Tue Nov 26, 2002 3:59 am
Location: Crestview, Florida

Post by JWR1945 »

Deluxe Calculator V1.1A

This is a description of a recent set of modifications to the Retire Early Safe Withdrawal [Rate] Calculator, Version 1.61, 7 November 2002. It adds numerous data analysis and data summary capabilities to the Deluxe Calculator V1.0A.

This is the full twelve pages.

Initial Changes

There are new data summary tables in rows 1 through 9 starting at columns V, AB, AH, AN, AT and AZ. Each one of these summarizes the number of start years in which a particular balance has dipped below a specified dollar amount in the first 10, 20, 30, 40, 50 and 60 years. [The beginning and ending start years for the new summary tables are the same as before. That is, they are set in cells M1 and M2 (actually, in fixed locations $M$1 and $M$2).]

The summary in columns V-Z tells how often the PORTFOLIO BALANCE falls below its threshold. You enter the portfolio balance's threshold in cell X1 (actually, in cell $X$1). You choose whether to use real or nominal dollars in cell Z2 (actually, in cell $Z$2).

The summary in columns AB-AF tells how often the TOTAL AMOUNT WITHDRAWN falls below its threshold. You enter threshold for the total amount withdrawn in cell AD1 (actually, in cell $AD$1). You choose whether to use real or nominal dollars in cell AF2 (actually, in cell $AF$2).

The summary in columns AH-AL tells how often the DIVIDENDS WITHDRAWN fall below their threshold. You enter the threshold for dividends withdrawn in cell AJ1 (actually, in cell $AJ$1). You choose whether to use real or nominal dollars in cell AL2 (actually, in cell $AL$2).

The summary in columns AN-AR tells how often the INTEREST WITHDRAWN falls below its threshold. You enter the threshold for the interest withdrawn in cell AP1 (actually, in cell $AP$1). You choose whether to use real or nominal dollars in cell AR2 (actually, in cell $AR$2).

The summary in columns AT-AX tells how often the STOCK BALANCE falls below its threshold. You enter the threshold for the stock balance in cell AV1 (actually, in cell $AV$1). You choose whether to use real or nominal dollars in cell AX2 (actually, in cell $AX$2).

The summary in columns AZ-BD tells how often the BOND BALANCE falls below its threshold. You enter the threshold for the bond balance in cell BB1 (actually, in cell $BB$1). You choose whether to use real or nominal dollars in cell BD2 (actually, in cell $BD$2).

New Tables related to Nominal Balances (near Row 2600)

I wrote If current balances (Nominal) below threshold, display 1. Otherwise, zero. into cell BN2599.

I copied the table of nominal balances in the rectangle of cells defined by A2370 and BJ2599 onto a rectangle of cells defined by BN2370 and DW2599. I wrote this formula into cell BO2601: =IF(B2601<$X$1,1,0). I used the fill handle to drag the formula all of the way to the right to cell DW2601. Then I used the fill handle to drag the formula for each column down to row 2370. Finally, I edited the length of each column.

The logic indicates that the portfolio's balance is below threshold. The logic returns a value of 1 when there is no data. It is necessary to eliminate such conditions. Otherwise, every sequence that extends beyond 2010 (which is the last year with dummy values for data) is counted as a failure.

To edit, I set the withdrawal rate and the expenses to zero. I set the threshold equal to $1. I cleared the formula from all cells that indicated portfolio failure (i.e., that had a value of 1). I used the fill handle to do this, starting from a blank cell outside of the table.

As a check, I set the initial balance to $100000 and the withdrawal rate to 20%. I set the threshold at $500000. Sure enough, there was a 1 in every cell in the table.

I copied the rectangle defined by cells BT2730 and BN2599 onto a rectangle defined by DZ2730 and EF2599.

I wrote into cell DZ2599: If current balances (Nominal) fell below threshold, within the number years indicated, display 1. Otherwise, zero.

I wrote 10 years into cell EA2600.
I wrote 20 years into cell EB2600.
I wrote 30 years into cell EC2600.
I wrote 40 years into cell ED2600.
I wrote 50 years into cell EE2600.
I wrote 60 years into cell EF2600.

I wrote =IF(SUM(BO2601:BY2601)>0,1,0) into cell EA2601. I used the fill handle to drag the formula all of the way down to cell EA2730.

I wrote =IF(SUM(BO2601:CI2601)>0,1,0) into cell EB2601. I used the fill handle to drag the formula all of the way down to cell EB2730.

I wrote =IF(SUM(BO2601:CS2601)>0,1,0) into cell EC2601. I used the fill handle to drag the formula all of the way down to cell EC2730.

I wrote =IF(SUM(BO2601:DC2601)>0,1,0) into cell ED2601. I used the fill handle to drag the formula all of the way down to cell ED2730.

I wrote =IF(SUM(BO2601:DM2601)>0,1,0) into cell EE2601. I used the fill handle to drag the formula all of the way down to cell EE2730.

I wrote =IF(SUM(BO2601:DW2601)>0,1,0) into cell EF2601. I used the fill handle to drag the formula all of the way down to cell EF2730.

I copied the rectangle defined by cells DZ2599 and EF2730 onto the area defined by cells EI2599 and EO2730. This took 132Rx7C.

I wrote =IF(AND($EI2601>=$M$1,$EI2601<=$M$2,EB2601>0),1,0) into cell EJ2601. I used the fill handle to drag the formula all of the way across (to cell EO2601). I used the fill handle to drag the formula all the way down (to row 2730, which is for 2000) for each of the columns (starting with cells EJ2601, EK2601, EL2601, EM2601, EN2601 and EO2601.

In cell EI2599, I wrote If within the years defined by $M$1and $M$2 and if failed, display 1. Otherwise, display 0.

[I wrote =$M$2-$M$1+1 into cell M3. It had been present in earlier versions. I seem to have overwritten in Deluxe Calculator V1.0A, JanSz-Chips Deluxe Version 1.0B and JanSz-Chips Deluxe Version 1.0C. All of the Versions 2.0 and 3.0 were OK.]

I copied the data summary table for PORTFOLIO BALANCES from the area defined by cells V1 and Z9. I pasted them onto the area defined by cells ER2600 and EV2608.

I wrote =SUM(EJ2601:EJ2730) into cell ES2603.
I wrote =SUM(EK2601:EK2730) into cell ES2604.
I wrote =SUM(EL2601:EL2730) into cell ES2605.
I wrote =SUM(EM2601:EM2730) into cell ES2606.
I wrote =SUM(EN2601:EN2730) into cell ES2607.
I wrote =SUM(EO2601:EO2730) into cell ES2608.

I wrote =$X$1 into cell ET2600.

I wrote 2 into cell EV2601.

New Tables for Total Amount Withdrawn in Nominal Dollars

I copied the rectangle defined by cells BN2730 and EV2599 onto the area defined by cells BN6028 and EV5897. It took up 132Rx87C.

I wrote 0 into cell BO5899. I dragged it all the way down (to cell BO6028) with the fill handle. Column BO is for year zero. We assign no failures in year zero.

I changed the formula in cell BP5899 to =IF(C5899<$AD$1,1,0). I used the fill handle to drag it all the way to cell DW5899. I used the fill handle to drag the formula from each column in row 5899 from BP5899 through DW5899 all the way down (to cells BP6028 through DW6028). I took care not to alter any of the column lengths.

I typed TOTAL AMOUNT WITHDRAWN into cell ET5900.

I typed =$AD$1 into cell ET5898.

Thanks to relative addressing, everything else was in place. All of the formulas were correct.

I checked out this new area carefully.

I set the initial balance in cell B4 equal to $100000. I set the withdrawal rate in cell B9 equal to 4.000%. I set the expenses to 0%. I set B14 equal to 2 to prevent any adjustments for inflation.

I put a withdrawal amount of $4000 into cell AD1. All of the (Nominal) Total Withdrawals in the table with values starting at cell C5899 were identically equal to $4000. I typed $3950 into cell AD1 and discovered that I had to adjust the rows and columns related to withdrawals.

I edited the table on (Nominal) Annual Withdrawals (individually for withdrawals 1 and 2). The first pair of changes occurred in rows 4460 and 4461 for 1951. They end in cell BI4460 and BI4461, respectively, referencing cell locations EK1640 and EK1653.

I proceeded to drag pairs of formulas to the right until they ended referencing column EK. It is very important not to drag formulas up or down since the referenced row numbers will be wrong.

I found a series of editing errors that needed corrections.

I had already edited the (Nominal) Initial Stock Balances with values starting in cell C4600. I edited the (Nominal) Initial Bond Balances with values starting in cell C4800. I proceeded to edit the table on (Nominal) Investment Expenses (for Expenses + Withdrawals 1 and Expenses + Withdrawals 2) with values starting in cell C5400. The procedure was the similar although it is OK to drag the fill handle downward. I eliminated all ending balances of zero (which meant that there were references to blank cells). All of the cells with balances showed (2000) because the withdrawal rate was 4% on an initial balance of $100000 (and there are both a front-end withdrawal and a back-end withdrawal). I found it necessary to edit the (Nominal) Expenses + Withdrawals 1 + 2 table with values beginning in cell C5700. Finally, I edited the (Nominal) Total Withdrawals table with values beginning in cell C5899.

I changed some titles.

I wrote If current amounts (Nominal) below threshold, display 1. Otherwise, zero. in cell BN5897. I wrote If current amounts (Nominal) fell below threshold, within the number years indicated, display 1. Otherwise, zero. in cell DZ5897.

I resumed checking for errors.

I changed the amount in cell AD1 to $4050.

I verified all tables that included standard withdrawals.

I verified that the tables with (Nominal) Total Withdrawals were correct (from row 5899). I verified that column BO had zeroes in it but that all other entries from BP through DW were equal to one. I verified the correct operation of the summary tables in columns DZ through EF and in columns EI through EO. I verified the proper operation of the summary box in columns ER through EV (and rows 5898 through 5906).

[I temporarily wrote =IF($AF$2>1,ES5901,ES3203) into cell AC4 and used the fill handle to drag the formula down to cell AC9. This displays the contents of the (Nominal) Total Amount Withdrawn correctly when cell AF2 has the number 2 in it. With the number 1 in cell AF2, it temporarily displayed the real Portfolio Balance results. Later, I replaced the temporary code.]

New Tables for Initial Stock Balance and Initial Bond Balance in Nominal Dollars

I copied the area defined by BN2730 and EF2599 onto the area defined by BN4729 and EF4598. This takes up 132Rx71C. [This is because of the sequence that I actually followed. Ideally, I would have included columns all the way to EV in this step.]

It is necessary to eliminate the calculations for year 0 in column BO. There are no initial stock balances for year 0. I wrote 0 into cell BO4600 and I used the fill handle to drag it down to cell BO4729.

I changed cell BP4600 to =IF(C4600<$AV$1,1,0). The threshold for the Initial Stock Balance is in cell AV1 (actually, in cell $AV$1). I used the fill handle to drag the new formula to cell DW4600. Then I used the fill handle to drag the formula down from row 4600 for all of the years from N = 1 through N = 60. I was careful not to change the lengths of the columns. Later, I found that it was necessary to edit the column lengths in the basic table in rows A through BJ after all. When editing it is critically important to drag formulas from left to right. They should not be dragged down. The referenced rows jump when one moves down in this table.

I copied the area defined by EI2730 and EV2599 onto the area defined by EI4729 and EV4598. This takes 132Rx14C. I wrote =$AV$1 in cell ET4599. I wrote STOCK BALANCE in cell ET4601. I wrote 2 in cell EV4600.

I repeated the process for the (Nominal) Initial Bond Balance table, which has value starting from cell C4800. I copied the area defined by BN4729 and EF4598 to the area defined by BN4929 and EF4798. This takes up 132Rx71C.

The change for column BO had carried over from copying the (Nominal) Initial Stock Balance table. I changed BP4800 to =IF(C4800<$BB$1,1,0) since the threshold for the Initial Bond Balance is in cell BB1 (actually, in fixed location $BB$1). I used the fill handle to drag the formula to the right to cell DW4800. Then I used the fill handle to drag the formula down from row 4800 for all of the years from N = 1 through N = 60. I was careful not to change the lengths of the columns. The previous editing of the stock tables helped.

I copied the area defined by EI2730 and EV2599 onto the area defined by EI4929 and EV4798. This takes 132Rx14C. I wrote =$BB$1 in cell ET4799. I wrote BOND BALANCE in cell ET4801. I wrote 2 in cell EV4800.

Dividends Withdrawn and Interest Withdrawn

I set the threshold for TOTAL AMOUNT WITHDRAWN in cell AD1 to $2000. I set the Initial Withdrawal Rate in cell B9 equal to 0%. I left the Initial Balance in cell B4 equal to $100000. I set the Dividends Reinvested in cell B22 equal to 20%. I set the Interest Reinvested in cell B23 equal to 70%. I left the Percentage Gains Removed in cell B17 at 0%. I set the expenses in cell B15 equal to 0%.

I edited the (Nominal) Dividends Withdrawn table, which has values starting in cell C5000 as with the earlier tables. I dragged formulas to the right using the fill handle although it is OK to drag formulas down in this table.

I copied the rectangle defined by cells EF4798 and BN4929 onto the area defined by EF4998 and BN5129. This takes 132Rx71C. I changed cell BP5000 to =IF(C5000<$AJ$1,1,0). I used the fill handle to drag this formula all of the way to the right to cell DW5000. Then I used it to drag the formula all the way down from row 5000 to every column from BP through DW (to BP5129 through DW5129). I was careful not to change the lengths of the columns. The previous editing (of the Nominal Initial Bond Balances table) helped.

I changed cell BN4998 to If current dividend withdrawals (Nominal) are below threshold, display 1. Otherwise, 0.

I changed cell DZ4998 to If dividend withdrawals (Nominal) fell below threshold within the number years indicated, display 1. Otherwise, 0.

I copied the rectangle defined by EI6028 and EV5897 to the area of EI5129 and EV4998. I wrote =$AJ$1 into cell ET4999. I wrote DIVIDENDS WITHDRAWN into cell ET5001. I wrote 2 into cell EV5000. Thanks to relative addressing, everything else was correct.

I edited the (Nominal) Interest Withdrawn table, which has values starting in cell C5200. I dragged formulas to the right using the fill handle.

I verified that everything was OK by looking at the (Nominal) Total Withdrawals area, which has values starting in cell C5899.

I copied the rectangle defined by cells EF4998 and BN5129 onto the area defined by EF5198 and BN5329. This takes 132Rx71C. I changed cell BP5200 to =IF(C5200<$AP$1,1,0). I used the fill handle to drag the formula all the way to the right to cell DW5200. Then I used it to drag the formula all the way down from row 5200 to every column from BP through DW (to BP5329 through DW5279). I was careful not to change the lengths of the columns. The previous editing (of the Nominal Initial Bond Balances table) helped.

I changed cell BN5198 to If interest withdrawals (Nominal) are below threshold, display 1. Otherwise, 0.

I changed cell DZ5198 to If interest withdrawals (Nominal) fell below threshold within the number years indicated, display 1. Otherwise, 0.

I copied the rectangle defined by EI6028 and EV5897 to the area of EI5329 and EV5198. I wrote =$AP$1 into cell ET5199. I wrote INTEREST WITHDRAWN into cell ET5201. I wrote 2 into cell EV5200. Thanks to relative addressing, everything else was correct.

Investment Expenses

I debugged the (Nominal) Investment Expenses table.

I set the threshold for TOTAL AMOUNT WITHDRAWN in cell AD1 to $2000. I set the Initial Withdrawal Rate in cell B9 equal to 0%. I left the Initial Balance in cell B4 equal to $100000. I set the Dividends Reinvested in cell B22 equal to 90%. I set the Interest Reinvested in cell B23 equal to 95%. I left the Percentage Gains Removed in cell B17 at 0%. I set the expenses in cell B15 equal to 6%.

I checked the table on (Nominal) Investment Expenses, which has values starting in cell C4000. All of the front-end Investment Expenses 1 in column C were $3000, which is correct. The back-end Investment Expenses 2 in column C varied, which is the effect of portfolio gains and losses during the year.

I found it necessary to edit the (Nominal) Investment Expenses table. It is necessary to drag the fill handle to the right and not down. You must fill out the rows in pairs.

I examined the data in the table on (Nominal) Total Withdrawals, which has values starting in cell 5899. I changed the threshold in cell AD1 to $5000 and checked again, making note of the number of failures in cells AC4 through AC9.

I set the threshold for TOTAL AMOUNT WITHDRAWN in cell AD1 to $3000. I set the Initial Withdrawal Rate in cell B9 equal to 2%. I left the Initial Balance in cell B4 equal to $100000. I set the Dividends Reinvested in cell B22 equal to 100%. I set the Interest Reinvested in cell B23 equal to 100%. I set the Percentage Gains Removed in cell B17 at 80%. I set the expenses in cell B15 equal to 0%.

In the process I found two bugs in my previous coding of the Percentage Gains Removed algorithms. In one case I compared one value from row 325 with another on row 235. In the other case, I had failed to include the "1-" portion of *(1-$B$10). The affected start years were 1878 and 1935.

I noticed that the Percentage Gains Removed feature behaves uniquely. It is included as part of the expenses.

It turns out that the TOTAL AMOUNT WITHDRAWN has either 0% failures or 100% failures when withdrawals consist only of the Percentage Gains Removed and a standard withdrawal. In fact, setting AD1 equal to $2000 resulted in zero failures. Setting AD1 equal to $2001 caused 60 failures in the years 1921-1980. The all or nothing aspect of removing a percentage of gains causes this behavior.

New Tables for Real Balances (near Row 3200)

I copied the rectangle defined by cells BN2730 and EV2599 onto the area defined by BN3330 and EV3199.

Fortunately, the calculations and logic of both sets are identical. The new tables require no changes except for titles, thanks to relative addressing.

I changed the contents of BN3199 to If current balances (Real) below threshold, display 1. Otherwise, zero.. I changed the contents of cell DZ3199 to If current balances (Real) fell below threshold, within the number years indicated, display 1. Otherwise, zero.

I typed 1 into cell EV3201.

New copies of tables with Real Balances.

I copied the rectangle defined by cells BN6028 and EV5897 [for Total Withdrawals (Nominal)] onto the area defined by cells BN6228 and EV6097 for the Total Withdrawals (Real).

I copied the rectangle defined by cells BN5129 and EV4998 [for Dividends Withdrawn (Nominal)] onto the area defined by cells BN6629 and EV6498 for the Dividends Withdrawn (Real).

I copied the rectangle defined by cells BN5329 and EV5198 [for Interest Withdrawn (Nominal)] onto the area defined by cells BN6829 and EV6698 for the Interest Withdrawn (Real).

Total Withdrawals in Real Dollars

I edited the table with values beginning in cell C6099 so that all of the lengths were correct.

I typed If the total amount (real) is below threshold, display 1. Otherwise, 0. into cell BN6097.

I typed If the total amount (real) falls below threshold within the number years indicated, display 1. Otherwise, 0. into cell DZ6097.

I wrote =$AD$1 into cell ET6098. I wrote TOTAL AMOUNT WITHDRAWN in cell ET6100. I wrote 1 in cell EV6099.

[Actually, the =$AD$1 was already in cell ET6098 because I had copied the nominal dollar version of the same tables and summary.]

Thanks to relative addressing and my previous editing, this was sufficient.

Dividends Withdrawn in Real Dollars

I edited the table with values beginning in cell C6500.

I wrote If the dividends withdrawn (real) are below threshold, display 1. Otherwise, 0. in cell BN6498.

I wrote If the dividend withdrawal (real) falls below threshold within the number years indicated, display 1. Otherwise, 0. into cell DZ6498.

The summary table already had =$AJ$1 in cell ET6499 and DIVIDENDS WITHDRAWN[/b] in cell EV6501 because I had copied from the Nominal Dividends Withdrawn area. I wrote 1 into cell EV6500.

Thanks to relative addressing and my previous editing, this was sufficient.

Interest Withdrawn in Real Dollars

I edited the table with values beginning in cell C6700.

I wrote If the interest withdrawn (real) is below threshold, display 1. Otherwise, 0. in cell BN6698.

I wrote If the interest withdrawn (real) falls below threshold within the number years indicated, display 1. Otherwise, 0 in cell DZ6698.

The summary table already had =$AP$1 in cell ET6699 and INTEREST WITHDRAWN[/b] in cell EV6701 because I had copied from the Nominal Interest Withdrawn area. I wrote 1 into cell EV6700.

Thanks to relative addressing and my previous editing, this was sufficient.

Stock Balances in Real Dollars

Both the front-end withdrawal and the front-end expenses are removed before calculating the initial stock balance.

I copied the Initial Stock Balance (Nominal) tables in the rectangle defined by cells A4729 and EV4598 onto the area defined by cells A7129 and EV6998. This takes 132Cx152R.

I wrote =C4600*C3001/$C3001 into cell C7000. I used the fill handle to drag the formula all of the way to the right (to cell BN7000). Then I used the fill handle to drag each formula down, being careful to maintain column lengths.

I wrote Real Dollars into cell A6998.

I wrote If current balances (Real) are below threshold, display 1. Otherwise, 0. in cell BN6998.

I wrote If current balances (Real) fall below threshold within the number years indicated, display 1. Otherwise, 0. in cell DZ6998.

I wrote 1 in cell EV7000.

Otherwise, no further changes were required. This is because of relative addressing and the fact that I had copied from the Initial Stock Balance (Nominal) area.

Bond Balances in Real Dollars

Both the front-end withdrawal and the front-end expenses are removed before calculating the initial bond balance.

I copied the Initial Bond Balance (Nominal) tables in the rectangle defined by cells A4929 and EV4798 onto the area defined by cells A7329 and EV7198. This takes 132Cx152R.

I wrote =C4800*C3001/$C3001 into cell C7000. I used the fill handle to drag the formula all of the way to the right (to cell BN7200). Then I used the fill handle to drag each formula down, being careful to maintain column lengths.

I wrote Real Dollars into cell A7198.

I wrote If current balances (Real) are below threshold, display 1. Otherwise, 0. in cell BN7198.

I wrote If current balances (Real) fall below threshold within the number years indicated, display 1. Otherwise, 0. in cell DZ7198.

I wrote 1 in cell EV7200.

Otherwise, no further changes were required. This is because of relative addressing and the fact that I had copied from the Initial Bond Balance (Nominal) area.

Main Data Summary Tables

These are main data summary tables in the first 9 rows.

PORTFOLIO BALANCE in columns V through Z. I wrote =IF($Z$2>1,ES2603,ES3203) in cell W4. I used the fill handle to drag the formula down to cell W9.

[The formulas for cells W4 through W9 are, as follows:
W4: =IF($Z$2>1,ES2603,ES3203)
W5: =IF($Z$2>1,ES2604,ES3204)
W6: =IF($Z$2>1,ES2605,ES3205)
W7: =IF($Z$2>1,ES2606,ES3206)
W8: =IF($Z$2>1,ES2607,ES3207)
W9: =IF($Z$2>1,ES2608,ES3208)]

TOTAL AMOUNT WITHDRAWN in columns AB through AF. I wrote =IF($AF$2>1,ES5901,ES6101) in cell AC4. I used the fill handle to drag the formula down to cell AD9.

DIVIDENDS WITHDRAWN in columns AH through AL. I wrote =IF($AL$2>1,ES5002,ES6502) in cell AI4. I used the fill handle to drag the formula down to cell AI9.

INTEREST WITHDRAWN in columns AN through AR. I wrote =IF($AR$2>1,ES5202,ES6702) in cell AO4. I used the fill handle to drag the formula down to cell AO9.

STOCK BALANCE in columns AT through AX. I wrote =IF($AX$2>1,ES4602,ES7002) in cell AU4. I used the fill handle to drag the formula down to cell AU9.

BOND BALANCE in columns AZ through BD. I wrote =IF($BD$2>1,ES4802,ES7202)
in cell BA4. I used the fill handle to drag the formula down to cell BA9.

Have fun.

John R.
Post Reply