Fees and Annual Percentage Rate

Loan Amortization in Google Sheets

Brent Allen

Instructor

Upfront fees

  • Fees are amortized over the life of the loan.
  • Referred to as "points"
  • Each point is 0.01% of the total opening loan balance.

The rate paid including fees is the Annual Percentage Rate or APR.

Mortgage rates picture

Loan Amortization in Google Sheets

Payday loans and APR

  • Loans are over a short period of time.
  • Loans are normally quoted in terms of amounts instead of percentages.
  • Annual rates can be over 400% in the case of a 15% fee over 2 weeks!

Payday loan store sign

Loan Amortization in Google Sheets

Payday loan calculation

  • Calculate the financing charge; the interest including all fees.
  • Divide financing charge by the initial loan balance to calculate the nominal interest rate.
  • Determine the number of loan periods per year.
  • Multiply the nominal rate by the periods per year to calculate APR.

Payday loan calculation

Loan Amortization in Google Sheets

Mortgages and APR

  1. Calculate the payments on the loan plus any amortizing fees with the PMT() function, using the posted interest rate.

  2. Calculate the nominal rate using the RATE() function.

  3. Multiply the nominal rate by the number of periods to calculate the annual percentage rate.

Amortization calculation with fees

Loan Amortization in Google Sheets

Using the RATE() formula

=RATE(number_of_periods, periodic_payment, loan_amount_before_fees)

  1. Calculate the number of periods on the loan.
  2. Use periodic_payment as calculated earlier with the PMT() function.
  3. Use the original loan balance as the amount before fees.
  4. Multiply by periods per year to calculate APR.

Full calculation of APR on schedule

Loan Amortization in Google Sheets

Time to calculate some APRs!

Loan Amortization in Google Sheets

Preparing Video For Download...