Loans ... and Newton's Method
motivated by e-mail from András

Suppose you borrow \$A and have to pay it back in N months, with monthly payments: P1, P2, ... PN
(interest being charged on the remaining balance, after each payment).

The relationship between the loan amount A, the payments and the monthly interest rate i is like so:

[1]         A = P1/(1+i) + P2/(1+i)2 + P3/(1+i)3 + ... + PN/(1+i)N

where Pk / (1+i)k is the present value of the kth payment.

If (as is often the case) all the payments are the same, say P, then [1] can be rewritten:

[2]         A = P[ 1/(1+i) + 1/(1+i)2 + 1/(1+i)3 + ... + 1/(1+i)N ] = P [1 - (1+i)-N] / i

The problem, either with [1] or [2], is to calculate i, given the loan amount A and the payments and, to do this, we use Newton's Method.

>Haven't we done that before?
Newton's Method? Yes, here, however, in this case we have (possibly) different monthly payments.
First note that there is no magic formula to solve for i, hence the need to ask Newton.
This is what we'll do:

1. Define f(i) = P1/(1+i) + P2/(1+i)2 + P3/(1+i)3 + ... + PN/(1+i)N - A
(Note that, if we had the correct value for i, then f(i) would be 0 and [1] would be satisfied)
2. We also have: f '(i) = -P1/(1+i)2 - 2 P2/(1+i)3 - 3 P3/(1+i)4 - ... - N PN/(1+i)N+1
(That's the derivative of f(i) with respect to i.)
3. We start with a guess at the monthly rate, say i0 and perform a bunch of Newton iterations, like so:
i1 = i0 - f(i0) / f '(i0)
i2 = i1 - f(i1) / f '(i1)
i3 = i2 - f(i2) / f '(i2)
etc. etc.
4. If our initial guess wasn't way off the mark, then the Newton iterates will converge to some final "correct" value for the monthly interest rate.

You type in a bunch of monthly Payments (column B) and the Loan Amount (cell F2) then some initial guess in cell F1.
Then click the Start button to start the iterations with your initial guess (expressed as a "monthly" rate).
Then you keep clicking the ask Newton button to get the sequence of iterates.
It's great fun!

>And it's accurate, right?
I think so. Besides, it's fun to see how quickly the Newton iterates converge, to make f(i) = 0

>Here you say i and on the spreadsheet you say Rate. That's confusing, eh?
Note that, in [1] and [2], only 1+i occurs, so it's convenient to play with Rate = 1+i.

>zzzZZZ
Yeah, I figured that'd be your reaction ...