1

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.

user1075940
  • 111
  • 2

1 Answers1

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
Chris Degnen
  • 10,107
  • 1
  • 21
  • 36