How to use goal seek to solve for the interest rate behind a loan payment in excel
- Step 1Write the payment formula longhand (no PMT) — In your payment cell, replace any
=PMT(...)with the explicit amortization formula:=P*r/(1-POWER(1+r,-n)), where the rate cell holds the monthly rater,Pis the principal, andnis the number of monthly payments. PMT is unsupported and returns NaN, so this rewrite is mandatory. - Step 2Upload the .xlsx — Drop the workbook onto the tool —
.xlsxonly, parsed in your browser by SheetJS so loan figures stay local. Goal Seek requires the Developer tier. - Step 3Set the payment cell as the target — Enter the payment cell in
targetCell(e.g.D6). It must hold the longhand amortization formula, with the rate cell as a single-cell reference inside it. - Step 4Set the target value to the quoted payment — Enter the monthly payment the lender quoted into
targetValue(e.g.450.00). This is the output you want the formula to match. - Step 5Set the rate cell as the input — Enter the monthly-rate cell in
inputCell(e.g.B4). Seed it with a plausible monthly rate like0.005(≈ 6% annual) so the search brackets sensibly. SetsheetNameif needed. - Step 6Run and annualise the result — Read the monthly rate from the report (six decimals). Multiply by 12 for the nominal annual rate, or compound it —
POWER(1+r,12)−1— for the effective annual rate. The tool returns a report; type the rate into your own model.
Loan rate Goal Seek setup
Options for solving the implied monthly rate. The payment cell must use the longhand amortization formula, not PMT.
| Option | Value | Notes |
|---|---|---|
targetCell | payment cell, e.g. D6 | Must hold =P*r/(1-POWER(1+r,-n)), not =PMT(...). |
targetValue | the quoted monthly payment | e.g. 450. The output you want to match. |
inputCell | monthly-rate cell, e.g. B4 | Seed ~0.005 (≈6% annual) so the bracket is sensible. |
sheetName | blank or your loan tab | Defaults to first sheet. |
PMT vs the longhand formula this tool needs
Why you must rewrite PMT. Standard amortization, monthly rate r, n payments, principal P.
| Approach | Formula | Works here? |
|---|---|---|
| Excel PMT | =PMT(B4, n, -P) | No — PMT is unsupported, returns NaN, search fails |
| Longhand amortization | =P*B4/(1-POWER(1+B4,-n)) | Yes — POWER and arithmetic are supported |
| Rate-in-cells variant | =C1*B4/(1-POWER(1+B4,-C2)) with P=C1, n=C2 | Yes — single-cell refs only |
| Range or lookup variant | anything with A1:A10 or VLOOKUP | No — ranges and lookups return NaN |
Reading the rate result
Convert the solver's monthly rate into the figures you actually compare loans on.
| From the report | Conversion | Meaning |
|---|---|---|
monthly rate r | as reported | Per-period rate the payment implies |
| nominal annual | r × 12 | Quoted-style APR (simple ×12) |
| effective annual (EAR) | POWER(1+r,12) − 1 | True annual cost with monthly compounding |
| residual | from report | How exactly the payment was matched (want < 1e-8) |
Cookbook
Implied-rate solves using the longhand amortization formula. Each shows the cell layout, the options, and the report.
Implied rate on a 36-month equipment loan
Borrow 15,000 over 36 months; lender quotes $456.33/month. Solve for the monthly rate using the longhand formula.
Sheet1: C1 = 15000 (principal P) C2 = 36 (n payments) B4 = 0.005 (input — monthly rate, seed) D6 = C1*B4 / (1 - POWER(1+B4, -C2)) Options: targetCell = D6 targetValue = 456.33 inputCell = B4 Report: ✓ converged after 41 iteration(s). Input cell: B4 = 0.005833 (monthly) Output cell: D6 = 456.330000 → ×12 = 0.0700 → 7.00% nominal annual.
Auto loan — compare two lenders
Same 20,000 / 60-month loan, two quotes. Solve each to compare true cost. Lender A quotes $377, Lender B quotes $387.
Layout: C1=20000 (P), C2=60 (n), B4=0.005 (rate),
D6 = C1*B4/(1-POWER(1+B4,-C2))
Lender A: targetValue = 377 → B4 = 0.003331 → 4.00% nominal
Lender B: targetValue = 387 → B4 = 0.004167 → 5.00% nominal
Report (Lender B):
✓ converged after 40 iteration(s).
Input cell: B4 = 0.004167
Output cell: D6 = 387.000000BNPL: short term, high implied rate
A 'pay in 6' plan: borrow 600, repay $108/month for 6 months. The implied rate is far higher than the marketing suggests.
Sheet1:
C1 = 600 (P)
C2 = 6 (n)
B4 = 0.02 (seed)
D6 = C1*B4/(1-POWER(1+B4,-C2))
Options:
targetCell = D6 · targetValue = 108 · inputCell = B4
Report:
✓ converged after 38 iteration(s).
Input cell: B4 = 0.041800
Output cell: D6 = 108.000000
→ ×12 = 50.2% nominal; EAR = POWER(1.0418,12)-1
≈ 63% effective. 'Interest-free' it is not.Wrong setup: leaving PMT in the formula
If the payment cell still uses PMT, the evaluator returns NaN and the search fails immediately. This is the most common mistake on this tool.
Sheet1:
D6 = PMT(B4, C2, -C1) (PMT unsupported!)
Options:
targetCell = D6 · targetValue = 456.33 · inputCell = B4
Report:
⚠ did not fully converge (best residual: 4.56e+2).
→ PMT evaluates to NaN. Rewrite D6 as:
C1*B4/(1-POWER(1+B4,-C2)) and re-run.Solving for the rate at a target total interest
Sometimes you want the rate where total interest equals a number. Total interest = (payment × n) − principal, written longhand.
Sheet1:
C1=10000 (P), C2=48 (n), B4=0.005 (rate)
D6 = C1*B4/(1-POWER(1+B4,-C2)) (payment)
D7 = (D6 * C2) - C1 (total interest)
Options:
targetCell = D7
targetValue = 1200
inputCell = B4
Report:
✓ converged after 43 iteration(s).
Input cell: B4 = 0.004690
Output cell: D7 = 1200.000000
→ Note: D6 must be recomputed in the file (it's the
dependency D7 reads); re-save so its cache is fresh.Edge cases and what actually happens
Payment cell still uses PMT
NaN / search failsPMT, PV, FV, RATE, NPER, and IRR are all unsupported — they return NaN and the search can't converge. You must rewrite the payment cell as the longhand amortization formula P*r/(1-POWER(1+r,-n)) using POWER and arithmetic. This is the single most common reason a loan solve fails here.
Rate seeded at zero
Division by zeroAt r = 0 the denominator 1 - POWER(1+r,-n) is zero, so the formula divides by zero and returns NaN at that search point. Seed the input cell with a small positive rate (e.g. 0.005) so the bracket avoids the zero-rate singularity.
Payment below the principal-only floor
No convergenceThere's a minimum payment (principal ÷ n at zero interest) below which no positive rate is consistent. Asking for a payment under that floor returns a best approximation with a non-zero residual — the quoted payment is mathematically impossible for the stated amount and term.
Total-interest solve reads a stale payment cell
Cached values usedIf you target a total-interest cell that references the payment cell, remember the solver recomputes only the target formula. The payment cell it depends on is read at its cached value and not recalculated as the rate varies — so the result can be wrong. Solve against the payment cell directly, or re-save so caches are fresh.
Negative rate result
Check the inputsIf the quoted payment is below the zero-interest payment (principal ÷ n), the only consistent rate is negative. The solver may report a small negative monthly rate. That usually signals a data-entry error in principal, term, or payment rather than a genuine sub-zero loan.
Annual vs monthly rate confusion
ExpectedThe solver returns the per-period (monthly) rate r, because the formula uses monthly compounding. Multiply by 12 for the nominal annual rate, or use POWER(1+r,12)-1 for the effective annual rate. Reporting the monthly figure as an APR understates the cost by 12×.
Range syntax inside the formula
NaN / search failsIf your amortization build sums a schedule with SUM(D2:D49) or similar, the range can't be parsed and returns NaN. Solve against the closed-form P*r/(1-POWER(1+r,-n)) instead of a row-by-row amortization schedule.
Result not written back to the file
By designThe output is a text report with the implied rate, not a re-saved workbook. Read the monthly rate, annualise it, and type it into your model. The original file is never modified.
Frequently asked questions
Why can't I just use PMT?
The built-in evaluator doesn't implement PMT (or PV, FV, RATE, NPER, IRR) — those functions return NaN, which stops the bisection search. To solve for a rate you must write the payment cell out longhand: =P*r/(1-POWER(1+r,-n)), using POWER and arithmetic, which the evaluator does support.
What's the exact formula to put in the payment cell?
For principal P, monthly rate cell r, and n monthly payments: =P*r/(1-POWER(1+r,-n)). Put P and n in their own cells and reference them as single cells. Then set the payment cell as targetCell, the quoted payment as targetValue, and the rate cell as inputCell.
Is the rate it returns monthly or annual?
Monthly — the per-period rate the amortization formula uses. Multiply by 12 for the nominal annual rate, or compute POWER(1+r,12)-1 for the effective annual rate (EAR), which accounts for monthly compounding and is the fairer comparison number.
Why does the solve fail when I seed the rate at 0?
At a zero rate the formula's denominator 1-POWER(1+r,-n) becomes zero, causing a divide-by-zero (NaN) at that point. Seed the input cell with a small positive rate like 0.005 so the search avoids the singularity.
Can I solve for total interest instead of the payment?
Yes — add a cell =(payment×n)-principal and target that. But that cell depends on the payment cell, which the solver won't recompute (only the target formula is recalculated). Either solve against the payment directly, or re-save the workbook so the payment cell's cache is current first.
Why is the implied BNPL rate so high?
Short terms amplify the effective rate. A small fee on a 6-week or 6-month plan annualises to a very large percentage. The solver shows it: a 'pay in 6' plan can imply 50%+ nominal and 60%+ effective annual once you reverse the math. That's exactly why solving for the rate is useful.
Do I need Excel installed?
No. The workbook is parsed in your browser by SheetJS and solved locally — no Excel, no add-in, no upload. Loan amounts and lender terms never leave your machine.
What if it won't converge?
The usual causes are PMT still in the formula (returns NaN), a payment below the principal-only floor (impossible for the term), or a zero-rate seed (divide by zero). The report shows the best approximation and residual so you can tell which it is.
Does it support a full amortization schedule?
Not via ranges. A row-by-row schedule summed with SUM(D2:D49) uses range syntax, which isn't parsed and returns NaN. Use the closed-form payment formula instead — it gives the same implied rate without a schedule.
Will it edit my spreadsheet with the answer?
No — it returns a text report. Read the implied monthly rate, annualise it, and enter it into your model yourself. To freeze recalculated cells into values afterward, use formula-to-value.
What tier do I need?
Developer tier. Developer accounts get up to 500 MB files and unlimited rows. Free, Pro, and Pro-media tiers are blocked from Goal Seek.
How do I check my formula is supported before running?
Run the formula explainer on the payment cell to confirm it uses only supported functions (POWER, arithmetic) and single-cell references. If it flags PMT or a range, rewrite the cell before solving.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.