Lump sum payments

Loan Amortization in Google Sheets

Brent Allen

Instructor

Closed loans v. Open loans

Closed Loan

  • Payments must be made per the initial schedule.
  • Payments cannot be increased during the loan.
  • Paying the balance in full before the end of the loan term leads to a penalty.

Open Loan

  • Minimum payments must be made per the initial schedule.
  • Payments can be increased above the minimum, with excess payments applied against the principal.
  • Loan can be paid off at any time without penalty.
Loan Amortization in Google Sheets

Adding lump sum column

  • Values in the lump sum column are optional.
  • Entered in the period when they occur.
  • Does not change the period, date, opening balance or LTV calculation.

Adding a lump sum payment on the schedule

Loan Amortization in Google Sheets

Final payment adjustment

  • Payments are normally calculated by the PMT() function.
  • In the final period, a full payment would lead to a negative balance.
  • Final payment limited to principal plus interest on principal over the final period.

Final period payment adjustment example

Loan Amortization in Google Sheets

Final period payment calculation

=IF(PMT() formula > opening balance + interest, PMT() formula, opening balance + interest)

PMT() formula >= opening balance plus interest; use payment formula

PMT() formula < opening balance plus interest; use the opening balance plus interest.

Example: 1,000 payment formula, 500 opening balance, 6% APR amortized monthly.

=IF(1000 > 500 + 500 * (6%/12), 1000, 500 + 500 * (6%/12))

502.50
Loan Amortization in Google Sheets

Don't use financial functions!

Financial functions only work when the schedule has no irregular payments.

Functions to not use when working with lump sums include:

  • IPMT()
  • PPMT()
  • CUMIPMT()
  • CUMPRINC()
Loan Amortization in Google Sheets

Manual calculations

PMT() formula must be calculated first!

  • Interest payment - Interest = Opening balance * APR / Number of annual periods

  • Principal payment - Principal = Monthly payment - Interest

  • Cumulative interest - Cumulative Interest = Prior cumulative interest + Current period interest payment

  • Cumulative principal paid - Cumulative Principal = Prior cumulative principal paid + Current period principal payment

Loan Amortization in Google Sheets

Let's get lumpy!

Loan Amortization in Google Sheets

Preparing Video For Download...