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
- 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
|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
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.
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.
I have no idea how
309,85 were calculated. Please help.