Mortgage#

A real estate mortgage is a loan from the bank to the customer that must be used to purchase real estate. The amount borrowed is typically large and the term is long (usually between 15 and 30 years). The payment frequency of real estate mortgages is almost always on a monthly basis.

Let’s define:

L - the value of the loan,
R - the monthly payment on the loan,
i - annual rate of interest on the loan,
j - the monthly rate of interest on the loan (equal to i/12).

The customer pays the same amount every month for the given term, so we can use an annuity certain (ann).

The bank pays the value of the loan to the customer now. The customer pays the monthly payments over a certain period.

The most important equation is that these two values must equal:

L = R * ann

Let’s recall that:

ann = (1 - v ** n)/j

The bank is earning on this contract by setting the interest rate high enough.

Modelling#

Input#

Let’s build an amortization schedule for a €100 000 real estate mortgage with monthly payments at 10% interest convertible monthly and a term of 30 years.

We will put this information into the model point set.

model.py#

main = ModelPointSet(data=pd.DataFrame({
  "id": [1],
  "loan": [100_000],
  "interest_rate": [0.1],
  "term": [360]
}))

Model#

In the model point set, we have the value of the yearly interest rate, so let’s create a variable for a monthly interest rate. The rate does not change value from month to month so it’s a time-independent variable. The function has no t parameter.

model.py#

@variable()
def monthly_interest_rate():
    return main.get("interest_rate") / 12

The monthly rate is the yearly rate divided by 12.

Now we need to determine the value of the regular monthly payment.

model.py#

@variable()
def payment():
    L = main.get("loan")
    n = main.get("term")
    j = monthly_interest_rate()
    v = 1 / (1 + j)
    ann = (1 - v ** n) / j
    return L / ann

We now know the value of the monthly payment which further splits into principal and interest. The principal is the value that is deducted from the balance. Interest is the part that is the bank’s earnings.

Let’s start with the interest. The interest is the outstanding balance multiplied by the monthly interest rate. We haven’t yet defined the model variable for balance but we will shortly.

model.py#

@variable()
def interest(t):
    if t == 0:
        return 0
    return balance(t-1) * monthly_interest_rate()

The principal is whatever is left from the monthly payment after deducting interest.

model.py#

@variable()
def principal(t):
    if t == 0:
        return 0
    return payment() - interest(t)

At the start, the value of the balance amounts to the value of the loan. Afterwards, it decreases by the value of the principal.

model.py#

@variable()
def balance(t):
    if t == 0:
        return main.get("loan")
    else:
        return balance(t-1) - principal(t)

We now have all components of the amortization schedule, so let’s take a look at the results.

Results#

Let’s take a look at the subset of the results - the first and last year of the loan repayment.

  t  monthly_interest_rate  payment   balance  interest  principal
             0.008333   877.57 100000.00      0.00       0.00
             0.008333   877.57  99955.76    833.33      44.24
             0.008333   877.57  99911.15    832.96      44.61
             0.008333   877.57  99866.18    832.59      44.98
             0.008333   877.57  99820.82    832.22      45.35
             0.008333   877.57  99775.09    831.84      45.73
             0.008333   877.57  99728.98    831.46      46.11
             0.008333   877.57  99682.48    831.07      46.50
             0.008333   877.57  99635.60    830.69      46.88
             0.008333   877.57  99588.32    830.30      47.27
             0.008333   877.57  99540.65    829.90      47.67
             0.008333   877.57  99492.59    829.51      48.07
             0.008333   877.57  99444.12    829.10      48.47

 ...

             0.008333   877.57   9981.95     89.75     787.82
             0.008333   877.57   9187.56     83.18     794.39
             0.008333   877.57   8386.55     76.56     801.01
             0.008333   877.57   7578.86     69.89     807.68
             0.008333   877.57   6764.45     63.16     814.41
             0.008333   877.57   5943.25     56.37     821.20
             0.008333   877.57   5115.20     49.53     828.04
             0.008333   877.57   4280.26     42.63     834.94
             0.008333   877.57   3438.36     35.67     841.90
             0.008333   877.57   2589.44     28.65     848.92
             0.008333   877.57   1733.45     21.58     855.99
             0.008333   877.57    870.32     14.45     863.13
             0.008333   877.57     -0.00      7.25     870.32

The loan starts with a balance of €100 000. Each month we pay the same amount of €877.57. At the beginning of the term, the interest is very high and the principal is low. In the end, it’s the other way around. The balance is exactly zero after 360 months.