Your first amortization schedule

Loan Amortization in Google Sheets

Brent Allen

Instructor

Remember the loan shark?

  • You have already built an amortization schedule!
  • All important parts are present: periods, opening balance and rate.
  • Schedule was complete; end closing balance shows $0.

An example from Chapter 1 with the 328% interest loan.

Loan Amortization in Google Sheets

Removing deceptive elements!

  • Interest Rate - must be stated in annual terms.

    • All loans in the USA must be stated in Annual Percentage Rate (APR) by law.
    • APR will be discussed later in the course.

  • Number of Periods - will always be stated in years.

  • Frequency of Payment - is required in order to know how many payments are made per year.
Loan Amortization in Google Sheets

The more honest loan shark.

  • Annual interest rate is over 328%!
  • Daily schedules are very uncommon.

The Loan Shark example, with 328% interest and 0.017 of a year.

Loan Amortization in Google Sheets

Modifying formula for installments.

  • Payment and interest functions must be modified to calculate for periodic payments and compounding.

Payment Formula
=PMT(Annual Interest Rate / # of Annual Payments, Years * # of Annual Payments, Present Value)

Interest Formula
=IPMT(Annual Interest Rate / # of Annual Payments, Current Period, Years * # of Annual Payments, Present Value)

  • For IPMT() and PPMT() formulas, use payment number. Never use a fraction of a year!
  • Example - the third monthly payment in the second year of a loan is period 15, not period 1.25.
Loan Amortization in Google Sheets

Time to fix some amortization schedules!

Loan Amortization in Google Sheets

Preparing Video For Download...