Roth vs Traditional IRA Spreadsheet (Side-by-Side Math)

Quick answer. Roth versus traditional IRA is a tax-rate-now versus tax-rate-later question. Lower retirement rate, traditional comes out ahead in the math. Higher retirement rate, Roth does. Same rate, tie. A side-by-side spreadsheet shows the dollar gap for any combination of inputs, plus the breakeven tax rate that flips the result.

The choice sounds binary; the math isn’t. Two accounts can hold the same balance, earn the same return for 30 years, and end with very different after-tax outcomes. The difference is when the IRS gets paid. A spreadsheet that compares both paths in parallel is the cleanest way to see it.

This walks through what to put in the spreadsheet, the breakeven calculation that drives the answer, and the variables that change which path comes out ahead. The Traditional vs Roth Calculator ships with this math built in, but every formula below is something you can also type into your own Google Sheet.

The one-paragraph version

Traditional IRA contributions reduce your taxable income now; you pay tax later when you withdraw. Roth contributions are made with after-tax money; you pay nothing later. Same contribution, same growth rate, same horizon. The only structural difference is the timing of the tax. So the question collapses to: is the tax rate higher today, or higher in retirement? The breakeven point is where the two paths produce identical after-tax dollars at withdrawal. Above the breakeven, Roth comes out ahead. Below it, traditional does.

2026 contribution limits and phase-outs

IRS Publication 590-A is the canonical source. The current limits the spreadsheet reads from:

Roth IRA contributions phase out above income thresholds. Traditional IRA contributions are always allowed, but the deduction phases out if you (or your spouse) have a retirement plan at work.

These numbers change yearly. The spreadsheet pulls the current limit into a single Inputs cell so the math stays correct as the IRS updates the figures.

Setting up the spreadsheet

A two-account comparison needs an Inputs sheet, a Projections sheet, and a Dashboard. The Inputs are intentionally short.

Seven inputs. The Projections sheet uses them to build two parallel columns: one for traditional, one for Roth.

Traditional column (year n):

Balance_n = (Balance_n-1 + Contribution) * (1 + ReturnRate)

Roth column (year n):

Balance_n = (Balance_n-1 + Contribution * (1 - CurrentTaxRate)) * (1 + ReturnRate)

The Roth contribution is reduced by today’s tax because you have to earn extra income to make the same gross contribution. Or, equivalently, you contribute less to Roth on the same after-tax dollar. The spreadsheet has to pick a convention and stick to it. We use “same gross contribution, Roth pays the tax separately from outside cash.”

At retirement, the traditional balance gets taxed at the retirement rate. The Roth balance does not.

Traditional after-tax = Balance_retirement * (1 - RetirementTaxRate)
Roth after-tax = Balance_retirement

The Dashboard reports both numbers and the dollar gap.

Today’s effect

The traditional contribution lowers your taxable income for the year. A $7,500 traditional IRA contribution at a 24% marginal rate saves $1,800 in federal tax. That $1,800 is real money you can invest, spend, or use to offset the tax owed on a separate Roth contribution.

The Roth contribution offers no current-year deduction. The $7,500 leaves your bank account, lands in the Roth IRA, and grows tax-free from there.

This is the symmetric piece people often miss. A fair comparison either (a) gives the traditional the deduction and reinvests it, or (b) reduces the Roth contribution to keep the after-tax cost the same. Skip this step and the comparison favors Roth in a way that doesn’t match reality.

Growth over time

Both accounts compound at the same rate. A $7,500 annual contribution growing at 6% real for 25 years produces about $411,000 in either account.

The difference shows up only at withdrawal.

That looks like Roth ahead by $90,000. It isn’t, because the Roth path required paying tax on $7,500 each year out of separate cash, while the traditional path returned $1,800 a year in deductions that could be reinvested.

Over 25 years, the reinvested traditional tax savings (at the same 6% real return) compound to roughly $98,700. Add that to the after-tax traditional balance and the total is $419,300 versus $411,000 for Roth.

At these inputs, traditional ends about $8,300 ahead, because the retirement tax rate (22%) is lower than today’s (24%). Flip those two numbers - 22% today, 24% in retirement - and the gap reverses by a similar margin. Whether the rate gap you actually face is in either direction is the question the formula can’t see.

The breakeven tax rate

The single number that answers the question. The breakeven is the future tax rate at which both paths produce identical after-tax dollars. Mathematically:

Breakeven retirement rate = Current tax rate

That’s it. If the contribution is fully deductible, the breakeven rate equals the current marginal rate. Below it, traditional comes out ahead in the math. Above it, Roth does. Equal, tie.

The proof is short. Let r be the return rate, n years, C the contribution, t_now the current rate, t_ret the retirement rate.

Traditional after-tax at withdrawal:

C * (1+r)^n * (1 - t_ret)

The reinvested tax savings of C * t_now, growing at the same rate:

C * t_now * (1+r)^n

Total traditional: C * (1+r)^n * (1 - t_ret + t_now)

Roth after-tax at withdrawal:

C * (1+r)^n

Setting them equal:

(1 - t_ret + t_now) = 1
t_ret = t_now

The growth rate r and horizon n drop out. That’s a useful piece of news: the breakeven doesn’t depend on how long you have or what return you assume. It depends on your tax rate now versus your tax rate later. Period.

The spreadsheet displays the breakeven as a single cell and a sensitivity table showing the after-tax gap at different retirement rates.

Reading the dashboard

The dashboard is built around three numbers and a small sensitivity grid.

After-tax balance, traditional path. End-of-horizon balance, less retirement tax, plus reinvested deduction savings. The full picture on the traditional side.

After-tax balance, Roth path. End-of-horizon balance with no further tax. Cleaner because no reinvestment math is involved.

Dollar gap. The signed difference. Positive means traditional comes out ahead; negative means Roth. The size of the gap matters as much as the sign - a $500 gap on a $400,000 balance is essentially a tie and any noise in the assumptions could flip it.

The sensitivity grid varies one input at a time. Future tax rate is the most useful one. Step it from 10% to 40% in 2% increments and the grid shows the after-tax dollar value of each path at each rate. The breakeven row is highlighted. Where you think your future rate falls determines which side of the highlighted row you’re on.

A second sensitivity worth running is contribution split. Holding the rates fixed at your projected numbers, vary the Roth percentage from 0% to 100%. The output is a smooth curve - usually pretty flat near the optimum, which is part of why a 50/50 split often gives up very little versus the optimal pure choice.

What changes the answer

The clean breakeven assumes the traditional deduction is fully captured and reinvested. Reality usually breaks one of those assumptions, which is where the variables matter.

State income tax. A state-level deduction now versus a state-level tax in retirement adds to the federal effect. Moving from a high-tax state in your working years to a low-tax state in retirement tilts toward traditional. Moving the other way tilts toward Roth.

Required Minimum Distributions. Traditional IRAs require withdrawals starting at age 73 (or 75 for some birth years; check current law). Roth IRAs have no RMDs during the owner’s lifetime. For a retiree who doesn’t need the full RMD to live on, the forced withdrawal can push them into a higher bracket and reduce flexibility.

Tax bracket compression in retirement. Many retirees see their marginal rate drop because earned income disappears. Social Security, modest withdrawals, and standard deductions can keep retirees in the 12% to 22% range even when working incomes were higher. The breakeven calculation is sensitive to this assumption.

The phase-out cliff. A high earner who is fully phased out of Roth contributions has no direct-Roth option (the backdoor Roth is a separate maneuver covered in our Backdoor Roth Calculator piece). The spreadsheet flags this with an income input.

The deduction itself can be phased out. If you or your spouse have a retirement plan at work, the traditional deduction phases out at income thresholds too. A non-deductible traditional contribution flattens the math: you pay tax on the contribution now and on growth later, which usually leaves Roth ahead at the same return. The spreadsheet checks for this.

Heirs. A Roth left to non-spouse heirs grows tax-free for 10 more years under current rules, then comes out tax-free. A traditional IRA left to heirs distributes over 10 years and the heir pays income tax at their rate. For high-net-worth readers, the heir’s expected tax bracket matters; this is its own conversation and beyond the scope of a simple two-column comparison.

The “future rates are unknown” caveat

The breakeven calculation assumes you know your future tax rate. Nobody does. Congress changes rates, incomes change, states change, filing statuses change.

This is the math behind splitting. Half traditional and half Roth produces a weighted-average after-tax result that’s less sensitive to rate uncertainty than either pure choice. The spreadsheet supports any split: 100/0, 50/50, 70/30. The output is a smooth curve; it doesn’t beat the optimal pure choice, but it shrinks the downside if the rate projection turns out wrong by 3 or 4 percentage points.

A 50/50 split with current rate 24% and projected retirement rate 22% lands between the two pure outcomes. Whether that’s worth doing depends on how much weight you give to rate uncertainty versus a known small edge today.

Conversions are a different decision

Confusing the two is common. A Roth conversion moves money from an existing traditional IRA to a Roth IRA and pays tax on the moved amount now. The breakeven math is similar - rate now versus rate later - but the timing is different. There’s no current-year contribution involved, and the converted amount often pushes you into a higher bracket if not carefully sized. The Roth Conversion Calculator article walks through that math separately.

Templates that fit this question

• Traditional vs Roth Calculator - A focused tool that compares the two paths year by year, calculates the after-tax gap at retirement, and shows how shifting tax rates move the answer. Fits when the comparison is the only thing you need.
• Retirement Financial Planning Projections - The broader 40-year plan that includes the traditional/Roth split alongside Social Security, employer match, and withdrawal ordering. Fits when this decision is one piece of a full retirement projection.

Both files open the same way in Google Sheets, Excel, or LibreOffice Calc. The Inputs cells sit at the top; everything else recalculates.

Related

• How to Build a 40-Year Retirement Plan in Google Sheets
• Roth Conversion Calculator: When Converting Makes Sense
• 401(k) vs Roth 401(k) Calculator: Which One Wins?
• Roth IRA Growth Calculator: See Your Tax-Free Future
• Retirement Account Tracker: 401(k) and IRA in One Sheet