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

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, and the IRS announcement of the 2026 limits sets the figures below. The current limits:

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 Traditional vs Roth Calculator takes the annual contribution as a single input 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 shipped calculator uses exactly those three, named Comparison Setup, Projection Table, and Dashboard. The inputs are intentionally short.

Seven inputs in this layout. The shipped calculator gets to five by taking years to retirement directly instead of two ages, and its Projection Table runs 40 year rows. Either way the projection builds two parallel columns, one for traditional and 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 the same pre-tax dollar buys less once the tax is paid. A comparison has to pick a convention and hold it. The shipped calculator holds the pre-tax outlay equal on both sides: $7,500 leaving your pocket goes in whole on the traditional side and lands as $5,700 on the Roth side at a 24% rate. In that setup the traditional deduction is not an extra benefit; it is what funds the larger traditional contribution.

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, so 22% today and 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 calculator displays the breakeven as a single dashboard tile, next to the after-tax total for each path and the gap between them.

Reading the dashboard

The dashboard is built around six tiles, a comparison chart, and a line-by-line detail block.

After-tax retirement income, traditional path. The end-of-horizon balance, less tax at the retirement rate entered.

After-tax retirement income, Roth path. The end-of-horizon balance, with no further tax. Smaller going in, because the equal pre-tax outlay arrives there already taxed.

Difference, and which side is ahead. One tile holds the gap between the two after-tax totals; a second names the side that ends higher on the assumptions entered. The size of the gap matters as much as the name: a $500 gap on a $400,000 balance is essentially a tie, and any noise in the assumptions could flip it.

There is no sensitivity grid in the file. Testing the assumption means changing the Expected Retirement Tax Rate in Comparison Setup and watching the tiles and the projection recalculate. The breakeven tile is the reference point, because it sits at whatever current rate you entered: retirement rates below it leave traditional ahead in the math, rates above it leave Roth ahead.

The two remaining assumptions, the return and the number of years, scale both sides together. Moving them changes the size of the numbers without changing which side ends ahead.

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, rising to 75 for those born in 1960 or later, per the IRS RMD rules. 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 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 calculator takes tax rates rather than income, so deductibility is a check to run outside it.

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 calculator compares the two pure paths rather than a blend, and because each is linear in the amount contributed, any split lands proportionally between them, with 50/50 halfway. A split 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 - A $39 Google Sheet that runs one combined savings balance from your current age to life expectancy, drawing down whatever annual expenses exceed pension and other income, across conservative, base and optimistic scenarios plus a block of what-if tests. It holds no account types, so the traditional versus Roth split is not part of it. Fits when the question is whether the money lasts, which is the other half of a full retirement projection.

The Traditional vs Roth Calculator opens in Excel, Google Sheets, or LibreOffice Calc; the retirement template is a Google Sheet. In both, the file marks which cells you fill in and 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
• Solo 401(k) vs SEP IRA Spreadsheet - The self-employed retirement account version of this Roth-versus-pre-tax choice