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?

1 Answer 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

  • Can you show the formula getting 1107.11? Because Interest I'm calculating definitly with the same formula :) Commented Oct 20, 2020 at 14:31
  • Due to all the different lengths of the months, the simplest way to find the answer is to set up the spreadsheet as shown and change the payment amount until the balance is as small as possible, Then the trickiest part is setting up the month lengths. There is no simple formula, due to the varying month lengths. If only the first month were different, and all the following months were of average length you could use a formula like here, but that isn't the scheme you are aiming to use. Commented Oct 20, 2020 at 21:46
  • I think thats the exactly what I want, as only first payment will be with different period. after all will be in average 30 days. Thank you! Commented Oct 21, 2020 at 5:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .