Measuring balance over time

Loan Amortization in Google Sheets

Brent Allen

Instructor

Dates vs. periods

  • Sometimes, dates can be hard to calculate mentally!
  • Dates can be calculated by the spreadsheet
  • Adding dates to schedules
    • allow for real life events to sync up with the loan schedule
  • Events could be
    • business events (quarter ends, year ends),
    • personal events
    • other loans

January 2019 Calendar

Loan Amortization in Google Sheets

Loan date and payment dates

2 new fields to add to the amortization schedule:

  • Loan Date
  • Payment Date

Loan Date and Payment Dates for first 9 periods.

Loan Amortization in Google Sheets

New Function - EOMONTH()

Returns the last day of the month a number of months before or after a specified date.

=EOMONTH(start_date, month)

Example: Loan Date - December 31, 2018. Period 3.

=EOMONTH(12/31/2018, 3)

3/31/2019
Loan Amortization in Google Sheets

Loan to value

  • The value of a loan vs. the underlying value of an asset being purchased.

Calculated using:

Loan to Value = Loan Balance / Asset Value

  • Used for creditworthiness, insurance.

Blue BMW on asphalt

Loan Amortization in Google Sheets

Asset value and loan to value

Asset Value - A new value which is located close to the loan principal value on the schedule.

Loan to Value (LTV) - Added to the right beside closing balance. Calculated by:

LTV = Closing Balance / Asset Value

Asset Value and Loan to Value

Loan Amortization in Google Sheets

Time to practice dates and LTV!

Loan Amortization in Google Sheets

Preparing Video For Download...