shantanuo shantanuo - 5 months ago 74
SQL Question

Using Excel like solver in Python or SQL

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.

excel goal seak

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...

enter image description here

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

I think these tabular/vector/matrix type analyses are perfect for numpy and pandas. You often can write more compact code that is also easy to read. See if you agree.

import numpy as np
import pandas as pd

def mpmt(amt, i, nper):
    """
    Calculate the monthly payments on a loan/mortgage
    """
    i = i/12  # convert to monthly interest
    i1 = i + 1  # used multiple times in formula below
    return amt*i1**nper*i/(i1**nper-1)

def ipmt(amt, i, per, nper):
    """
    Calculate interest paid in a specific period, per, of a loan/mortgage
    """
    i = i/12  # convert to monthly interest
    i1 = i + 1  # used multiple times in formula below
    return (amt*i*(i1**(nper+1)-i1**per))/(i1*(i1**nper-1))

def amorttable(amt, i, nper):
    """
    Create an amortization table for a loan/mortgage
    """
    monthlypmt = mpmt(amt, i, nper)

    # the following calculations are vectorized
    df = pd.DataFrame({'month':np.arange(1, nper+1)})
    df['intpaid'] = ipmt(amt, i, df['month'], nper)
    df['prinpaid'] = monthlypmt - df['intpaid']
    df['balance'] = amt
    df['balance'] -= np.cumsum(df['prinpaid'])
    return df


print(amorttable(7692, .18, 12).round(2))

Here's the result:

    month  intpaid  prinpaid  balance
0       1   115.38    589.82  7102.18
1       2   106.53    598.67  6503.51
2       3    97.55    607.65  5895.86
3       4    88.44    616.76  5279.09
4       5    79.19    626.02  4653.08
5       6    69.80    635.41  4017.67
6       7    60.27    644.94  3372.73
7       8    50.59    654.61  2718.12
8       9    40.77    664.43  2053.69
9      10    30.81    674.40  1379.29
10     11    20.69    684.51   694.78
11     12    10.42    694.78    -0.00