Amortisation with a lump sum principal payment during term

[Guest]

I am trying to build am amortisation spreadsheet which allows for either
regular or irregular laump sum payments during the term of loan which should
affect the monthly payments during the remaining term. Spreadsheet is to be
used for commercial equipment lending rather than mortgage loan amortisation.
Can some one help with formulae please or build spreadsheet?
Thank you

 

[Fred Smith]

An amortization table is easy to build. You need five columns: Period, Opening
Balance, Payment, Interest, Closing Balance. For your application, you could
modify the payment in any period, or add a column for lump sum payments.

To calculate interest, use:

=OpeningBalance * IntRate / #PeriodsPerYear

You can calculate Payment using the Pmt function.

 

[Guest]

you'll probably need two additional things:
1. is a balloon payment column. Presumably the principal payment column is
going to be equal to the mortgage payment less the interest payment.
2. either the ending balance or the principal payment will probably need to
be adjusted with =min, so that in the event of an early buyout, you don't
wind up overpaying the loan balance.