Payment Function

Loan Amortization in Google Sheets

Brent Allen

Instructor

The payment function

  • Used to determine the principal payments on a loan.
  • Constant throughout the term of a loan.
  • Uses the PMT() function in your worksheet.

The payment formula

Loan Amortization in Google Sheets

The payment function in spreadsheets

=PMT(Interest Rate, Amortization Periods, Present Value,
     [Future Value],[End or Beginning])
  • Interest Rate - Nominal interest rate.
  • Amortization Periods - Total number of amortization periods on the loan.
  • Present Value - Initial balance of the loan.
  • Last 2 parameters are optional and not used in this course.
Loan Amortization in Google Sheets

Payment formula example

Example of the payment formula in use

  • 15 year term loan, $100,000 principal, 6% per annum
  • Interest rate in B3, Periods in B4, Principal in B2
  • Result = -$10,296.28
Loan Amortization in Google Sheets

Payment formula example with monthly payments

Example of monthly payment formula in use

  • No parameter to change payment frequency.
  • Interest rate should be kept in annual terms.
  • Divide interest rate by 12 in PMT() formula.
Loan Amortization in Google Sheets

Time to try out the PMT() function!

Loan Amortization in Google Sheets

Preparing Video For Download...