MUNI Bond Payment Calculator

Project Deep Dive

Municipal bond financing involves comparing payment structures across multiple term lengths simultaneously - something Excel can do, but not elegantly. The original spreadsheet packed all of it onto a single Proposal sheet: one loan-amount input cell feeding nine output formulas, so typing in a number produced every term and payment-frequency combination at once. The catch wasn't the math - it was reading a client-ready comparison out of that dense nine-cell grid on a laptop screen during a meeting, and then printing it without the spreadsheet's formatting falling apart.

What the spreadsheet did

The original workbook was a two-sheet proposal tool. A "Proposal" sheet held a single loan-amount input cell and nine output cells; a separate "Rates" sheet held an amortization factor table - precomputed payment-per-dollar-financed constants arranged in columns, one per loan term, with rows split into two amount tiers so larger loans priced off a different row of the same table. Each output cell ran a nested IF/AND formula: it tested which amount tier the input amount fell into, then multiplied the raw loan amount by the matching factor pulled from the Rates sheet with a direct cell reference. Nine formulas covered every combination of the available terms against monthly, quarterly, and annual payment frequency. Amounts below the lowest tier, in the narrow gap between the two tiers, or above the highest tier didn't resolve to a number at all - the formula's fallback branch returned the literal text "Call for Quote," the spreadsheet's built-in way of routing edge-case loan sizes to a phone call instead of a printed figure.

How we converted it

Rather than reimplementing the Rates sheet as a live lookup, we read the values each Proposal-sheet formula resolved to and hardcoded them as named JavaScript constants - a fair simplification, since the rates only change when the client renegotiates terms, not per transaction. The nested IF/AND boundary logic was ported condition-for-condition, including the exact numeric cutoffs and the "Call for Quote" fallback for out-of-range amounts, so the web app fails the same way the spreadsheet did instead of silently guessing at a number. We added a two-way-synced range slider next to the numeric input: dragging the slider updates the text field and vice versa, and both fire the same recalculation on every keystroke or drag tick, with the slider track's fill percentage computed live from the input's position between its minimum and maximum. Every result is rounded and formatted with a dollar sign and thousands separators, then written into the on-screen comparison grid and again into a hidden table used only when the client-facing PDF is generated.

What changed for the client

For the sales team, this replaced a multi-tab spreadsheet that had to be recalculated and re-copied for every client conversation with a single screen where moving one slider updates all three term comparisons at once. The print button - previously a source of clipped columns and broken formatting when the spreadsheet was sent to a printer - now generates a clean, branded PDF handout on demand, directly answering the formatting complaint that prompted the rebuild. What used to be "let me pull up the spreadsheet" became a polished comparison the sales team could run live, on a laptop, in front of the client.