Level 4 · Boss: what can we afford?

What-if questions run the formula backwards

So far the inputs were known and the payment was the answer. Planning questions turn it around: the payment is fixed by a budget, and the unknown is the loan, the rate or the term. Excel offers Goal Seek for this: set a cell to a target value by changing another cell. Knowing how it works lets you do the same in Python, and check a spreadsheet when the answer looks odd.

When there is a closed form

The loan amount can be solved for directly. Rearranging the payment formula:

pv = cap * (1 - (1 + r) ** -n) / r       # 103,451.12 for $2,000 at 6% over 60 months

This is Excel’s PV function. When a formula can be inverted, invert it: it is exact and instant.

When there isn’t: bisection

The rate cannot be isolated in the payment formula. Goal Seek handles this by trying values and narrowing in. The simplest reliable version is bisection: start with a range that surely contains the answer, test the middle, keep the half where the answer must be.

lo, hi = 0.0001, 0.5
while hi - lo > 1e-9:
    mid = (lo + hi) / 2
    if pmt(mid / 12, 60, 48_000) > 950:
        hi = mid
    else:
        lo = mid

It works because the payment always rises with the rate. Each step halves the range, so 30 steps take an interval of 0.5 below a billionth. The same loop finds a break-even price, the sales volume that covers a fixed cost, or the discount that keeps a margin.

Scenario tables

One answer invites “and what if the rate is higher?”. Answer it before it is asked with a grid of inputs and outputs. Build it as rows, then reshape if you want a grid:

table = pd.DataFrame(rows)
table.pivot(index="rate", columns="months", values="payment")

Common mistakes

• Swapping the bisection bounds. If a too-high payment moves lo, the loop drifts to the edge of the range and returns 0.5 or 0.0001. Check the answer by plugging it back in.
• A range that doesn’t contain the answer. Bisection only narrows; it can’t find what isn’t between lo and hi.
• Rounding inside the loop. Round only the final answer.
• One scenario presented as certain. Show the neighbours too.