shantanuo - 2 years ago 358
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.

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

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download