Goal Seek in Python Instead of Excel

Goal Seek is Excel's "work backwards" button: set this cell to that value by changing that other cell. What price gives a 40,000 profit? What interest rate makes the payment 450 a month? It works one question at a time, through a dialog, and leaves no record of what was asked. In Python the same thing is root finding — find the input where result minus target equals zero — and scipy.optimize does it in one line, for one question or ten thousand. This page, part of Excel Formula Equivalents in pandas, shows the pattern on three typical questions and then applies it to an Excel model directly.

Prerequisites

pip install scipy pandas openpyxl

The first examples use a small product P&L written as a Python function: revenue is units times price, variable cost is units times unit cost, fixed costs of 60,000 come off, and 25% tax is paid on any profit. The same model exists as a workbook, pricing_model.xlsx, for the last step.

Step 1 — Solve for one input with brentq

Write the model as a function of the input you want to change, subtract the target, and ask brentq for the zero. It needs a bracket: two input values where the result is on opposite sides of the target:

from scipy.optimize import brentq

def net_profit(price, units=10_000, unit_cost=14.5, fixed=60_000, tax=0.25):
    operating = units * (price - unit_cost) - fixed
    return operating - max(0.0, operating * tax)

target = 40_000
price = brentq(lambda p: net_profit(p) - target, 15, 50, xtol=1e-9)
print(f"price for {target:,}: {price:.4f}")
print(f"check: net profit at that price = {net_profit(price):,.2f}")

brentq combines bisection with faster interpolation steps. As long as the function is continuous and the bracket really contains a sign change, it is guaranteed to find the answer, and it does so in a handful of evaluations. The result — about 25.83 — is the price at which this model earns exactly 40,000; rounding it to a real price is a business decision, which is why the check line matters. Excel's Goal Seek stops at a fixed tolerance and quietly accepts "close"; here xtol says how close you need.

Step 2 — When there is no answer

Goal Seek's "may not have found a solution" message is easy to click away. In Python, a bracket with no sign change raises an error, which is exactly what an automated job should do:

try:
    brentq(lambda p: net_profit(p, units=1_000) - target, 15, 50)
except ValueError as err:
    print("no solution in range:", err)

import numpy as np
prices = np.linspace(15, 50, 8)
print([round(net_profit(p, units=1_000)) for p in prices])

With only 1,000 units sold, even a price of 50 does not reach the target, so the function never crosses zero in the range, and brentq says so. Tabulating the function over the range, as the second part does, is the quickest way to pick a sensible bracket — or to see that the target is unreachable.

Step 3 — The rate behind a payment (Excel's RATE)

Some Excel functions are goal seeks. RATE finds the interest rate that makes a loan's payments match a given amount; there is no formula for it, only a search. The payment formula itself is simple, so solve it for the rate:

def payment(rate, n, principal):
    return principal * rate / (1 - (1 + rate) ** -n) if rate else principal / n

loan, months, pay = 20_000, 48, 480
monthly = brentq(lambda r: payment(r, months, loan) - pay, 1e-9, 0.1)
print(f"monthly rate {monthly:.6%}, annual {monthly * 12:.4%}")
print(f"payment check: {payment(monthly, months, loan):.4f}")

A 20,000 loan repaid at 480 a month over four years carries a rate of about 0.59% a month — roughly 7.1% a year, nominal. The same structure answers "how many months at this payment?" (NPER) by solving for n instead. PMT, NPV, IRR and XIRR Excel Functions in Python covers the functions that do have closed forms.

Step 4 — Goal seek every row of a table

Goal Seek works on one cell. A list of products, each with its own volume and costs, needs one answer per row — the price that gives each product a 30% net margin, say. Wrap the solve in a function and apply it:

import pandas as pd

products = pd.DataFrame({
    "product": ["Basic", "Plus", "Pro", "Niche"],
    "units": [12_000, 6_000, 2_500, 300],
    "unit_cost": [9.0, 16.0, 38.0, 55.0],
    "fixed": [40_000, 35_000, 30_000, 20_000],
})

def price_for_margin(row, margin=0.30):
    f = lambda p: net_profit(p, row.units, row.unit_cost, row.fixed) - margin * row.units * p
    try:
        return brentq(f, row.unit_cost, row.unit_cost * 20)
    except ValueError:
        return float("nan")

products["price_for_30pct"] = products.apply(price_for_margin, axis=1).round(2)
print(products.to_string(index=False))

The target here depends on the input itself — 30% of revenue, which grows with price — so it goes inside the function rather than being a fixed number. Rows with no solution come back as NaN instead of stopping the whole table; with 300 units and 20,000 of fixed costs, the niche product needs a price of about 203 — steep, but it exists. Seeing that number next to the others is often the real point of the exercise. For thousands of rows this is fast enough; each solve takes microseconds.

Step 5 — Goal seek the Excel model itself

When the model lives in a workbook and must stay there, the function being solved can be the workbook: write the input, recalculate, read the output. openpyxl cannot calculate, so each evaluation goes through headless LibreOffice. That is slow — around a second per evaluation — which is exactly why brentq's small number of steps matters:

import subprocess
import tempfile
from pathlib import Path
from openpyxl import load_workbook

calls = []

def model_net_profit(price):
    with tempfile.TemporaryDirectory() as tmp:
        wb = load_workbook("pricing_model.xlsx")
        wb["Inputs"]["B3"] = price
        src = Path(tmp) / "run.xlsx"
        wb.save(src)
        subprocess.run(["soffice", f"-env:UserInstallation=file://{tmp}/p", "--headless",
                        "--convert-to", "xlsx", "--outdir", f"{tmp}/out", str(src)],
                       check=True, capture_output=True, timeout=120)
        value = load_workbook(Path(tmp) / "out" / "run.xlsx", data_only=True)["Model"]["B7"].value
    calls.append(price)
    return value

price_xl = brentq(lambda p: model_net_profit(p) - target, 20, 35, xtol=1e-4)
print(f"price from the workbook: {price_xl:.4f} after {len(calls)} recalculations")

The answer agrees with the Python version to four decimal places, and it took only six recalculations — the same order of magnitude as Excel's own Goal Seek, but scripted and repeatable. On Windows with Excel, the same function can write and read the cells through xlwings, with Excel recalculating in memory, which is much faster per step. Run What-If Scenarios on an Excel Model with Python uses the same write–recalculate–read loop for scenario tables.

Common pitfalls

• A bracket without a sign change. brentq needs the result on both sides of the target; tabulate the function first.
• Discontinuous models. IFs, MAX(0, …), rounding and lookup tables make steps in the curve. brentq still finds a crossing if one exists, but a stepped model can jump over the target entirely.
• Several answers. A curve that crosses the target twice has two answers, and the bracket decides which you get. Narrow it deliberately.
• Tolerance on the wrong axis. xtol is how precisely the input is found; check the output against the target afterwards, as the examples do.
• Solving by recalculating Excel for every row. Translate the logic to Python when you need thousands of answers; keep the workbook route for one-off questions on a model you must not duplicate.

Conclusion

Goal Seek is root finding: write the result as a function of the input, subtract the target, and pass it to scipy.optimize.brentq with a bracket that contains a sign change. Check the answer, let failures raise, solve RATE-style questions the same way, and apply the solver per row for tables. When the model must stay in Excel, make the function write, recalculate and read the workbook — brentq needs few enough steps for that to be practical.

Frequently asked questions

What is the Python equivalent of Excel Goal Seek? Root finding with scipy.optimize: brentq(lambda x: model(x) - target, low, high) returns the input at which the model equals the target, provided the result is below the target at one end of the range and above it at the other.

Why does brentq say f(a) and f(b) must have different signs? The range you gave does not contain a crossing: the model is on the same side of the target at both ends. Evaluate the model over a wider range to find a bracket, or accept that the target cannot be reached.

How do I calculate Excel's RATE function in Python? RATE has no closed form, so solve for it: write the payment formula as a function of the rate and use brentq to find the rate at which the payment equals the known amount.

Can Python goal seek an Excel workbook directly? Yes. Make the function write the input with openpyxl, recalculate the workbook with headless LibreOffice or with Excel through xlwings, and read the output. brentq usually needs only a handful to a dozen or so evaluations.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions and features translated to Python.
• Replace Excel Solver with Python — optimising with constraints instead of hitting one target.
• FORECAST, TREND and SLOPE Equivalents in Python — fitting lines to data.
• Build a Loan Amortization Schedule in Excel — the payment formula in a full schedule.