Solve Implied Loan & Annuity Interest Rate from Payment, Principal, and Term

Why loan interest rate has no algebraic closed form

The present-value annuity equation PV * r / (1 - (1 + r)^(-N)) = Payment is a degree-(N + 1) polynomial in (1 + r), so no radical formula exists for general N >= 5. Spreadsheet engines solve it iteratively from a linear Taylor seed r_0 = 2 * (N * Payment / PV - 1) / (N + 1).

Evaluating three analytic Newton-Raphson updates inside LET converges to 6+ decimal places across standard consumer, auto, and equipment-finance APRs, while an explicit N * Payment = PV check handles 0% promotional financing without dividing by zero.

3-Step Newton-Raphson Periodic Rate Solver

Start from rate_0 = 2 * (nper * pmt_amt / pv_amt - 1) / (nper + 1) and apply three Newton steps f(r) / f'(r), guarding zero-interest loans when ABS(nper * pmt_amt - pv_amt) < 0.0001.

=LET(pv_amt, B2, nper, B3, pmt_amt, B4, rate_0, 2 * (nper * pmt_amt / pv_amt - 1) / (nper + 1), res_0, pv_amt * rate_0 / (1 - (1 + rate_0) ^ (-nper)) - pmt_amt, slp_0, pv_amt * ((1 - (1 + rate_0) ^ (-nper)) - rate_0 * nper * (1 + rate_0) ^ (-nper - 1)) / ((1 - (1 + rate_0) ^ (-nper)) ^ 2), rate_1, rate_0 - res_0 / slp_0, res_1, pv_amt * rate_1 / (1 - (1 + rate_1) ^ (-nper)) - pmt_amt, slp_1, pv_amt * ((1 - (1 + rate_1) ^ (-nper)) - rate_1 * nper * (1 + rate_1) ^ (-nper - 1)) / ((1 - (1 + rate_1) ^ (-nper)) ^ 2), rate_2, rate_1 - res_1 / slp_1, res_2, pv_amt * rate_2 / (1 - (1 + rate_2) ^ (-nper)) - pmt_amt, slp_2, pv_amt * ((1 - (1 + rate_2) ^ (-nper)) - rate_2 * nper * (1 + rate_2) ^ (-nper - 1)) / ((1 - (1 + rate_2) ^ (-nper)) ^ 2), IF(ABS(nper * pmt_amt - pv_amt) < 0.0001, 0, ROUND(rate_2 - res_2 / slp_2, 6)))

Round-Trip Payment Verification with PMT

Feed the solved periodic rate back into -PMT(E2, B3, B2) to confirm that the implied payment matches the contractual quote to the cent.

=ROUND(-PMT(E2, B3, B2) - B4, 2)

Further reading

• Microsoft Support — PMT function