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.

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
2
  • 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 at 19:07
  • Closest I can get to the first interest payment is 160000*0.0237 (15/365 + 1/12) = 471.84 Sep 12 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.