A number of topics are cued up which will be posted over the coming months. Feel free to respond to this post if there are topics you’d like to see.
Roth IRA Conversion Optimizer
Community Site
Welcome to the Roth IRA Conversion Optimizer Blog
14 responses to “Welcome to the Roth IRA Conversion Optimizer Blog”
-
Here are some ideas for topics.
- Explain how the Roth Conversion Optimizer sheet works
- Explain how to interpret the Monte Carlo results
- Explain the difference between Scenarios and Scenario Sets
-
Great suggestions! I’ll post on all of those in the coming weeks!
-
It would be great if you could suggest what to do if you file under head of household. About 13% of the population files this way. Is it better to list single or married filing jointly? Or run it both ways?
-
Thanks for the question! The best approximation for Head of Household users will be to select “Single”.
At the Federal level, these two brackets are similar. The primary differences are at the lower/middle brackets, where Head of Household is slightly better taxwise. Selecting Single will result in up to $1925 more tax than Head of Household. Fortunately, that’s the largest the difference will be.
The brackets are identical once your taxable income is over $103k which is why $1925 is the max. Assuming your income is over $103k, a simple fix would be to select “Single” for your “Filing Status” and reduce your “Starting Annual Expenses” by $1925. This will result in the same Total Expenses as if the brackets had an option for “Head of Household”.
In building the spreadsheet model, both the Federal and State tax calculations had to be high-level estimates. The subtle details around deductions, credits, exemptions, adjustments, and state/local rules make it impossible to provide an exact tax calculation like tax software would. Fortunately, the tax calculations don’t need to be perfect to provide a good long-term model. What’s most important for the model is that the progressive tax structure includes both the Federal and State tax brackets—which it does.
Additionally, there are two blue columns in the Main spreadsheet that allow people to fine-tune the model if a higher level of accuracy is desired. The Income column allows people to add/subtract taxable income. The One-Time-Expense column allows people to add/subtract expenses. Creative use of these two columns provides a lot of additional flexibility for making adjustments.
-
Head of Household is now available as a selection for Filing Status in release v3.1b. Thanks for pointing this out!
-
-
I am not able to activate the program. I thought I did the “make it a Trusted…”, though I was not clear what a “trusted site” is. Now it says the sheet is protected and I “may need to enter a password” Any suggestions.
Thanks-
Hi Tom. Thanks for purchasing the spreadsheet! A trusted document just means you trust the author and are allowing that document to run macros. By default Excel will have macros disabled to protect from viruses. You should only enable macros for files that you trust. See this link for Microsoft’s support page on Trusted Documents.
The best way to confirm macros are enabled is to look for the Control Panel when you open the document. If the Control Panel appears, then the macros are enabled.
Sheet protection is different. I have deliberately protected all the worksheets to prevent users from accidentally breaking the spreadsheet. The spreadsheet is designed to run with sheet protection on. The only place you are required to make edits is in Scenarios 1 – 5 on the top of the Scenario Sets worksheet. There are a few other places where edits are allowed, but they are for more advanced modeling.
While I encourage users to leave screen protection on, it can be temporarily disabled with the “Remove Sheet Protection” button on the Control Panel. Although, this is for power users. I don’t recommend using this button until you’re very comfortable with the spreadsheet and are confident you won’t break anything.
Let me know if that helps!
-
-
How can I handle Pensions that do not have COLA? For married filing jointly, how do I address an age difference as it effects taking RMDs among other things? Why isn’t there a separate entry for self and spouse?
-
Hi James. Thanks for the questions!
The pension is a good example of something that can be added to the Income column (column O) on the Main spreadsheet. Anything you put in that column will be taxed as ordinary income and accounted for in the Roth Optimization function. You can type the income into each cell manually or use a formula if desired.
Merging the spouses IRAs and Roths for MFJ (married filing jointly) was a design decision for the initial version. It allowed me to keep the model simple so I could focus on the optimal Roth conversion algorithm—which is the primary purpose of this tool. I plan to add support for separate IRAs, Roths, RMDs, Social Security, etc. in future versions.
The tool currently requires you and your spouse to combine your Savings, T-IRA and Roth accounts and will model the RMDs as if you and your spouse were both the same age. There are a couple ways you can handle this.
- Enter the Starting Age in the Scenario of the person who will get RMDs first. This will result in the tool overestimating the RMDs of the younger person since, in reality, they would start later.
- Enter the Starting Age in the Scenario of the person with the larger IRA. If one spouse has a significantly larger IRA, but is younger than the other, then it might make sense to use their Starting Age instead. In this case, the tool will not capture RMDs properly for the older person with the smaller IRA so you need to be aware that this is an aggressive view compared to reality.
I never expected the spreadsheet to gain this much traction so soon after its initial release. The feedback from users has been amazing! I’m adding features and enhancements as quickly as possible to respond to the demand and am regularly releasing new versions. The tool will just continue to get better!
-
-
So where does it say what the optimum amount I’m to convert each year?
-
Thanks for the great question as this is the most important feature in the tool!
When you click the “Run Roth Optimization” button, it will generate a table and chart on the “Roth Conversion Optimizer” worksheet. The chart is sourced from the table, so they’re both showing the same thing, which is the after-tax net worth at your end of life for 600 different scenarios.
The table represents different settings of the following fields from your scenario Set: “Income Target”, “Savings Return”, “IRA Retirement Return” and “Roth IRA Return”. You could create the table manually, but you’d have to run 600 different scenarios on the “Main” worksheet to do it.
When you click the “Run Roth Optimizer” button, it will first set the “Savings Return”, “IRA Retirement Return” and “Roth IRA Return” to 4% and then test 100 different values of the “Income Target” from $0 to $1,000,000, putting the “After Tax Net Worth” value from the total row (“Main” worksheet) into the associated 4% column of the table on the “Roth Conversion Optimizer” worksheet. It will then do the same thing for the 6% column, 8% column, 9% column, 10% column, and 12% column (600 total iterations).
Now, you can quickly glance at the table, or the chart, to see which value of “Income Target” provides the largest “After Tax Net Worth” at your EOL. This is your optimal “Income Target” and is the key to understanding your optimal Roth Conversion.
The first thing you’ll notice is that it is highly dependent on the rate of return of your accounts (“Savings Return”, “IRA Retirement Return” and “Roth IRA Return”). The larger the return, the larger the optimal “Income Target” will be.
So how do you get from optimal “Income Target” to optimal Roth Conversion amount? At a high level, you can think of the optimal “Income Target” from the table/chart as that optimal Roth conversion amount. In the first version of the Optimizer, it was actually called that. But here’s the issue.- The optimal Income Target is in today’s dollars. It needs to be adjusted for inflation.
- Optimal Income Target assumes all income sources, not just the Roth Conversion income.
So, to find the optimal Roth conversion amount for a given year, you need to do the following:
- Take the optimal Income Target value from the first column of the table and plug that value back into your Scenario Set.
- Make sure the “Savings Return”, “IRA Retirement Return” and “Roth IRA Return” match the chosen column from the table (4%, 6%, etc.)
- Load the Scenario to the Main worksheet.
- Look at the “Roth Conv” column on the Main worksheet.
That’s it. The “Roth Conv” column in the Main worksheet now shows you the optimal Roth conversion amount, taking into account inflation and additional income sources like Social Security and RMDs for each year in your Scenario. As a double-check, the After Tax Net Worth value in the total row should match the value in the Roth Conversion Optimizer table. If these two value match, you did it right!
-
One question I have regarding the Social Security settings. At the present time, if nothing is done by our government, the Social Security Trust Fund will run out of funds in 83 months. This will cause our checks to be cut by about 23%, with more to come as the workforce diminishes and retirees increase. Is there any way to model this in the spreadsheet?
-
Great question! You’re right that current projections show the Social Security Trust Fund facing a shortfall in the coming years if no legislative changes are made. While the spreadsheet doesn’t automatically model future benefit cuts, you can reflect this scenario manually a few different ways.
1. Reduce your Social Security benefit directly in the Scenario settings. This is the simplest option. You can reduce your benefit by 23% (or any percentage you want). The downside is this also reduce it in the years leading up to the projected shortfall.
2. Add a custom formula in the blue Income column to reduce Social Security. If you want the reduction to begin in a specific year, you can use a formula that applies the cut only after the year you choose. This gives you more control and lets you model multiple reduction scenarios. An example formula is below.
=IF([@Year]>2033,-0.23*[@[Social Security]],[@[Social Security]])The one downside of this is that Social Security is taxed slightly differently than income so your taxable income will be slightly off (but probably negligible).
3. Modify the Social Security Calculation. I don’t recommend this one and I don’t support spreadsheets that have been modified this way, but you’re welcome to try it if you’d like. This is what I would do if I was to make it a permanent change on my side (in the master sheet). You will need to use the “Remove Sheet Protection” button on the Control Panel to make this change.
=LET(
SocialSecurityAmount, [@[Self Social Security]] + [@[Spouse Social Security]],
PastStartYear, [@Year] > 2033,
Reduction, 0.23,IF(PastStartYear, (1 - Reduction) * SocialSecurityAmount, SocialSecurityAmount)
) -
I decided to build this functionality into the Optimizer. I just released a new version, v4.1d, that allows you to set the Year the reduction starts and the percentage reduction amount. These fields are at the top of the “Experimental Settings” section on the Settings worksheet. The default year is set to 2100, which essentially disables it. If, as an example, you change the year to 2033, the reductions will start in 2033.
-
