Calculating the monthly payment on loans with dates can be tricky, unless the loan assumes all months have 30 days and there are 12 months in a year (30/360). In that case you can use the Excel PMT function.
It is more difficult however to calculate a monthly payment that will pay off a loan with the same payment each month if the loan uses the actual days in each month to calculate interest (Actual/365 or Actual 360). Simple interest loans are good example of loans that require actual days in each month. The problem is not only do months not have the same number of days, but that February has an extra day every four years. Therefore, when using the PMT function, the last payment will be larger than the rest, or an extra payment may be needed.
To overcome this problem, I have incorporated Excel’s Goal Seek into the calculation.
Once the input data above is entered in the spreadsheet, an amortization schedule is produced. The last input is a dropdown with three options (30/360, Actual/365, and Actual/360). When you change the last input (Interest Accrual) a new payment is calculated to amortize the loan to zero at maturity,
Here is how it works:
With the xlookup function it finds the last payment in the amortization schedule. Then using the same xlookup function it finds the next to last payment. Then it subtracts the two for the difference (cell j6). The payment is in cell E15. Then when either the “Solve for Payment” button is pushed or the Interest Accrual cell is changed, Goal Seek triggers and changes the payment in cell E15 until the cell containing the difference between the last and next to last payments equals zero.
If the only input you change is the “Interest Accrual”, calculating the appropriate payment is automatic. Changing any of the other of the four inputs will require clicking the “Solve for Payment” button.