JWR1945 ***** Legend
Joined: 26 Nov 2002 Posts: 1697 Location: Crestview, Florida

Posted: Wed Jan 14, 2004 2:01 pm Post subject: Deluxe Versions of the Calculators 


Deluxe Versions of the Calculators
This is my latest modification to the Retire Early Safe Withdrawal [Rate] Calculator, Version 1.61, 7 November 2002. I refer to these as Deluxe Versions. They calculate annualized returns.
As always, these latest changes may be made on any or all of the previously modified calculators. Nothing is lost. Nothing is destroyed.
These calculators can do a whole lot of things. I refer to these updates as the JanSzChips Deluxe Versions. By Deluxe, I mean that they calculate annualized returns. By making a reference to JanSz, I mean that we can increase withdrawals by a percentage of the portfolio's gain from six years earlier, provided only that the balance has increased. When I mention Chips, I mean that we can specify a percentage of dividends to reinvest.
All of the Deluxe versions are upgrades to my Special Versions. My Special Versions incorporate the full switching capabilities (two thresholds, three allocations) with stocks and commercial paper or TIPS or ibonds. I have included all of my data reduction aids in the Special Versions as well.
Description of the Modification
In this modification, I convert the existing Data Summaries in the T15:AK144 area into annualized percentage returns and annualized gain multipliers. This area lists both the nominal and real portfolio balances at 10, 20, 30, 40, 50 and 60 years.
To convert to annualized gain multipliers after N years, one first divides the current balance by the initial balance. Then he takes the Nth root. I do this by using the POWER function in the form of POWER(X,1/N). It could also be written as X^(1/N).
I place two special conditions on X. If the current balance is negative, I set the gain multiplier to zero. I also require that the initial balance be positive. Otherwise, I set the gain multiplier equal to zero. Excluding these special cases, X is the current balance at N years divided by the initial balance, where N=10, 20, 30, 40, 50 or 60.
The annualized percentage return is 100%*(the gain multiplier)  100%.
The new tables appear in the CN12:DL144 area of the spreadsheet.
Initial Steps
Open the calculator that you wish to modify and copy it.
In this case, I opened the JanSzChips Special V1.0 calculator. I clicked on the gray block just above row 1 and to the left of column A. That is the Excel equivalent to a Select All button. I clicked Edit and then Copy. I opened a new, blank spreadsheet. I clicked Edit and then Paste. Then I clicked on an empty cell to complete the Paste operation. (I clicked on cell B1.)
Close the original version of the calculator. [I minimized the new copy. Then I clicked in the formula area of the original spreadsheet. Then I clicked on an empty cell (B1). And finally, I closed the old spreadsheet.]
I named and saved the new calculator at this point. I named it RESWR JanSzChips Deluxe V1.0. The spreadsheet that I copied was RESWR JanSzChips Special V1.0. I have retained that version.
In the new spreadsheet, highlight (i.e., click on) cell E1. Replace JanSzChips Special V1.0 with JanSzChips Deluxe V1.0. I used the formula area and clicked on the checkmark.
Data Locations
Start years are located in cells $S14: $S144. That is, references are always to column S, but the rows vary between row 14 and row 144.
The initial balance is always in cell B4. This is written as $B$4 so that all references to it remain fixed at that specific location.
These are the locations of current balances in the Data Summary area:
The nominal balances at 10 years are in $T15:$T144.
The nominal balances at 20 years are in $W15:$W134.
The nominal balances at 30 years are in $Z15:$Z124.
The nominal balances at 40 years are in $AC15:$AC114.
The nominal balances at 50 years are in $AF15:$AF104.
The nominal balances at 60 years are in $AI15:$AI94.
The real balances at 10 years are in $V15:$V144.
The real balances at 20 years are in $Y15:$Y134.
The real balances at 30 years are in $AB15:$AB124.
The real balances at 40 years are in $AE15:$AE114.
The real balances at 50 years are in $AH15:$AH104.
The real balances at 60 years are in $AK15:$AK94.
Start Years
In cell CN12, write Year and press Enter.
In cell CN13, write Starting and press Enter.
Highlight (i.e., click on) cell S15. Press the Shift key and hold it down. Scroll to cell S144 and click on it. Release the Shift key.
Copy and Paste this into cells CN15:CN144. (That is, click Edit, then Copy. Highlight cell CN15. Press the Shift key and hold it down. Scroll down to row 144. Click on cell CN144. Release the Shift key. Click Edit, then Paste. Finally, click on an empty cell.)
Annualized Percentage GainsNominal
Write Annualized Percentage Gains (Nominal) into cells CO11 and CR11. Click the checkmark with each entry.
Write 10 Year into cell CO13. Press the Tab key.
Write 20 Year into cell CP13. Press the Tab key.
Write 30 Year into cell CQ13. Press the Tab key.
Write 40 Year into cell CR13. Press the Tab key.
Write 50 Year into cell CS13. Press the Tab key.
Write 60 Year into cell CT13. Click on the check mark (or press Enter).
Highlight cell CO15. In the formula area, write:
=POWER(IF($T15<0,0,$T15)*IF($B$4>0,1/$B$4,0),1/10)*100100
Click the checkmark.
Highlight cell CO15. Locate the fill handle at the lower right hand corner. When the cursor has changed from a fat plus sign to narrow crosshairs, click down on the mouse button and hold it down. Drag the cursor down to cell CO144. Release the mouse button and then click on any cell.
This will copy the formula into the other rows of column CO. It changes the cell references unless there is a dollar sign. For example, in cell CO39, the reference from $T15 becomes $T39 (that is, the formula uses the data in cell T39). The $ sign holds the reference to column T. The lack of a $ sign changes the row reference to row 39.
Continue entering this formula by changing the cell references and the number of years. I found it helpful first to copy the formula in cell CO15 to the right from CO15 through CT15 and then modifying the formulas in cells CP15, CQ15, CR15, CS15, CT15 and CT15. Finally, I used the fill handle to drag each new formula down its respective column.
In cell CP15, write the formula:
=POWER(IF($W15<0,0,$W15)*IF($B$4>0,1/$B$4,0),1/20)*100100 and click the checkmark. Highlight cell CP15. Use the fill handle to drag the formula down to cell CP134. (The summary data for 20 years extends only to row 134.)
In cell CQ15, write the formula:
=POWER(IF($Z15<0,0,$Z15)*IF($B$4>0,1/$B$4,0),1/30)*100100 and click the checkmark. Highlight cell CQ15. Use the fill handle to drag the formula down to cell CQ124. (The summary data for 30 years extends only to row 124.)
In cell CR15, write the formula:
=POWER(IF($AC15<0,0,$AC15)*IF($B$4>0,1/$B$4,0),1/40)*100100 and click the checkmark. Highlight cell CR15. Use the fill handle to drag the formula down to cell CR114. (The summary data for 40 years extends only to row 114.)
In cell CS15, write the formula:
=POWER(IF($AF15<0,0,$AF15)*IF($B$4>0,1/$B$4,0),1/50)*100100 and click the checkmark. Highlight cell CS15. Use the fill handle to drag the formula down to cell CS104. (The summary data for 50 years extends only to row 104.)
In cell CT15, write the formula:
=POWER(IF($AI15<0,0,$AI15)*IF($B$4>0,1/$B$4,0),1/60)*100100 and click the checkmark. Highlight cell CT15. Use the fill handle to drag the formula down to cell CT94. (The summary data for 60 years extends only to row 94.)
Annualized Percentage Gains (Real)
Highlight cell CX11 and write Annualized Percentage Gains (Real) and click the checkmark. Highlight cell DA11 and write the same thing and click the checkmark.
In cell CX15, use the formula from cell CO15. Change the formula to use $V15 instead of $T15. This occurs in two places. Cell CX15 should have this formula:
=POWER(IF($V15<0,0,$V15)*IF($B$4>0,1/$B$4,0),1/10)*100100
Highlight cell CX15, locate the fill handle and use it to drag the formula down to cell CX144. Click on any cell to complete the operation. (I clicked on cell CX144.)
In cell CY15, use the formula from cell CO15. Change the formula to use $Y15 instead of $T15. This occurs in two places. Change the 10 (years) to 20. Cell CY15 should have this formula:
=POWER(IF($Y15<0,0,$Y15)*IF($B$4>0,1/$B$4,0),1/20)*100100
Highlight cell CY15, locate the fill handle and use it to drag the formula down to cell CY134. Click on any cell to complete the operation. (I clicked on cell CY134.)
In cell CZ15, use the formula from cell CO15. Change the formula to use $AB15 instead of $T15. This occurs in two places. Change the 10 (years) to 30. Cell CZ15 should have this formula:
=POWER(IF($AB15<0,0,$AB15)*IF($B$4>0,1/$B$4,0),1/30)*100100
Highlight cell CZ15, locate the fill handle and use it to drag the formula down to cell CZ124. Click on any cell to complete the operation. (I clicked on cell CZ124.)
In cell DA15, use the formula from cell CO15. Change the formula to use $AE15 instead of $T15. This occurs in two places. Change the 10 (years) to 40. Cell DA15 should have this formula:
=POWER(IF($AE15<0,0,$AE15)*IF($B$4>0,1/$B$4,0),1/40)*100100
Highlight cell DA15, locate the fill handle and use it to drag the formula down to cell DA114. Click on any cell to complete the operation. (I clicked on cell DA114.)
In cell DB15, use the formula from cell CO15. Change the formula to use $AH15 instead of $T15. This occurs in two places. Change the 10 (years) to 50. Cell DB15 should have this formula:
=POWER(IF($AH15<0,0,$AH15)*IF($B$4>0,1/$B$4,0),1/50)*100100
Highlight cell DB15, locate the fill handle and use it to drag the formula down to cell DB104. Click on any cell to complete the operation. (I clicked on cell DB104.)
In cell DC15, use the formula from cell CO15. Change the formula to use $AK15 instead of $T15. This occurs in two places. Change the 10 (years) to 60. Cell DC15 should have this formula:
=POWER(IF($AK15<0,0,$AK15)*IF($B$4>0,1/$B$4,0),1/60)*100100
Highlight cell DC15, locate the fill handle and use it to drag the formula down to cell DC94. Click on any cell to complete the operation. (I clicked on cell DC94.)
Annualized Gain MultipliersNominal
Repeat the steps used in the Annualized Percentage GainsNominal section with the following changes:
1) Make use of the area from DG11 through DL144 (instead of CO11 through CT144).
2) When entering each formula into row 15, delete the *100100 part of the formula. For example, the formula in cell DG15 should be changed to:
=POWER(IF($T15<0,0,$T15)*IF($B$4>0,1/$B$4,0),1/10)
instead of
=POWER(IF($T15<0,0,$T15)*IF($B$4>0,1/$B$4,0),1/10)*100100
3) Use the fill handle to drag down the new formulas for each column. The columns will end in rows 144, 134, 124, 114, 104 and 94 respectively, corresponding to locations in the summary data.
Annualized Gain MultipliersReal
Repeat the steps used in the Annualized Percentage GainsNominal section with the following changes:
1) Make use of the area from DP11 through DU144 (instead of CX11 through DC144).
2) When entering each formula into row 15, delete the *100100 part of the formula. For example, the formula in cell DP15 should be changed to: =POWER(IF($V15<0,0,$V15)*IF($B$4>0,1/$B$4,0),1/10)
instead of
[b]=POWER(IF($V15<0,0,$V15)*IF($B$4>0,1/$B$4,0),1/10)*100100[/b]
3) Use the fill handle to drag down the new formulas for each column. The columns will end in rows 144, 134, 124, 114, 104 and 94 respectively, corresponding to locations in the summary data.
Special Notes
When using the JanSzChips Deluxe V1.0 calculator, be sure to set the dividend reinvestments to 100% under normal circumstances. In addition, set the Capital Gains Percentage, which is a withdrawal percentage, equal to 0% under normal circumstances.
Once you have made these changes and checked them out, install them on your other calculators. In my case, I have chosen to retain all of my previous Special Versions in their original form and I have added Deluxe Versions that have these modifications. There are separate versions for switching when the nonstock component consists of commercial paper, TIPS or ibonds.
Applications
If you set the withdrawal rate equal to zero, you can determine your portfolio's annualized return over time. You can compare these results with different stock allocations and with different portfolio allocation switching algorithms.
If you set the Dividends Reinvestments to 0%, you can read out the market's annualized returns from caused by price changes alone. You can do this with different stock allocations and with different switching algorithms.
You can vary the expense ratio. By including portfolio expenses, you can introduce greater realism.
By choosing conditions carefully, you can make a crude estimate of the effects of taxes on your portfolio's performance. [Capital gains are treated separately from dividends. That is the key feature for taxes. However, all withdrawals from portfolio growth are made by comparing the portfolio's current balance with that from 6 years earlier. That limits the applicability of capital gains comparisons.]
You can set various withdrawal rates and determine how the resulting portfolio compares with to a portfolio without withdrawals.
Have fun.
John R.

