Using Excel like solver in Python or SQL

Question

Here is a simple calculation that I do in Excel. I will like to know if it can be done python or any other language.

Loan amount 7692
Period : 12 months
Rate of interest 18 Per Annum
The formula in the B2 cell is =A1*18/100/12
The formula in the A2 cells is =A1+B2-C2

The column C is tentative amount the borrower may need to repay each month. All other cells next to C2 simply points to the first installment of 200. After using the solver as shown in the following image, I get the correct installment of 705.20 in the C column.

I will like to know if this calculation can be done using any scripting language like python (or SQL)

Here is how the final version looks like...

I tried something like this, but it does not exit the loop and prints all combinations.

loan_amount= 7692
interest = 18
months =12

for rg in range(700, 710):
    for i in range(months):
        x = loan_amount * interest / 100 / 12
        y = loan_amount + x - rg
        if x < 0: 
            print rg, i
            exit
        else:
            loan_amount = y

Answer 1

Well, you can solve it using numerical method (as Excel does), you can solve it with brute force by checking every amount with some step within some range, or you can solve it analytically on a piece of paper.

Using the following notation

L - initial loan amount = 7692
R - monthly interest rate = 1 + 0.18/12
m - number of months to repay the loan = 12
P - monthly payment to pay the loan in full after m months = unknown

is loan amount after the n-th month. is the initial loan amount (7692). is the loan amount after m months (0).

The main relation between n-th and (n-1)-th month is:

So, analytical formula turns out to be:

Now it should be fairly straight-forward to calculate it in any programming language.

For the given initial parameters

---

By the way, if you are modelling how the real bank works, it may be tricky to calculate it correctly to the last cent.

The answer that you get from precise analytical formulas like the one above is only approximate.

In practice all monthly amounts (both payment and interest) are usually rounded to the cent. With each month there will be some rounding error, which would accumulate and grow.

Apart from these rounding errors different months have different number of days and even though payments are the same for each month, the interest is usually calculated for each day of the month, so it varies from month to month. Then there are leap years with extra day, which also affects the monthly interest.