Using Excel like solver in Python or SQL

Asked 26/12, 2015 at 6:3 Answered 8/1, 2016 at 19:33

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

Percussion answered 26/12, 2015 at 6:3 Comment(1)

Ofcourse it can be done using a scripting language. Yes python and R can do this as well – Antibiotic 26/12, 2015 at 6:9

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

Falcone answered 8/1, 2016 at 19:33 Comment(2)

simple and elegant. I wish I could offer bounty for this. – Percussion 9/1, 2016 at 10:59

I wish I had seen the question a day earlier. ;) – Falcone 9/1, 2016 at 22:37

Well, you can solve it using numerical method (as Excel does), you can solve it with brute force by checking every amount with some step within some range, or you can solve it analytically on a piece of paper.

Using the following notation

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

$L_{n}$ is loan amount after the n-th month. $L_{0}$ is the initial loan amount (7692). $L_{m}$ is the loan amount after m months (0).

The main relation between n-th and (n-1)-th month is:

$L_{n} = L_{n-1} * R - P$

So, analytical formula turns out to be:

$P = L * \frac{R^{m}}{\sum_{k=0}^{m-1}R^{k}} = L * R^{m} * \frac{R-1}{R^{m}-1}$

Now it should be fairly straight-forward to calculate it in any programming language.

For the given initial parameters

$R = 1 + \frac{0.18}{12} = 1.015$

$P = 7692 * 1.015^{12} * \frac{1.015-1}{1.015^{12}-1}\approx 705.2025054$

By the way, if you are modelling how the real bank works, it may be tricky to calculate it correctly to the last cent.

The answer that you get from precise analytical formulas like the one above is only approximate.

In practice all monthly amounts (both payment and interest) are usually rounded to the cent. With each month there will be some rounding error, which would accumulate and grow.

Apart from these rounding errors different months have different number of days and even though payments are the same for each month, the interest is usually calculated for each day of the month, so it varies from month to month. Then there are leap years with extra day, which also affects the monthly interest.

Earhart answered 3/1, 2016 at 5:57 Comment(1)

That's a very nice answer, that I keep at hand for the day I will want to understand how loans computation works ! Thanks! – Ashjian 7/1, 2016 at 14:7

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

Falcone answered 8/1, 2016 at 19:33 Comment(2)

simple and elegant. I wish I could offer bounty for this. – Percussion 9/1, 2016 at 10:59

I wish I had seen the question a day earlier. ;) – Falcone 9/1, 2016 at 22:37

Code:

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

Output:

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

Omer answered 3/1, 2016 at 20:54 Comment(0)

As the title/tag also mentioned SQL, I will post an SQL solution:

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;

Result:

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

SQL Fiddle.

If you want to get the whole amortization schedule, then an idea would be to first create a table with sequential month numbers (1, 2, ...), enough to cover for the longest loan pay-off duration you would have data for:

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)

Then use the following query, which has the above mentioned query as sub-select:

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;

This produces the following output:

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

Here is the SQL fiddle.

The formula used is provided and derived in this Wikipedia article.

Wilburn answered 5/1, 2016 at 21:45 Comment(2)

Thanks. Is it possible to create a table as shown in image-2 using SQL? – Percussion 6/1, 2016 at 14:46

Yes, I have added this to my answer. – Wilburn 6/1, 2016 at 16:58

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

The first column shows what the equal 12 month payment would be and the right column shows what the balance would be after 12 months. See how the balance approaches zero around 705.26? That indicates zero is somewhere around there.

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

I had a similar question using linear programming. Might be worth checking out.

Bess answered 8/1, 2016 at 8:30 Comment(0)

Your python code has some problems. For one thing, the command to exit is exit(), not exit. Here is a revised version:

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

This prints 706, which is the nearest whole number approximation of 705.20.

If you want python code that prints exactly 705.20, that is certainly possible. However the code will be more complex and take quite a bit of effort to write. Spreadsheets seem better suited for this job.

Hogtie answered 26/12, 2015 at 8:47 Comment(5)

It returns 5 values from 705 to 709 and not just 706 as mentioned. – Percussion 27/12, 2015 at 5:10

What version of python are you using? I adjusted the code, so now it works on both python 2.7.6 and 3.3.3. If it is returning more than one value, then for some reason exit() doesn't seem to be working. Did you make sure to get the parenthesis on exit()? If you just type exit then it will behave as you said. – Hogtie 28/12, 2015 at 15:47

Oh I misread, I thought you were using integer iteration and claiming difficulty in getting floats to print out. My bad. – Retentive 28/12, 2015 at 15:50

Since this appears to be dollar amounts, iterating by 1 cent should be a good enough approximation. – Retentive 28/12, 2015 at 15:51

@Untitled123: Yes, that is a good idea. Also the range would need to be increased, maybe from 1 to 100000 or something. But first we need to get the basic routine working. – Hogtie 28/12, 2015 at 15:58

A simple brute force approach in Python with the option to determine the level of accuracy you want.

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

Keesee answered 3/1, 2016 at 8:30 Comment(0)

I decided to tune it up for myself. So he is the last version i came up with, which now includes a "amortization_tab" function for printing an amortization table (PrettyTable and CSV formats) and another useful functions: "rest_amount", "amount_can_be_payed_in_n_years", "years_to_pay_off". I have added CSV format, so one can generate an initial calculation, import it into Excel and continue there. So now it's a more or less complete library for annuity loan/mortgage math.

PS it's been tested with Python v3.5.1, but it should also theoretically work with another Python versions.

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

Output:

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

Omer answered 4/1, 2016 at 22:43 Comment(4)

It is a pretty looking amortisation table, but usually banks calculate monthly interest as AnnualRate / 365 * DaysPerMonth, so if you want to get precise answer you'd better take this into account. While you are at it don't forget about leap years as well. – Earhart 5/1, 2016 at 12:58

Thank you for your hint! But as far as i know it doesn't apply for annuity loans/mortgages. And my goal was to do the math for annuity mortgages. – Omer 5/1, 2016 at 17:22

If i'm correct banks do the calculations for annuity mortgages depending on the number of compounds per year (either 12 or 4 per year), so the account balance doesn't change during that period. Therefore it doesn't matter how many days do we have in the particular month or year (leap year problem). – Omer 5/1, 2016 at 17:52

After all, it is up to the bank how to calculate the interest and there can be banks that do it as you describe. In my very limited personal experience all banks were calculating interest for each day of the month, so the final monthly interest was different for different months. Quite often it doesn't matter much, you just need to keep in mind that these generic formulas are approximate. – Earhart 6/1, 2016 at 0:34

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags