shantanuo - 1 year ago 152

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

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

Source (Stackoverflow)