Instructions
To find the optimal Roth conversion for the active scenario, click the Run Roth Optimization button on the Control Panel. This will test every Income Target value from 0 to $1M at $10k intervals for the six interest rates shown in the column headers on the Roth Conversion Optimizer worksheet for the selected Scenario. It will display the results in both a table and chart. The column headers can be modified if different rates of return are desired. The table assumes all the returns (Savings, IRA, & Roth IRA) are the same. Once the optimal Income Target value is identified, the user can enter and load that value back in the active scenario and view it in the Main worksheet.
See blog comment for additional details.
Methodology
This chart shows the Final After Tax Net Worth for every Income Target from 0 to $1M at the six different rates of return shown in the table on the left. Monte Carlo is disabled for this report. The highest point on each line is the optimal Income Target for that rate of return. All the other values from the Active Scenario are held constant. The optimal Roth Conversion is shown in the “Roth Conv” column on the Main worksheet. It is the difference between the Income Target and any other sources of income (Fixed Income, RMD, Social Security, Income and IRA withdrawals).
On the Main worksheet, the Adj Income Target column equals the Income Target minus any Fixed Income, RMDs, Social Security and the Income column (adjusted for inflation). As a result, the Adj Income Target will represent the the value of the actual Roth Conversion plus the amount of the IRA withdrawn for remaining annual expenses. This keeps the tax rate at the desired target as long as possible.
This spreadsheet uses a Roth conversion ladder methodology for pulling money out of the IRA. This is what enables it to find the optimal Adj Income Target. People who are used to thinking of Roth conversions and IRA withdrawals separately can think of the Adj Income Target as the sum of these two amounts. The spreadsheet automatically determines the amount to be used for expenses (the “IRA withdrawal”) and will leave the rest in the Roth to grow tax free (the “Roth conversion”).
Notes
- This spreadsheet does not currently calculate early withdrawal penalties. The user can add penalties in the one-time expenses column on the Main worksheet if needed.
- The portion of Adj Income Target that is IRA withdrawal and the portion that is Roth Conversion can be seen in columns L and M on the Main worksheet.
- It’s possible for the Roth IRA to be negative while the IRA still has money in it. This is a sign that the Income Target is too low and that the scenario is not optimal.
- Just because this spreadsheet uses a Roth conversion ladder methodology for the calculations doesn’t mean the user should implement it this way. It may make sense for the user to do an IRA withdrawal earlier in the year and then do a Roth IRA conversion at the end of the year when they have a better idea of what their taxable income will be. Mathematically these two approaches are the same. Click the links below for more information on Roth conversion ladders.
Roth Conversion Ladder Links