Loans ... and Newton's Method

motivated by email from András
Suppose you borrow $A and have to pay it back in N months, with monthly payments:
P_{1}, P_{2}, ... P_{N}
(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 = P_{1}/(1+i) + P_{2}/(1+i)^{2} + P_{3}/(1+i)^{3} +
... + P_{N}/(1+i)^{N}
where P_{k} / (1+i)^{k} is the present value of the k^{th} 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:
 Define f(i) = P_{1}/(1+i) + P_{2}/(1+i)^{2} + P_{3}/(1+i)^{3} +
... + P_{N}/(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)
 We also have:
f '(i) = P_{1}/(1+i)^{2}  2 P_{2}/(1+i)^{3}  3 P_{3}/(1+i)^{4} 
...  N P_{N}/(1+i)^{N+1}
(That's the derivative of f(i) with respect to i.)
 We start with a guess at the monthly rate, say i_{0} and perform a bunch of Newton iterations, like so:
i_{1} = i_{0}  f(i_{0}) / f '(i_{0})
i_{2} = i_{1}  f(i_{1}) / f '(i_{1})
i_{3} = i_{2}  f(i_{2}) / f '(i_{2})
etc. etc.
 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.
Here's the spreadsheet:
Click on picture to download spreadsheet
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 ...
