Loan repayment calculations and high school algebra

FV = (PMT*(1+RATE*TYPE)*(1-(1+RATE)^NPER)/RATE) – PV*(1+RATE)^NPER

This is the formula for the “Future Value” (FV) of an investment assuming an initial investment (PV), constant repayments (PMT) and a constant interest rate (RATE) over a fixed term of months (NPER). When “TYPE” is zero, loan repayments are made in advance. When “TYPE” is 1, loan repayments are made in arrears.

You can algebraically rearrange the formula to give an expression that calculates PMT.

You can also rearrange the formula to give an expression that calculates PV.

Both of these just require simple high-school algebra.

But you CAN’T rearrange the formula to give an expresion that calculates RATE or NPER.

To work out these figures, you have to use an iterative method where you “plug in” a guess of what you think the rate or period should be, evaulate the result, and adjust your guess accordingly.

If you use some smart iterative methods, you should only have to loop about 5 times. The simplest way is given two guess / result pairs (x1, y1) and (x2, y2), and wanting to find the “x” which will result in “y”, your next best guess is to try x = y*(x1-x2)/(y1-y2).

In subsequent iterations, discard the (x,y) pair furthest from the desired result, and repeat the process with the new results.