This is the third model I’ve pulled out of a spreadsheet and rebuilt as a web app, after the staff scheduler and the vehicle router. Going in, I assumed the optimization would be the hard part. It wasn’t. The solver finished in under a second every single time. The hard part was trusting what it told me — and the lessons that came out of that are the ones I keep coming back to.
Here’s what building a Roth conversion optimizer actually taught me. Not about Roth conversions — that’s a separate post — but about the craft of turning a twenty-year-old model into something other people can trust.
1. Your old model does not say what you remember it saying
The source was an Excel file I’d built years ago and solved with OpenSolver. I remembered it as a model that maximized the money I’d get into a Roth. So before writing a line of Python, I opened it back up and read it cell by cell. Three surprises.
First, the objective in the file wasn’t “maximize Roth” at all. It was minimize the present value of lifetime tax. Those are not the same goal, and if I’d coded what I remembered instead of what was actually there, I’d have built the wrong tool.
Second, the “single vs. married” switch was quietly broken. The bracket widths were hard-coded to the single-filer numbers, and the married toggle never actually swapped them. Anyone running it as a married couple was getting single brackets and wrong answers, and had been for years. I never noticed because I always ran it for myself.
Third, the model never used a “tax on whatever’s left” term. It forced the traditional balance down to roughly zero by the end of the horizon and minimized tax along the way. That’s a perfectly valid way to make the problem well-posed, but it’s a modeling choice, and I’d forgotten I’d made it.
The lesson: a model you haven’t touched in years is a black box, even if you wrote it. Reverse-engineer it like a stranger’s code before you port it.
2. Validate to the dollar, or don’t ship
The rule I gave myself was simple: feed the Python engine the exact inputs from the spreadsheet, and the two have to agree to the dollar before any web work starts. Not “close.” Not “same shape.” The dollar.
They came in at 0.3% apart — the engine produced $164,729 of lifetime tax against the spreadsheet’s $165,199. That gap nagged at me until I found it: the spreadsheet realized a small long-term capital gain in two early years that my first version didn’t model. Once I accounted for it, the numbers lined up exactly, year by year, bracket by bracket.
I cannot overstate how much confidence that one exercise bought me. Every weird-looking result after that, I could trust was the model talking, not a bug. Which mattered, because the model was about to say something that looked completely insane.
3. The objective is a decision, and it changes the advice
Here’s a subtlety that the spreadsheet hid from me. “Minimize lifetime tax” sounds like one well-defined thing. It isn’t. Do you minimize the raw sum of every future tax dollar, or the present value of those dollars, discounting future tax because a dollar paid in 2050 hurts less than one paid today?
The spreadsheet effectively did the former — nearly undiscounted. I think the present-value version is more honest, so I made it the default. The kicker: on the same inputs, the two objectives gave materially different advice. The undiscounted version wanted about $62,000 of conversions a year; the present-value version wanted about $24,000. Same model, same person, same tax law — a 2.5x swing in the recommendation, driven entirely by a modeling assumption most users will never see.
So I left both in, switchable, and documented which is which. If a single buried assumption can move your answer that much, hiding it isn’t simplicity — it’s negligence.
4. “Optimal” is not the same as “unique” — and the difference looks like a bug
This was the big one. My first working version produced a year-by-year plan where the recommended conversion lurched around violently. One year it would fill the 12% bracket; the next it would spike up into the 22% bracket; the year after, back down to 12%. To a human it looked random and frankly unusable. Who’s going to trust “convert $70,000 this year, $15,000 next year, $72,000 the year after” with no apparent reason?
The instinct is to assume a bug. But the validation work told me the tax numbers were right. What was actually happening is something every optimization person eventually meets: alternate optima. The problem had a whole family of conversion schedules that all produced the exact same lifetime tax — to the penny. The solver isn’t wrong to return any of them. It just grabs whichever corner of that flat region it lands on first, and that corner can look like nonsense.
The fix is a technique worth knowing: lexicographic optimization. Solve once for minimum tax and record that optimal value. Then lock the tax objective at that value with a constraint, and solve a second time for a different goal — a tie-breaker. My tie-breaker was “use the higher brackets as little as possible,” which translates the vague human wish (“don’t jump around for no reason”) into something the solver can act on.
The result snapped into focus immediately. Same minimum tax, but now a plan that any advisor would recognize: convert hard into the 22% bracket in the early years before Social Security and required distributions start, then settle into the 12% bracket and coast. It went from “is this broken?” to “oh, that’s exactly the right strategy” without changing the lifetime tax by a single dollar.
When an optimization model gives you an ugly answer, check whether it’s wrong or just non-unique. If the objective is flat across many solutions, the solver’s pick is arbitrary, and the cure is a secondary objective — not a patch to the first one.
A smaller technique from the same build, for the optimization-minded: I modeled “filling tax brackets” without any integer variables. Each year’s taxable income gets split across bracket buckets, each bucket capped at its width, and because the solver is minimizing tax and the rates increase with each bucket, it fills the cheapest brackets first on its own. No binaries, no branch-and-bound, just a clean linear program that solves instantly. The math behind it is in the formulation post.
5. A correct model can still confuse the human reading it
Even after the schedule was clean, the first person to look at it (me) got tripped up. In one year the recommended conversion was smaller than the required minimum distribution, and my gut said that was wrong. It wasn’t. The RMD is forced money the IRS makes you take; the conversion is the optional extra you choose to move on top of it. Once Social Security and a growing RMD already fill the cheap brackets, there’s only a little room left to convert before you’d spill into a higher rate. Perfectly correct — and perfectly confusing until you stare at it.
That’s a reminder that the model’s job isn’t done when the math is right. The labels, the layout, and the explanation are part of the product. A correct answer nobody understands is a failed tool.
The last discipline was knowing what to leave out. The first version sizes conversions and reports the tax, but it does not yet model your living expenses or which account pays the tax bill. Those belong in a cash-flow layer that’s genuinely harder, and bolting a half-built version onto v1 would have made everything less trustworthy. Shipping a smaller thing that’s right beats shipping a bigger thing that’s vague.
The tools behind it
The engine is Python with PuLP driving the CBC solver — the same open-source engine OpenSolver was using inside Excel the whole time, so the move to the web didn’t even change the math under the hood. A few hundred variables, sub-second solves, and a tax table I can refresh in five minutes when the IRS publishes next year’s brackets.
If you want the investing side of this — what the model actually recommends and why — I wrote that up separately in the Roth conversion lessons the math kept teaching me, the underlying formulation is in the linear-program post, and the free tool itself is on the way. This post was about the part nobody sees: the unglamorous work of making an old model honest.
