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

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.

--
Regards,
Fred


"Network Capital" <Network wrote in message
...
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

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.

"Fred Smith" wrote:

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.

--
Regards,
Fred


"Network Capital" <Network wrote in message
...
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