Использование Excel как решатель в Python или SQL

Я думаю, что эти табличные/векторные/матричные анализы идеально подходят для 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

Ну, вы можете решить ее численным методом (как это делает 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-го месяца.  — начальная сумма кредита (7 692).  – сумма кредита через m месяца (0).

Основное соотношение между n-м и (n-1)-м месяцем таково:

Итак, аналитическая формула получается:

Кстати, если вы моделируете, как работает реальный банк, может быть сложно правильно рассчитать его до последнего цента.

Ответ, который вы получаете из точных аналитических формул, подобных приведенной выше, является лишь приблизительным.

На практике все месячные суммы (как выплаты, так и проценты) обычно округляются до цента. С каждым месяцем будет какая-то ошибка округления, которая будет накапливаться и расти.

Помимо этих ошибок округления, разные месяцы имеют разное количество дней, и хотя платежи одинаковы для каждого месяца, проценты обычно рассчитываются для каждого дня месяца, поэтому они варьируются от месяца к месяцу. Затем идут високосные годы с дополнительным днем, что также влияет на месячный процент.

Код:

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

Поскольку в заголовке/теге также упоминается 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.

Если вы хотите получить полный график погашения, идея состоит в том, чтобы сначала создать таблицу с последовательными номерами месяцев (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.

Используемая формула приведена и получена в этой статье Википедии.

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

У меня был похожий вопрос с использованием линейного программирования. Может стоит проверить.

В вашем коде 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, это, безусловно, возможно. Однако код будет более сложным, и для его написания потребуется немало усилий. Электронные таблицы кажутся более подходящими для этой работы.

Решил настроить под себя. Так что это последняя версия, которую я придумал, которая теперь включает функцию "amortization_tab" для печати таблицы амортизации (форматы PrettyTable и CSV) и другие полезные функции: "остаточная_сумма >", "сумма_может_выплачиваться_в_n_годах", "годы_до_выплаты_в_годах". Я добавил формат 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's 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

Простой метод грубой силы в 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))