Reducing your debt

Financial Modeling in Google Sheets

Erin Buchanan

Professor

Loan amortization tables

  • A loan is borrowed money that is paid across time with interest
  • Each payment is split into principal and interest
  • The interest portion starts high and then decreases, as the amount owed decreases
Financial Modeling in Google Sheets

Loan Table

Financial Modeling in Google Sheets

Beginning balances and payments

Required Payment B7: =pmt(rate, number_of_payments, -present_value)

Year 1 B10: =B4

Annual Payment C: =$B$7

Loan Start Functions

Financial Modeling in Google Sheets

Calculate where your money went

Interest Year 1 D10: =B10*$B$5

Principal E10: =C10-D10

Year-end Balance F10: =B10-E10

Payment Interest Split

Financial Modeling in Google Sheets

Complete the model

Second year beginning balance B11: =F10

Copy function cells for the rest of years

Finalize the model

Financial Modeling in Google Sheets

Make your loan model!

Financial Modeling in Google Sheets

Preparing Video For Download...