Floating rates

Loan Amortization in Google Sheets

Brent Allen

Instructor

What are floating rates?

  • Interest rates are not fixed throughout the term of the loan.
  • Rates are based on a central index, such as US Federal Reserve Prime or LIBOR.
  • May be quoted as Prime + 1.5% or similar.
  • Index rates change over time, with central banks meeting quarterly.
  • Rates can change on fixed dates - reset dates.

The Bank of England

Loan Amortization in Google Sheets

Floating vs. fixed rates

Floating rates

  • Interest rate not fixed.
  • Payments fixed for term of loan unless interest exceeds payments.
  • Amortization period can change based on rates.

Fixed rates

  • Interest rate fixed for term of loan.
  • Payments fixed for term of loan.
  • Amortization period cannot change.
Loan Amortization in Google Sheets

Rate reset example

Loan Terms

  • $500,000 15 year floating rate loan starting at 5% APR amortized monthly.

Time to Maturity

  • At 5% - 180 months
  • At 5.25% - 185 months
  • At 4.75% - 176 months
  • At 0% - 127 months
  • At 9.5% - Infinite

Sensitivity of interest rates example

Loan Amortization in Google Sheets

Negative amortization

  • Increasing rates cause interest to exceed payment.
  • Negative principal increases balance owing at end of period.
  • Balance never reaches zero.

Negative amortization example

Loan Amortization in Google Sheets

Maximum interest rate

Maximum interest rate

  • Highest possible rate that a loan can reach before negative amortization.
  • Payment fixed at start of loan.

Maximum interest rate = Balance / Payment * Payment frequency

Example: $500,000 loan, 5% APR, paid monthly.

=500000 / 3953.97 * 12

9.48952%
Loan Amortization in Google Sheets

Time for floating interest!

Loan Amortization in Google Sheets

Preparing Video For Download...