This is Part 3 in a series on Roth conversions. If you haven’t read Part 1 and Part 2, you should go back and start there, because this post builds on both.
Those posts treated a Roth conversion like any other cost-benefit analysis. You pay real costs up front, in the form of taxes and lost ACA subsidies, and you hope to earn that money back later through lower RMDs and less tax on withdrawals. Whether the conversion has a positive return depends on what those future savings are worth in today’s dollars. That’s why we used a discounted cash flow and NPV approach instead of just comparing tax rates or nominal dollars.
I couldn’t find a free tool that handled the time value of money, ACA subsidies, and RMDs together, measured against the right baseline, so I made one in a spreadsheet. This post covers how the sheet works, what it does and doesn’t consider, and how to use it for your own situation. The link is at the bottom.
Version 8 is the one I’m sharing. Versions 1 through 7 are a cautionary tale about scope creep.
The Baseline
The model is built around a simple baseline: doing nothing has an NPV of zero. You retire, collect every ACA subsidy available to you, let the traditional account grow, and take RMDs when required. Every strategy is measured against that.
Basically the model asks the question: how much does it cost convert, compared with the later benefits of reduced taxes?
How the Workbook Is Organized
There are seven tabs.
- Assumptions: This is where you enter data. Every blue cell is an input: your age, RMD start age, Medicare age, tax rates, discount rate, investment return, and ACA assumptions. The black cells are calculated, so it is best to leave them alone.
- Conversion Schedule: 40 rows, one per year. Enter a conversion amount in any year. The tab calculates the tax cost and lost ACA credits for each one. To compare strategies, clear the cells, enter new amounts, and note the NPV.
- Balance and Drawdown: Projects your traditional balance year by year, with and without conversions. Column C is your annual drawdown for living expenses before RMDs begin. Column D shows it as a percentage of your current balance, a quick sanity check if you’re targeting something like the 4% rule.
- RMD Calculations: Looks up the IRS Uniform Lifetime Table factor for each age, divides it into the projected balance, and calculates the RMD in both scenarios. Column K, the annual RMD tax savings, feeds into the NPV.
- NPV Analysis: Adds up the present value of the costs (taxes paid, ACA credits lost) and the benefits (RMD tax savings, discounted year by year) and gives you one number.
- Sensitivity Analysis: NPV across a grid of future tax rates (rows) and investment returns (columns). Green is positive, red is negative. If you only look at one tab before deciding, make it this one.
- IRS ULT: The Uniform Lifetime Table from Publication 590-B.
One Design Choice
There are two possible ways to measure the benefit of a conversion: the tax avoided on Roth withdrawals and growth, or the reduction in taxable RMDs. They measure the same benefit from different angles, so using both would be double-counting. I used the RMD approach because it captures the full balance trajectory, including drawdowns, and uses the actual IRS factors.
Recreating Sarah and Tom
To reproduce the numbers from Parts 1 and 2:
On the Assumptions tab, enter:
- Current age: 55
- RMD start age: 75
- Medicare age: 65
- Discount rate: 5%
- Investment return: 7%
- Marginal Tax rate on conversions: 22%
- Future traditional tax rate: 22%
- Roth rate: 0%
- ACA credit without conversion: $20,000
- ACA credit with conversion: $0
- Traditional balance: $2,000,000
On Balance and Drawdown, you’ll see some default entry for withdrawals in column C.
Then:
- Strategy A: Enter $100,000 in column C of the Conversion Schedule for years 1 through 5 (ages 55 to 59). You should see costs of $152,203, with benefits of $124,551, and an NPV of negative $27,652.
- Strategy B: Clear Strategy A and enter $100,000 for years 11 through 15 (ages 65 to 69). You should see costs of about $40,281, benefits of $63,315, and an NPV of positive $23,034.
- Do nothing: Leave the schedule blank. The NPV is zero.
Using It for Your Situation
I’d encourage you to get the big inputs right first: current and future tax rates, Medicare age, and your ACA subsidy situation. For the discount rate, 5% is a reasonable risk free default, which is close the the 10 year treasury rate.
You should also check the Sensitivity tab, which shows you whether your conclusion holds across a range of future tax rates, not just the one I started with.
What the Model Doesn’t Do
- Consider Heirs. Roth accounts pass to beneficiaries income-tax-free, while traditional balances pass as ordinary income. For a large traditional IRA that benefit can be substantial. I left it out because you won’t be around to enjoy the benefits, and you’d have to speculate about your heirs’ tax brackets decades from now.
- State income tax. If your state taxes conversions, add it to your current rate as a rough adjustment.
- Social Security taxation. Higher retirement income can make more of your benefits taxable.
- IRMAA. The model doesn’t calculate Medicare surcharges. In Part 2 I kept conversions under the first threshold, and I checked that by hand. Check it yourself if your conversions get large.
| Included | Not included |
| Federal taxes on conversions | State income taxes |
| ACA subsidy loss in conversion years | Medicare IRMAA surcharges |
| IRS actuarial table RMDs | Social Security tax impact |
| Annual drawdown before RMD age | Benefit to heirs |
| Do-nothing baseline (NPV = 0) | Estate tax |
| Time value of money throughout | |
| Sensitivity across tax rates and returns |
I think the spreadsheet evaluates the most important drivers of the Roth conversion cost-benefit analysis, but it doesn’t include everything. So that is one more reason you should treat the NPV output as an informed estimate, not a precise answer.
The Bottom Line
I built this because the calculators I kept finding were incomplete and often measured against the wrong baseline. This one puts the right costs and benefits on the ledger and appropriately accounts for the time value of money. Whether it tells you to convert early, wait, or do nothing, the answer will be grounded in math you can see.
The spreadsheet is linked below. If you find an error or have a suggestion, the contact page is always open.
