Calculating mortgage schedule

Question

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.

Answer 1

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