amortization

Albert

In the attached file, columns A:D show "Regular payments using PMT and PPMT functions". This is a modified version of the Excel-easy file (from Willie's suggested URL). With your basic loan details (and the addition of rounding to tehir formulae), PMT is used in B to determine the expected payment and PPMT the reduction in the laon balance (in C) per month. The balance in D then reduces to very nearly 0 after the 5 years. Orange cells indicate differing formulae.

Columns F:H however allow for your extra payment (to be set once only in yellow cell F9). In column H, the balance is calculated as the (remaining principal plus monthly interest) less the total monthly payment. In H10 (and cells below), that is:

=ROUND(H9*(1+$G$3/12)-G10,2)

With F9=0 (like the original loan repayments) and no adjustment to the final payment, the balance reduces to a few cents only after the 5 years (so very close to the PMT and PPMT calculations).

Set F9=28.53 (so an extra), the total monthly becomes $150 as you say and the balance reduces to 0 faster, the final payment being at 47 months.

Note I've used If statements  in columns F and G to work out what happens as the balance approaches 0 (plus conditional fomatting to indicate the area when no payments are needed).

Hope this is good enough for your purposes.

Hello wilde,

I did a quick search of the site but didn't find a tutorial on building an amortization table. I did a quick Google search and found this in under a minute:

https://www.excel-easy.com/examples/loan-amortization-schedule.html

I also discovered that you made a very similar post Sept. 7 and Sept. 20.

Information at the above url should give you the help you need.