Roth IRA Conversion Spreadsheet

Aug. 06, 2017 11:11 AM ET171 Comments5 Likes
Investment Pancake profile picture
Investment Pancake


  • Required minimum distributions have both direct and indirect tax costs.
  • For investors with significant dividend income and relatively little ordinary income, it can pay in more than one way to convert a traditional IRA to a Roth IRA.
  • Link to a free spreadsheet investors can use to calculate potential tax savings (or tax losses) from a Roth IRA conversion.

You may not realize it, but under today's tax laws, if you earn $75,900 a year in qualified dividend (or long-term capital gain) income and have no other income, the marginal Federal tax rate on that first $75,900 is ZERO. It's like a "stealth" $75,900 Federal tax exemption on qualified dividends and long-term capital gains, which many investors never even notice. Other types of income can crowd out the dividends and gains that might otherwise have been eligible for the exemption, causing the exemption to go completely unnoticed.

For example, let's take a married couple filing jointly with no dependents. They can shield up to $20,800 in ordinary income thanks to the standard deduction and because they can claim two personal exemptions. If they earned $20,800 in ordinary income and $75,900 in dividends and long-term capital gains (for a grand total of $96,700 per year), they would be able to completely use the stealth exemption and thereby pay no Federal income tax. If, on the other hand, they earned $96,700 in ordinary income and $100,000 in dividend income, no portion of that $100,000 would be eligible for the stealth exclusion - the ordinary income would entirely crowd the dividend income out of the stealth exemption. As a result, the entire $100,000 would attract a marginal tax rate of 15%.

So the question is, how do you make the most of this stealth tax exemption? As far as Federal taxes go, the ideal solution for an investor would be earn no more than $20,800 in ordinary income, and concentrate the balance of income into qualified dividends and long-term capital gains. That's difficult to accomplish while you're working and drawing a salary, but for a retiree, it could be possible - depending on your social security payments, pension income, etc.

That brings me to one of the problems with required minimum distributions (hereafter, RMDs) from a traditional IRA, 401(k) or other deferred tax retirement account. These distributions can push you even further above the $20,800 ordinary income mark. If so, then each dollar of RMD could crowd out potential dividend and long-term capital gain income that you could have otherwise received completely free of Federal income tax. This opportunity cost, plus the direct tax on the RMD itself, can be a double whammy for the taxpayer.

One way to avoid the double whammy is to avoid the need for RMDs in the first place by converting a traditional IRA into a ROTH IRA. It's not possible to articulate any sort of bright line rule for whether it makes sense to make these conversions. The reason why is because there are multiple variables you need to take into account, including (1) how much dividend income you earn now and expect to earn in the future, (2) how much ordinary income you earn now and expect to earn in the future, (3) your filing status (single, married, dependents, etc.), (4) your expected dividend growth rate, (5) your expected growth rate for ordinary income, (6) the amount of RMDs you expect in the future, and (7) the time value of money.

That's why I decided to put together a sample spreadsheet to help investors calculate if and when a Roth conversion could make sense. A person's individual tax situation has many other variables besides the ones captured in the spreadsheet - too many for me to include in a standardized format. The best that the attached spreadsheet can do is to roughly navigate some of the main variables, and provide a starting block from which to launch the taxpayer down the path towards tax championship. Feel free to copy the link and save a copy of the spreadsheet for yourself. Then, you can manipulate the variables, add a few variables of your own based on your individual tax situation, increase the assumed number of years in the calculator from ten to whatever other number of years you wish, and otherwise customize the tool to your own uses and based on your own preferences.

Here is the link:

Roth IRA Conversion Benefits For Dividend Investors

Simply click the link, and you will get to a screen that looks like this:

Click "file" in the upper left hand corner, and then click "make a copy." You may need to create a Google account for yourself if you don't already have one - it's free and easy to do.

The spreadsheet I have set up will allows you to enter 9 variables (which you can see in the green boxes), including how much ordinary income you expect in retirement (not including RMDs), how much dividends and long-term capital gains you expect, whether you expect to claim one or two standard deductions, your expected dividend growth rate and expected inflation rate, and the current value of your retirement account that could be converted into a Roth.

Let's walk through an example calculation.

In the example you see above, I assumed a single taxpayer claiming only one exemption. I have built in some assumptions - for instance, the RMD calculation assumes the taxpayer is 70. You may wish to change that age assumption, and if so, you would need to use a separate online tool to calculate the RMDs (such calculators are easy to find online).

I assumed the taxpayer expects $60,000 a year in ordinary income (not including his RMDs) that will grow by 4% a year. He also expects $5,000 of qualified dividend income per year that will grow by 8% a year. The assumed tax bracket I entered is 28% (which could very well change in any year where there's a large lump sum of ordinary income). I assumed a 2% inflation rate and a balance of $300,000 in his regular IRA.

The spreadsheet digests all these variables and determines that the RMDs on their own produce a total tax liability of $35,179 in today's dollars - both for the ordinary income tax on the distributions themselves, but also because of the opportunity cost of losing the benefits of the "stealth" exemption for dividend income gets crowded out by the RMDs. The spreadsheet also concludes that the total cost of converting an amount of the IRA assets equal to the present value of all those RMDs (in this case, $115,429) to a Roth IRA would cost $33,070 in direct tax costs as well as the opportunity cost from losing some of the stealth exemption for qualified dividend and long-term capital gain income. The total benefit of converting this amount to a Roth IRA on these assumptions appears to be $2,108 over a ten-year time horizon.

At the risk of repetition, this spreadsheet does not capture all of the potential benefits and costs of a Roth conversion. Some people may opt to convert to a Roth for estate planning purposes. Others may be planning to live abroad, and seek a source of income that will be tax exempt under an applicable tax treaty. There may be ways you could add those types of considerations (if applicable to your situation) into your own version of the spreadsheet. Enjoy!

This article was written by

Investment Pancake profile picture
Individual value investor with strong penchant for dividend growth.  A former tax and estates attorney who retired in his early 40s and expatriated to Lisbon, Portugal with his family. Now writes about tax law, portfolio strategy and life in sunny Portugal.Association with SA author Jose Trias

Disclosure: I/we have no positions in any stocks mentioned, and no plans to initiate any positions within the next 72 hours. I wrote this article myself, and it expresses my own opinions. I am not receiving compensation for it (other than from Seeking Alpha). I have no business relationship with any company whose stock is mentioned in this article.

Additional disclosure: This article, and the spreadsheet tool, are not tax advice and cannot be relied on for any purpose other than informational purposes.

To ensure this doesn’t happen in the future, please enable Javascript and cookies in your browser.
Is this happening to you frequently? Please report it on our feedback forum.
If you have an ad-blocker enabled you may be blocked from proceeding. Please disable your ad-blocker and refresh.