Deluxe Calculator V1.0A

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.0A

Post by JWR1945 »

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 uses the JanSz-Chips Deluxe Version 1.0C Calculator as a starting point.

The Deluxe Calculator V1.0A changes the algorithm for withdrawing capital gains. Instead of comparing portfolio balances from six years earlier, the new algorithm looks at the previous (single) year's portfolio balance. If there has been an increase, it removes a percentage of that increase in accordance with the contents of cell B17 (more precisely, $B$17), which has the Percentage Gains Removed. If there has been a decrease, the balance is left untouched.

Changes

The front-end in the 1871 sequence is in cell C201. Its formula is =IF(C199<=0,0,-$B$15*C199)*$B$10, where $B$10 is the fraction of expenses applied in January and $B$15 is the expense ratio. The back-end expenses are in cell C214. Its formula is =IF(C212<=0,0,-$B$15*C212)*(1-$B$10).

The new algorithm is applied first in cell D201. This includes the front-end special withdrawal amount. Its formula is =IF(D199<=0,0,-$B$15*D199)*$B$10+ IF(D199<=C199,0,-$B$17*(D199-C199))*$B$10. Cells C199 and D199 have the balances at the beginning of 1872 and 1873, respectively. The contents of cell $B$17 is the fraction of any increase that is withdrawn. Notice that there is no additional withdrawal if the balance decreases. The back-end special withdrawal is in cell D214. Its formula is =IF(D212<=0,0,-$B$15*D212)*(1-$B$10)+ IF(D199<=C199,0,-$B$17*(D199-C199))*(1-$B$10). Once again, I compare the balances from 01/01 in cells C199 and D199.

I used the fill handle to drag the formula all of the way to the right starting from D201 and from D214.

In the 1872 sequence, the relevant starting balances are in cells D217 and E217. The relevant withdrawals are made beginning from cells E219 and E232. Notice that there is an increment of one column letter and 18 row numbers. I used the fill handle to drag the formulas all of the way to the right beginning from cells E219 and E232.

I made use of the ability to copy and paste code to reduce the likelihood of errors. I wrote the initial formula for the last part of cell D201 in word and highlighted it.

That is, I wrote + IF(D199<=C199,0,-$B$17*(D199-C199))*$B$10 in word and pasted it into corresponding cells such as D217. I edited the cell references (D199 and C199) on the second part. Next, I copied the new contents of cell D214 (and the corresponding cells later on) and pasted them into cell E232. That is, I pasted the entirety of =IF(E217<=0,0,-$B$15*E217)*$B$10+ IF(E217<=D217,0,-$B$17*(E217-D217))*$B$10 at the end of the existing formula in cell E232. I followed that by deleting the first part =IF(E217<=0,0,-$B$15*E217)*$B$10 and by changing $B$10 to (1-$B$10).

I checked the formulas at the end of each sequence. The relevant row number corresponds to the top line of each sequence and the relevant column letters are the last two letters in the sequence.

Availability

I have placed both this calculator and the JanSz-Chips Deluxe Version 1.0C calculator in the special SWR Research section. They are in self-extracting zip files. They are memory hogs. They require just over 3 Mbytes each. They expand to occupy 8.5 Mbytes.

I have also placed both calculators in my Yahoo briefcase under my username jwr19452000.

Have fun.

John R.
Mike
*** Veteran
Posts: 278
Joined: Sun Jul 06, 2003 4:00 am

Post by Mike »

Thank you John.
Post Reply