# Calculating mortgage schedule

I am trying to figure out formulas used in my friend's history of payments unfortunately I can't figure out how bank calculates some of them.

All values in tables represent real payments history of that loan I am trying to find formulas to calculate them

Variables:

• Loan Amount : 160000 PLN (Polish Złoty)
• Interest rate : 2,37%
• Loan Term : 120 months
• Payment is made each month on 15th or next working day.
• Reference rate is updated every 6 months.
• Year has always 365 days
• Banks started charging interests from 2nd of October 2020
• First payment 16th of November 2020 (because 15/11/2020 was sunday)

Knowing this stuff I was able to calculate monthly payment using PMT formula in excel

`````` PMT(Interest Rate/ 12;120;Loan Amount) =  1498,88
``````
Payment date Rate Payment Interests Principal Balance Comment
16/11/2020 0,0237 1498,88 471,51 1031,37 158968,63 15/11/2020 is Sunday

To calculate Interest part I came up with this formula :

``````d = Days difference between 2/10/2020 and 16/11/2020 -> 45
Loan Amount * Interest Rate * (d /365) = 471.51
``````

Next payments

Payment date Rate Payment Interest Principal Balance Comment
15/12/2020 0,0237 1498,88 309,66 1189,22 157779,41 30 days
15/01/2021 0,0237 1498,88 317,59 1181,29 156598,12 31 days

Here is another thing that I don't know how to calculate.

• 02/10/2020 to 16/11/2020 => 45 days
• 16/11/2020 to 15/12/2020 => 29 days but I need 30 to get correct interest
• 15/12/2020 to 15/01/2021 => 30 days but I need 31 to get correct interest

Maybe I would be able to finally figure how data difference is calculate with more data, but there is another problem I am unable to solve.

At `21.01.2021` the interest rate drop by 0.05%, because bank became owner of the property and because of that they drop the rate.

Payment date Rate Payment Interest Principal Balance Comment
15/02/2021 0,0232 1496,81 309,85 1186,96 155411,16

I have no idea how `1496,81` and `309,85` were calculated. Please help.

Calculating the charge for the extended first period by the method used here

``````principal      p = 160000
monthly rate   r = 0.0237/12
no. months     n = 120
``````

2nd Oct to 15th Oct 2020 is 13 days

``````one month's interest  m1 = r p = 316

13 day's interest    d13 = (p + m1)*(0.0237/365)*13 = 135.32

1st interest charge (Nov)   c1 = m1 + d13 = 451.32
``````

Reset principal to `s` and calculate payment amount `d` for an annuity due so that the November repayment is applied straight away.

``````s = p + c1 = 160451.32

d = (r (1 + r)^(n - 1) s)/((1 + r)^n - 1) = 1500.14
``````

This does not match the OP's payment amount because the OP's figure does not include an extended first period. I.e. calculating an ordinary annuity payment

``````d = (r (1 + r)^n p)/((1 + r)^n - 1) = 1498.88
``````

Matching the bank's figures

1496.81 and 309.85 can be obtained thus

``````p = 156598.12   (January balance)
r = 0.0232/12
n = 117
``````

With an ordinary annuity formula

``````d = (r (1 + r)^n p)/((1 + r)^n - 1) = 1496.81
``````

The February interest charge

``````p (6*0.0237 + 25*0.0232)/365 = 309.85
``````
• At first I came up with exactly same values, but they are not what bank actually charged. All values in tables represent a real payments history of that loan. Sep 11, 2021 at 19:07
• Closest I can get to the first interest payment is `160000*0.0237 (15/365 + 1/12) = 471.84` Sep 12, 2021 at 11:57