I'm trying to make loan amortization schedule where Interest rate is calculated on the exact period dates. (installment dates taking into account weekends and public holidays) and with the first payment less after than 30 days. What I have - Loan Amount 130,550 Interest Rate 8.2% Tenor 240 month Disbursement date 10/16/2020 First Payment date 02/11/2020
With General PMT formula I'm getting the 1,108.3 for monthly payments , however the interest is calculated only for 17 days and is equal to 130,550 * 8.2%/365/*(02/11/2020-10/16/2020) as a result in the first payment the principal is amortized more than it should. So my loan is fully amortized at 239 payment. The more interest rate the earlier it is amortized. I tried to shorten the tenor, but it should be at least half to be normal.
this is how my excel is look like
I need to get 240 month amortization and all roundings should be corrected at the last payment. Any Ideas?