Info for other users on determining Roth conversion amounts. References are to RPM Rev. 19.0.
An Excel technique to automatically establish factors for a Roth conversion. The effect of these refinements over manual entries will be small compared to that of uncertainties in other assumptions and estimates in your model. So... for interest only.
I have always used the RPM Setup Sec. 10 'Entered yearly anounts' option to choose Roth conversion amounts (set cell E244 to 'e'), picking values for cells E258 etc that would cause Federal taxes to just hit 100% of one bracket without straying into the next higher one. That's simply a rule of thumb but is reasonable for manual use. I ignored the 'Calculated' amounts method because in my case it has only three variables to work with: first-year Starting value, Annual percent change, and End Age. I had assumed that this limitation would prevent it from bettering hand-crafted entries, and a few manual trials of 'Calculated' seemed to show that.
However, Microsoft Excel has a powerful optimizing add-in called 'Solver'. It also has 'Goal Seek' but AFAIK that can only handle one variable so won't apply to this case. Solver can theoretically handle large sets of variables, and provides several advanced methods of optimizing a 'results' cell by manipulating such sets of input cells.
It finally occurred to me to try Solver on the 'Calculated' method, and I was surprised and impressed to find that the results were better than my manually 'Entered' values. Again, these are small differences at a distant time, but it's easy to try and interesting to see.
Here's how to do it:
- Install the Excel 'Solver' add-in - google for instructions on that. Another good reason to buy Excel and not scrape by on VisiCalc.
Enable RPM's Roth conversion 'Calculated amounts' mode (set cell E244 to 'c').
Set your Start Age. I have no choice in this value but if you do, see the note later on.
Open Solver (Excel top menu | Data | Solver).
At the bottom, select the 'Evolutionary' method.
At the top, tell Solver to maximize Portfolio Ending Balance w Conversion (cell I247).
Below that, tell Solver to change variable cells for Starting amount, Annual Escalation, and End Age (cells E247,E248,E250) aka Roth_Convert, Roth_Esc, Roth_End.
These input cells must have high and low bounds set; that's done in the middle window. For cell E247 I suggest a max of your entire Trad IRA starting value, and a minimum of 5% of that value. For cell E248 I suggest a max of 0.2 (a 20%/yr increase) and a minimum of -0.2 (a 20% decrease). Set cell E250 to allow for an approx 20-year span.
Click on 'Solve'.
Excel should within a few minutes return practical results in cells E247, E248, E250. In my case these resulted in conversion amounts that, though they sometimes jumped into a higher bracket than I would have chosen with 'Entered' amounts, gave a superior final balance.
If Solver instead reports that it can't find a solution, or produces some obviously bogus values, you can try tweaking it. Open the Options dialog and tab to the Evolutionary settings. There 'Mutation Rate' can be increased up to a maximum of 1.0, and for 'Random Seed' you can choose any positive integer. Allowable solution time can also be increased, here and in the All Methods tab. More info on these is available on the web, especially from Frontline Systems who developed the Solver module. Also note that if you have flexibility in your Start Age you can use "E247:E250" as a range above, and perhaps do even better.
Once you have a good set of Calculated numbers you can manually clean them up by rounding to the nearest dollar or 0.01%, while keeping an eye on final balance to make sure you don't affect that. Don't be surprised if small changes here actually improve the outcome, as the Evolution solver can miss those. You can tighten the convergence settings and re-run the Evolutionary method, or the GRG Nonlinear method, starting with these first Evolutionary results, just to see what happens.
It won't be of much value to re-optimize / change these Calculated values unless some other RPM factor has altered significantly. Solver will remember its last scenario so you can run it again very easily if necessary. The Calculated conversion amounts can be seen in Details sheet cells F128-AS128. These will at least give you a reasonable set of starting points and time span for manual 'Entered' values before you decide on funding an real conversion.
Of course Solver can be theoretically be used to directly calculate optimum 'Entered' amounts for cells E258 etc, hoping to find the maximum possible final balance. However, even its Evolutionary engine is not sufficiently robust to do this reliably in a straightforward manner. It will most often report failure or give useless results. That's because the RPM model is necessarily highly and sharply nonlinear. There are techniques to help Solver with this (such as input scaling) but they have performed only a little better for me and are not easy to explain. Still, everyone's situation is different and it can be worth trying Solver on the 'Entered' values directly just out of interest. The Calculated method above is completely automatic after the first optimization so may be best most of the time.