Membership

One membership: every in-depth guide, plus the Deal Workspace. Templates are sold separately and bought once — see below.

All-Access$9.99/mo

Every in-depth guide across Mortgage, Insurance, Investment, Salary, Rent and Budget, plus the Deal Workspace — our per-property case file for tracking a purchase from pre-approval to closing.

Every in-depth guide, across every category
Deal Workspace — per-property case file, multi-deal comparison, and buying-journey checklist
Unlimited saved calculations
The full article library, always free

Backdoor Roth Pro-Rata Worksheet

$9.99

Every traditional, SEP and SIMPLE IRA you own, in one place, because the IRS aggregates them into a single number and a forgotten rollover account at an old provider counts exactly as much as the one you use. Works out what a conversion actually costs and states plainly whether it is clean. Built around the two things people get wrong: the measurement date is 31 December rather than the day you converted — which means a rollover completed in December still rescues the year — and the after-tax basis is tracked by you rather than by your custodian, so a missing Form 8606 means paying tax twice on the same money.

Format: Excel (.xlsx)

Look inside

Preview only — scroll to read it. The file you download is fully editable.

Read-only preview of the Pro-Rata Tracker worksheet



Backdoor Roth Pro-Rata Worksheet
A backdoor Roth is only clean if you have no other pre-tax IRA money — and the IRS aggregates every traditional, SEP and SIMPLE IRA you own, at every custodian, measured on 31 December. This is the bookkeeping people lose track of and then get the conversion wrong. List every account, and the sheet…

Part 1 — Every IRA you own, at every custodian
INCLUDE: traditional IRA, rollover IRA, SEP-IRA, SIMPLE IRA — yours only. EXCLUDE: Roth IRAs, your spouse's IRAs (the rule is per person), and any 401(k), 403(b) or 457, which is precisely why rolling an IRA INTO an employer plan is the standard fix.

Custodian	Account type	Pre-tax balance	After-tax basis	Notes
Fidelity	Rollover IRA	94000	0
Vanguard	Traditional IRA	21000	0
Schwab	SEP-IRA	15500	0
		0	0
		0	0
		0	0
		0	0
		0	0
TOTAL		ƒx	ƒx	These two totals are the only figures the pro-rata rule uses. It does not care which custodian holds what.

Part 2 — This year's contribution and conversion
Non-deductible contribution made	7000	The after-tax money you just put into a traditional IRA, intending to convert it. Report it on Form 8606 whether or not you convert.
Amount you are converting to Roth	7000	Usually the same figure as above. Converting more pulls more pre-tax money along with it.
Your marginal tax rate (%)	24	Federal plus state. A conversion is ordinary income in the year you do it.
Pre-tax IRA balance on 31 DECEMBER	130500	Yellow because the DATE is what people get wrong. The rule measures your balance at year end, not on the day you converted — so a rollover in November still counts, and one completed by 31 December can still rescue the year.
(from Part 1, if you have listed everything)	ƒx	Copy this into the row above once Part 1 is complete, or override it if you know a rollover will land before year end.

Part 3 — What the conversion actually costs
Total after-tax basis	ƒx	This year's non-deductible contribution plus any basis already sitting in those IRAs from prior years.
Total IRA value the rule looks at	ƒx
Non-taxable share of any conversion	ƒx	The fraction of every dollar converted that comes out tax-free. It is decided by the ratio, not by which dollars you moved.
Taxable share	ƒx
Taxable amount of this conversion	ƒx
Tax owed on it	ƒx	What the pro-rata rule costs you this year. In a clean backdoor Roth this is zero.
Basis left behind for future years	ƒx	Not lost — it stays with your IRAs and reduces the tax on future distributions. But it is stranded until you either convert the rest or take it out.
Is this a clean backdoor Roth?	ƒx

Part 4 — The fix, and its own trap
IF THE ANSWER ABOVE IS NO, THE STANDARD FIX IS TO ROLL YOUR PRE-TAX IRA INTO AN EMPLOYER PLAN. A 401(k), 403(b) or 457 balance is not part of the pro-rata calculation, so moving the money there empties the denominator. It must be COMPLETE by 31 December of the conversion year, and plan administrator…


My employer plan accepts incoming rollovers — confirmed, in writing
It accepts PRE-TAX IRA money specifically, not just plan-to-plan transfers
I have checked the plan's investment options and fees before moving money into it
The rollover is a direct trustee-to-trustee transfer, not a cheque to me
It will complete before 31 December, with weeks to spare
I have NOT rolled after-tax basis into the plan — most plans will not take it
Form 8606 filed for every year I make a non-deductible contribution
I have kept every Form 8606 — basis is tracked by you, not by your custodian

THE LAST TWO ITEMS ARE THE ONES THAT COST PEOPLE MONEY YEARS LATER. Nobody tracks your after-tax basis for you. If the Forms 8606 are missing, the IRS's default assumption is that your distributions are fully taxable — which means paying tax a second time on money you already paid it on. Keep them w…

A CONVERSION IS ALSO A THRESHOLD EVENT. The taxable amount in Part 3 raises your income for the year, which can cross an IRMAA bracket two years later, change how much of your Social Security is taxable, or push long-term gains out of the 0% band. None of that is modelled here — see the withdrawal-o…

This worksheet is a general planning tool, not financial, tax, or legal advice. Rates, fees, and program limits vary by lender, location, and your individual circumstances — confirm real numbers with a lender before making a decision. For real, sourced state-by-state figures, see calculatorbystate.c…

Notes

Preview shows the first 60 rows. The full worksheet continues for 3 more — the complete worksheet is in the download.

This template is general information, not financial, legal, or tax advice.