Newton-Raphson method in Excel: a step-by-step iteration table

The Newton-Raphson formula

xₖ₊₁ = xₖ − f(xₖ) ÷ f′(xₖ)

At the current guess xₖ, draw the tangent to the curve. Its slope is the derivative f′(xₖ), and it crosses zero at xₖ − f(xₖ) ÷ f′(xₖ). That crossing is the next guess. Near a simple root the method converges quadratically: the number of correct digits roughly doubles with every step, which is why a handful of rows is usually enough.

The first example is the equation Isaac Newton himself used to demonstrate the method, x³ − 2x − 5 = 0. Its derivative is 3x² − 2.

Building the iteration table

• B2: the start value, 2.
• C2: f(x): =B2^3-2*B2-5
• D2: f′(x): =3*B2^2-2
• E2: the Newton step: =B2-C2/D2
• B3: =E2, so each row starts where the previous one ended.

Fill row 3 down as far as you need. Column F shows the size of each step with =TEXT(ABS(E2-B2),"0.0E+00"), in scientific notation, because a format with four decimals cannot show changes that small. Stop when the step is below your tolerance and f(x) is close to zero.

Worked example: two Newton-Raphson iteration tables

Rows 2 to 6 solve x³ − 2x − 5 = 0 from the start value in B2 (yellow). Rows 9 to 14 solve the monthly rate of a 50,000 USD loan with 60 payments of 1,000 USD from a start of 2% in B9, using the exact derivative. Rows 16 to 21 hold the loan inputs and compare the result with Excel's RATE.

Worked example: two Newton-Raphson iteration tables

	A	B	C	D	E	F
1	k	xₖ	f(xₖ) = x³ − 2x − 5	f′(xₖ) = 3x² − 2	xₖ₊₁	Step size
2	0	2.0000	-1.0000	10.0000	2.1000	1.0E-01
3	1	2.1000	0.0610	11.2300	2.0946	5.4E-03
4	2	2.0946	0.0002	11.1616	2.0946	1.7E-05
5	3	2.0946	0.0000	11.1614	2.0946	1.6E-10
6	4	2.0946	0.0000	11.1614	2.0946	0.0E+00
7
8	k	rₖ (monthly)	f(r) = PV − loan	f′(r)	rₖ₊₁	Step size
9	0	2.00%	-15,239.11	-841,626	0.19%	1.8E-02
10	1	0.19%	6,666.98	-1,692,998	0.58%	3.9E-03
11	2	0.58%	505.05	-1,444,091	0.62%	3.5E-04
12	3	0.62%	3.50	-1,424,105	0.62%	2.5E-06
13	4	0.62%	0.00	-1,423,965	0.62%	1.2E-10
14	5	0.62%	0.00	-1,423,965	0.62%	3.5E-16
15
16	Loan amount (USD)				50,000
17	Monthly payment (USD)				1,000
18	Number of monthly payments				60
19	Excel's RATE for the same loan				0.62%
20	Gap between Newton (E14) and RATE				2.5E-16
21	Effective annual rate = (1 + r)¹² − 1				7.68%

Illustrative loan figures. Step sizes below about 1E-15 are floating-point noise and can differ slightly in Excel. Educational example, not financial advice.

Watching it converge

From x₀ = 2, f = −1 and f′ = 10, so the first step lands on 2.1. The step sizes then shrink from 1.0E-01 to 5.4E-03, 1.7E-05 and 1.6E-10: each one is roughly the square of the one before. After three steps x = 2.0945514815, correct to about ten digits, and f(x) is zero to the precision Excel shows. Bisection from the bracket [2, 3] would need more than 30 halvings for the same accuracy, though it is guaranteed to converge.

Solving a loan rate with Newton's method

The interest rate of a loan has no closed formula: PV = PMT × (1 − (1 + r)⁻ⁿ) ÷ r cannot be solved for r by algebra. Newton can. The root is f(r) = PMT × (1 − (1 + r)⁻ⁿ) ÷ r − loan, and its exact derivative is PMT × (n(1 + r)⁻ⁿ⁻¹ ÷ r − (1 − (1 + r)⁻ⁿ) ÷ r²).

For a 50,000 USD loan repaid with 60 payments of 1,000 USD, a deliberately poor start of 2% per month leaves the payments worth 15,239 USD less than the loan. The first step overshoots below the root, then the residual in column C drops to 0.00 within a few rows. The result, 0.62% per month, agrees with =RATE(60,-1000,50000) to many decimal places, because RATE runs the same kind of iteration internally, as does IRR for the root of NPV (see how to calculate IRR in Excel). As an effective annual rate that is 7.68%.

No derivative? Use a numerical slope

If the derivative is hard to write down, estimate it with a central difference: f′(x) ≈ (f(x + h) − f(x − h)) ÷ (2h), with a small h such as 1E-6 times the size of x. The error of this slope shrinks with h², so Newton keeps almost all of its speed. Much smaller h values lose digits to rounding. The secant method goes one step further and uses the slope through the last two guesses, with no derivative at all.

When Newton-Raphson fails

• Flat spots: where f′(x) is close to zero the tangent is almost horizontal and the next guess jumps far away. For x³ − 2x − 5, starting at 0.8165 (where 3x² − 2 = 0) breaks the method.
• Poor starts: a start far from the root can overshoot, oscillate or run to a different root.
• Several roots: Newton finds the one its start leads to, not necessarily the one you want. Cash flows with several sign changes can have more than one IRR.
• Repeated roots: at a double root convergence drops to linear.

Practical safeguards: start from a sensible guess, cap the number of iterations, check |f(x)| at the end and, if the method misbehaves, bracket the root with bisection first and let Newton finish.

Common mistakes

• A wrong derivative. Newton with a wrong f′ still moves, but slowly or in the wrong direction. Check f′ against a central difference at one point.
• Every row starting from x₀. Each row must take the previous row's result (=E2 in B3), or the table repeats the first step forever.
• Stopping on one criterion only. A tiny step with a large f(x) means the method has stalled. Check both the step and the residual.
• Starting where the slope is zero. Division by a near-zero f′ throws the next guess far away or returns #DIV/0!.
• Percent versus decimal. A start of 2 for a 2% rate is 200% per month. Enter rates as decimals (0.02) or format the cell as a percentage.
• Trusting a root without a plot. Chart f(x) over a range first. It shows how many roots there are and where a good start lies.