2

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

enter image description here

I need to get 240 month amortization and all roundings should be corrected at the last payment. Any Ideas?

D Stanley
  • 145,656
  • 20
  • 333
  • 404
Tamuna Kochladze
  • 21
  • 2

1 Answers1

1

"I need to get 240 month amortization"

The closest with rounding is an instalment amount of 1107.11

There is no reason to make the first payment less; every month carries a different interest charge, so it would be completely arbitrary to make the first payment different.

With days, balance, interest and instalment amount in the columns.

enter image description here

Chris Degnen
  • 10,107
  • 1
  • 21
  • 36