Using Excel as a solver in Python or SQL

Question

Here is a simple calculation that I do in Excel. I would like to know if it could be 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

Column C is the approximate amount that the borrower may require to repay each month. All other cells next to C2 simply point to the first 200 release. After using the solver, as shown in the following image, I get the correct 705.20 release in column C.

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

This is what the final version looks like ...

I tried something like this, but it does not exit the loop and prints all the 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

, // numpy pandas. , . , .

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

:

    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

Answer 2

, ( Excel), , .

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

- n -th . - (7692). - m (0).

n -th (n-1) - :

, :

, .

---

, , , .

, , , .

( , ) . , .

, , , , . , .

Answer 3

:

from __future__ import print_function

"""
Formulas: http://mathforum.org/dr.math/faq/faq.interest.html
"""

def annuity_monthly_payment(P, n, q, i, debug = False):
    """
    Calculates fixed monthly annuity payment
    P   - amount of the Principal 
    n   - Number of years
    q   - the number of times per year that the interest is compounded
    i   - yearly rate of interest (for example: 0.04 for 4% interest)
    """
    if debug:
        print('P = %s\t(amount of the Principal)' %P)
        print('n = %s\t\t(# of years)' %n)
        print('q = %s\t\t(# of periods per year)' %q)
        print('i = %s %%\t(Annual interest)' %(i*100))
    return P*i/( q*(1 - pow(1 + i/q, -n*q)) )


### Given :
P = 7692
n = 1
q = 12
i = 18/100

print('M = %s' %annuity_monthly_payment(P=P, n=n, q=q, i=i, debug=True))

:

P = 7692        (amount of the Principal)
n = 1           (# of years)
q = 12          (# of periods per year)
i = 18.0 %      (Annual interest)
M = 705.2025054347173

Answer 4

/ SQL, SQL-:

create table loan (
  amount decimal(10,2),
  repay_months int,
  yearly_interest_rate decimal(4, 4)
);

insert into loan values (7692, 12, 0.18);

select amount * yearly_interest_rate/12 /
           (1 - pow(1 + yearly_interest_rate/12, -repay_months))
           as monthly_payment
from   loan;

:

monthly_payment
-----------------
705.2025054347173

SQL Fiddle.

, , (1, 2,...), :

create table months (month int);

insert into months -- one year of months
  values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
-- now some record multiplication to avoid long literal lists:    
insert into months -- multiply to cover 2 years
  select month + 12 from months;
insert into months -- multiply to cover 4 years
  select month + 24 from months;
insert into months -- multiply to cover 8 years
  select month + 48 from months;
insert into months -- multiply to cover 16 years
  select month + 96 from months;
insert into months -- multiply to cover 32 years
  select month + 192 from months;
-- OK, we have now months from 1 to 384 (= 32 years)

, :

select month,
       monthly_payment * (1 - pow(1 + monthly_interest_rate, month-repay_months)) 
                       / monthly_interest_rate
                       as loan_balance,
       monthly_payment * (1 - pow(1 + monthly_interest_rate, month-1-repay_months)) 
                       as interest,
       monthly_payment
from   months,
       (
        select amount,
               repay_months,
               yearly_interest_rate,
               yearly_interest_rate/12 as monthly_interest_rate, 
               amount * yearly_interest_rate/12 /
                   (1 - pow(1 + yearly_interest_rate/12, -repay_months))
                   as monthly_payment
        from   loan
       ) as loanX
where  month <= repay_months
order by 1;

:

+-------+--------------------+---------------------+-------------------+
| month | loan_balance       | interest            | monthly_payment   |
+-------+--------------------+---------------------+-------------------+
| 1     | 7102.177494565289  | 115.38              | 705.2025054347173 |
+-------+--------------------+---------------------+-------------------+
| 2     | 6503.507651549055  | 106.53266241847933  | 705.2025054347173 |
+-------+--------------------+---------------------+-------------------+
| 3     | 5895.8577608875785 |  97.55261477323582  | 705.2025054347173 |
+-------+--------------------+---------------------+-------------------+
| 4     | 5279.093121866177  |  88.43786641331367  | 705.2025054347173 |
+-------+--------------------+---------------------+-------------------+
| 5     | 4653.077013259458  |  79.18639682799265  | 705.2025054347173 |
+-------+--------------------+---------------------+-------------------+
| 6     | 4017.6706630236345 |  69.79615519889187  | 705.2025054347173 |
+-------+--------------------+---------------------+-------------------+
| 7     | 3372.7332175342744 |  60.265059945354515 | 705.2025054347173 |
+-------+--------------------+---------------------+-------------------+
| 8     | 2718.12171036258   |  50.590998263014114 | 705.2025054347173 |
+-------+--------------------+---------------------+-------------------+
| 9     | 2053.6910305833035 |  40.7718256554387   | 705.2025054347173 |
+-------+--------------------+---------------------+-------------------+
| 10    | 1379.2938906073389 |  30.805365458749552 | 705.2025054347173 |
+-------+--------------------+---------------------+-------------------+
| 11    |  694.7807935317352 |  20.689408359110082 | 705.2025054347173 |
+-------+--------------------+---------------------+-------------------+
| 12    |    0               |  10.421711902976027 | 705.2025054347173 |
+-------+--------------------+---------------------+-------------------+

SQL.

.

Answer 5

import numpy as np

for pmt in np.linspace(200, 800, 20):
    loan = 7692.00
    for n in range(1, 13):
        new_balance = loan + ((loan*(1+(0.18/12)))-loan) - pmt
        loan = new_balance
    print(round(pmt, 2), '->', round(loan,2))

, 12- , , 12 . , 705,26? , - .

200.0 -> 6588.45
231.58 -> 6176.62
263.16 -> 5764.8
294.74 -> 5352.97
326.32 -> 4941.14
357.89 -> 4529.31
389.47 -> 4117.49
421.05 -> 3705.66
452.63 -> 3293.83
484.21 -> 2882.0
515.79 -> 2470.18
547.37 -> 2058.35
578.95 -> 1646.52
610.53 -> 1234.69
642.11 -> 822.86
673.68 -> 411.04
705.26 -> -0.79
736.84 -> -412.62
768.42 -> -824.45
800.0 -> -1236.27

. , .

Answer 6

Python . -, - exit(), exit. :

loan_amount= 7692
interest = 18
months = 12

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

706, 705.20.

, python 705.20, , , . . .

Answer 7

Python , .

"""
    Calculate required monthly repayment for a given:
        - loan amount, and
        - annual interest rate, and
        - period of repayments in months

    You can nominate the accuracy required by adjusting the value of
        ACCURACY_AS_PARTS_OF_CENT. For example:
        - .01 = accurate to a dollar
        - .1  = accurate to 10 cents
        - 1   = accurate to cent
        - 100 = accurate to 100th of a cent
"""

# Set constants.
LOAN_AMOUNT = 7692
ANNUAL_INTEREST_PERCENT = 18
REPAY_MONTHS = 12
ACCURACY_AS_PARTS_OF_CENT = 1

loan_amount = int(LOAN_AMOUNT * 100 * ACCURACY_AS_PARTS_OF_CENT)
monthly_interest = float(ANNUAL_INTEREST_PERCENT / 100 / 12)
repay_guess_min = int((LOAN_AMOUNT / REPAY_MONTHS) - 1)
result_found = False
repayment_required = 0

for repay_guess in range(repay_guess_min, loan_amount):
    if result_found:
        break
    loan_balance = loan_amount
    for _ in range(REPAY_MONTHS):
        interest_to_add = loan_balance * monthly_interest
        loan_balance = loan_balance + interest_to_add - repay_guess
        if loan_balance <= 0:
            repayment_required = repay_guess / 100 / ACCURACY_AS_PARTS_OF_CENT
            result_found = True
            break

print('Required monthly repayment = $' + str(repayment_required))

Answer 8

. , , , amortization_tab ( PrettyTable CSV) : " rest_amount", " amount_can_be_payed_in_n_years", " years_to_pay_off". CSV-, , Excel . , /.

PS Python v3.5.1, Python.

from __future__ import print_function
import sys
import math
import io
import csv
import prettytable

# Formulas:   http://mathforum.org/dr.math/faq/faq.interest.html

description = {
    'P': 'amount of the principal',
    'i': 'annual interest rate',
    'n': 'number of years',
    'q': 'number of times per year that the interest is compounded',
    'M': 'fixed monthly payment',
    'k': 'number of "payed" payments',
}


def pr_debug(P=None, i=None, n=None, q=None, M=None, k=None, debug=False):
    if not debug:
        return

    columns = ['var','value', 'description']
    t = prettytable.PrettyTable(columns)
    t.align['var'] = 'l'
    t.align['value'] = 'r'
    t.align['description'] = 'l'
    t.padding_width = 1
    t.float_format = '.2'

    if P:
        t.add_row(['P', P, description['P']])
    if i:
        t.add_row(['i', i, description['i']])
    if n:
        t.add_row(['n', n, description['n']])
    if q:
        t.add_row(['q', q, description['q']])
    if M:
        t.add_row(['M', M, description['M']])
    if k:
        t.add_row(['k', k, description['k']])

    print(t.get_string() + '\n')


def annuity_monthly_payment(P, n, q, i, debug = False):
    """
    Calculates fixed monthly annuity payment
    P   - amount of the principal 
    n   - number of years
    q   - number of times per year that the interest is compounded
    i   - yearly rate of interest (for example: 0.045 for 4.5% interest)
    """
    pr_debug(P=P, n=n, q=q, i=i, debug=debug)

    i /= 100
    return round(P*i/( q*(1 - pow(1 + i/q, -n*q)) ), 2)


def rest_amount(P, M, k, q, i, debug = False):
    """
    Calculates rest amount after 'k' payed payments 
    P   - Principal amount
    M   - fixed amount that have been payed 'k' times
    k   - # of payments
    q   - # of periods (12 times per year)
    i   - yearly interest rate (for example: 0.04 for 4%)
    """
    pr_debug(P=P, M=M, k=k, q=q, i=i, debug=debug)

    i /= 100
    return round((P - M*q/i) * pow(1 + i/q, k) + M*q/i, 2)


def amount_can_be_payed_in_n_years(M, n, q, i, debug = False):
    """
    Returns the amount of principal that can be paid off in n years 
    M   - fixed amount that have been payed 'k' times
    n   - Number of years
    q   - # of periods (12 times per year)
    i   - yearly interest rate (for example: 0.04 for 4%)
    """
    pr_debug(M=M, n=n, q=q, i=i, debug=debug)

    i /= 100
    return round( M*(1 - pow(1 + i/q, -n*q) )*q/i, 2)


def years_to_pay_off(P, M, q, i, debug = False):
    """
    Returns number of years needed to pay off the loan 
    P   - Principal amount
    M   - fixed amount that have been payed 'k' times
    q   - # of periods (12 times per year)
    i   - yearly interest rate (for example: 0.04 for 4%)
    """
    pr_debug(P=P, M=M, q=q, i=i, debug=debug)

    i /= 100
    return round(-math.log(1 - (P*i/M/q)) / (q*math.log(1 + i/q)), 2)


def amortization_tab(P, n, q, i, M=None, fmt='txt', debug=False):
    """
    Generates amortization table 
    P   - Principal amount
    M   - fixed amount that have been payed 'k' times
    q   - # of periods (12 times per year)
    i   - yearly interest rate (for example: 0.04 for 4%)
    """
    assert any(fmt in x for x in ['txt', 'csv'])

    # calculate monthly payment if it not given
    if not M:
        M = annuity_monthly_payment(P=P, n=n, q=q, i=i)

    pr_debug(P=P, M=M, n=n, q=q, i=i, debug=debug)

    # column headers for the output table
    columns=['pmt#','beg_bal','pmt','interest','applied', 'end_bal']

    i /= 100

    beg_bal = P
    term = n*q

    if fmt.lower() == 'txt':
        t = prettytable.PrettyTable(columns)
        t.align = 'r'
        t.padding_width = 1
        t.float_format = '.2'
    elif fmt.lower() == 'csv':
        if sys.version_info >= (2,7,0):
            out = io.StringIO()
        else:
            out = io.BytesIO()
        t = csv.writer(out, quoting=csv.QUOTE_NONNUMERIC)
        t.writerow(columns)

    for num in range(1, term+1):
        interest = round(beg_bal*i/q , 2)
        applied = round(M - interest, 2)
        end_bal = round(beg_bal - applied,2)
        row = [num, beg_bal, M, interest, applied, end_bal]
        if fmt.lower() == 'txt':
            t.add_row(row)
        elif fmt.lower() == 'csv':
            t.writerow(row)
        beg_bal = end_bal

    if fmt.lower() == 'txt':
        return t.get_string()
    elif fmt.lower() == 'csv':
        return out.getvalue()

############################
P = 7692.0
n = 1
q = 12
i = 18
print(amortization_tab(P, n, q, i, debug=True))

print('#' * 80)
print('#' * 80)
############################
# another example
P = 100000.0
n = 5
q = 12
i = 3.5
k = 36
M = 1200
print(amortization_tab(P, n, q, i, M, fmt='csv', debug=True))
print('*' * 80)
print('Rest amount after %s payments:\t%s' %(k, rest_amount(P=P, M=M, k=k, q=q, i=i)))

:

+-----+---------+----------------------------------------------------------+
| var |   value | description                                              |
+-----+---------+----------------------------------------------------------+
| P   | 7692.00 | amount of the principal                                  |
| i   |      18 | annual interest rate                                     |
| n   |       1 | number of years                                          |
| q   |      12 | number of times per year that the interest is compounded |
| M   |  705.20 | fixed monthly payment                                    |
+-----+---------+----------------------------------------------------------+

+------+---------+--------+----------+---------+---------+
| pmt# | beg_bal |    pmt | interest | applied | end_bal |
+------+---------+--------+----------+---------+---------+
|    1 | 7692.00 | 705.20 |   115.38 |  589.82 | 7102.18 |
|    2 | 7102.18 | 705.20 |   106.53 |  598.67 | 6503.51 |
|    3 | 6503.51 | 705.20 |    97.55 |  607.65 | 5895.86 |
|    4 | 5895.86 | 705.20 |    88.44 |  616.76 | 5279.10 |
|    5 | 5279.10 | 705.20 |    79.19 |  626.01 | 4653.09 |
|    6 | 4653.09 | 705.20 |    69.80 |  635.40 | 4017.69 |
|    7 | 4017.69 | 705.20 |    60.27 |  644.93 | 3372.76 |
|    8 | 3372.76 | 705.20 |    50.59 |  654.61 | 2718.15 |
|    9 | 2718.15 | 705.20 |    40.77 |  664.43 | 2053.72 |
|   10 | 2053.72 | 705.20 |    30.81 |  674.39 | 1379.33 |
|   11 | 1379.33 | 705.20 |    20.69 |  684.51 |  694.82 |
|   12 |  694.82 | 705.20 |    10.42 |  694.78 |    0.04 |
+------+---------+--------+----------+---------+---------+
################################################################################
################################################################################
+-----+-----------+----------------------------------------------------------+
| var |     value | description                                              |
+-----+-----------+----------------------------------------------------------+
| P   | 100000.00 | amount of the principal                                  |
| i   |      3.50 | annual interest rate                                     |
| n   |         5 | number of years                                          |
| q   |        12 | number of times per year that the interest is compounded |
| M   |      1200 | fixed monthly payment                                    |
+-----+-----------+----------------------------------------------------------+

"pmt#","beg_bal","pmt","interest","applied","end_bal"
1,100000.0,1200,291.67,908.33,99091.67
2,99091.67,1200,289.02,910.98,98180.69
3,98180.69,1200,286.36,913.64,97267.05
4,97267.05,1200,283.7,916.3,96350.75
5,96350.75,1200,281.02,918.98,95431.77
6,95431.77,1200,278.34,921.66,94510.11
7,94510.11,1200,275.65,924.35,93585.76
8,93585.76,1200,272.96,927.04,92658.72
9,92658.72,1200,270.25,929.75,91728.97
10,91728.97,1200,267.54,932.46,90796.51
11,90796.51,1200,264.82,935.18,89861.33
12,89861.33,1200,262.1,937.9,88923.43
13,88923.43,1200,259.36,940.64,87982.79
14,87982.79,1200,256.62,943.38,87039.41
15,87039.41,1200,253.86,946.14,86093.27
16,86093.27,1200,251.11,948.89,85144.38
17,85144.38,1200,248.34,951.66,84192.72
18,84192.72,1200,245.56,954.44,83238.28
19,83238.28,1200,242.78,957.22,82281.06
20,82281.06,1200,239.99,960.01,81321.05
21,81321.05,1200,237.19,962.81,80358.24
22,80358.24,1200,234.38,965.62,79392.62
23,79392.62,1200,231.56,968.44,78424.18
24,78424.18,1200,228.74,971.26,77452.92
25,77452.92,1200,225.9,974.1,76478.82
26,76478.82,1200,223.06,976.94,75501.88
27,75501.88,1200,220.21,979.79,74522.09
28,74522.09,1200,217.36,982.64,73539.45
29,73539.45,1200,214.49,985.51,72553.94
30,72553.94,1200,211.62,988.38,71565.56
31,71565.56,1200,208.73,991.27,70574.29
32,70574.29,1200,205.84,994.16,69580.13
33,69580.13,1200,202.94,997.06,68583.07
34,68583.07,1200,200.03,999.97,67583.1
35,67583.1,1200,197.12,1002.88,66580.22
36,66580.22,1200,194.19,1005.81,65574.41
37,65574.41,1200,191.26,1008.74,64565.67
38,64565.67,1200,188.32,1011.68,63553.99
39,63553.99,1200,185.37,1014.63,62539.36
40,62539.36,1200,182.41,1017.59,61521.77
41,61521.77,1200,179.44,1020.56,60501.21
42,60501.21,1200,176.46,1023.54,59477.67
43,59477.67,1200,173.48,1026.52,58451.15
44,58451.15,1200,170.48,1029.52,57421.63
45,57421.63,1200,167.48,1032.52,56389.11
46,56389.11,1200,164.47,1035.53,55353.58
47,55353.58,1200,161.45,1038.55,54315.03
48,54315.03,1200,158.42,1041.58,53273.45
49,53273.45,1200,155.38,1044.62,52228.83
50,52228.83,1200,152.33,1047.67,51181.16
51,51181.16,1200,149.28,1050.72,50130.44
52,50130.44,1200,146.21,1053.79,49076.65
53,49076.65,1200,143.14,1056.86,48019.79
54,48019.79,1200,140.06,1059.94,46959.85
55,46959.85,1200,136.97,1063.03,45896.82
56,45896.82,1200,133.87,1066.13,44830.69
57,44830.69,1200,130.76,1069.24,43761.45
58,43761.45,1200,127.64,1072.36,42689.09
59,42689.09,1200,124.51,1075.49,41613.6
60,41613.6,1200,121.37,1078.63,40534.97

********************************************************************************
Rest amount after 36 payments:  65574.41