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.