Loan Amortization in Spreadsheets

Course Description

A loan amortization schedule sounds like something that's only used by bankers and financial traders, right?

Wrong! In this course, we'll be looking at the key financial formulas in Google Sheets that you can use to investigate your own loans, like student loans, car loans, and mortgages. We'll build up a dashboard in Google Sheets which uses visualizations and conditional formulas to produce presentation-ready spreadsheets which will impress any finance manager!

By Brent Allen, Financial Spreadsheets Specialist

Introduction to Financial Concepts in Google Sheets

Basic financial formulas in Google Sheets to build a loan amortization spreadsheet for a student loan. Calculations for principal payment, interest and principal at a given point in time.

Load Amortization Table
- Periodic Payments
- Interest and Principal
- Finite Length
Used for mortgages, car loans, student loans
Not used for credit cards, lines of credit, payday loans
Required Elements
- Interest rate
- Amortization period of the load
- Frequency of loan payments
- Amount of the loans
Simple Interest
Monthly Payment = Interest Payment + Principal Payment
Payment Function
- How much are the princial payments
- Constant throughout the term of the loan
Percentahe of Payments to Interest and Principal

=PMT(Interest Rate, Amortization Periods, Present Value,
     [Future Value], [End or Beginning])
=PPMT(Interest Rate, Current Period, Total Amortization Periods, Presetn Value,
     [Future Value], [End or Beginning])
=IPMT(Interest Rate, Current Period, Total Amortization Periods, Presetn Value,
     [Future Value], [End or Beginning])

Creating an Amortization Schedule

Extend the payment formulas to the full length of a loan. create a fully functional schedule and verify the accuracy of the calculations on the schedule.

Important parts: periods, opening balance, rate
Removing decepting elements
- Interest Rate must be stated in annual terms
- Number of Periods will alwats be in years
- Frequency of Payments must be clear
Modify the PMT and IPMT functions
Loan length is not the amortization length
- On longer amortization schedules, the loan can be shorter than the total amortization in order to help control the risk
- Amortization length must be the number of periods to reduce the balance on an amortizing loan to zero
- At the end of the loan, the borrower can pay off the unamortized balance in full or enter into a new load
Cumulative Calculation Functions
Dates can be hard to calculate when we use periods, need to add:
- Loan Date
- Payment Date
Loan to Value = loan balance divided by the value of the asset

=CUMPRINC(rate, number_of_periods, present_value, 
          first_period, last_period, end_or_beginning)
=CUMIPMT(rate, number_of_periods, present_value, 
          first_period, last_period, end_or_beginning)
=EOMONTH(start_date, number_of_months)

Making a Loan Amortization Dashboard

Take the amortization schedule from Chapter 2 and convert it into a fully functional loan dashboard. Create line and bar graphs, as well as input controls and cell protection.

Customizable Schedules and Dashboards with different amortization schedules
Adjusting dates for different time periods
- Monthly - use EOMONTH()
- Bi-weekly - ADD(date, days)
- Semi-monthly - EOMONTH(date, 0) + 15, EOMONTH(date, 1)
Loan visualizations
- Area charts
- Column charts
- Line graphs
- Scatter plots
- Histograms
Hiding unused cells
- Created filtered table
- Hide cells with IFS

=IF(condition, value if true, value if false)
=IFS(condition1, value1, condition2, value2, ..., 1=1, default value)
=SWITCH(cell, case1, value1, case2, value2, ... default value)
=FILTER(original table, condition)

Non-standard amortization schedules

Real-world adjustments to amortization schedules. Upfront fees and lump sum payments. Floating rate mortgages, maximum interest rate on floating loans and negative amortization.

Fees and Annual Percentage Rate
- Non-amortized fees, for example moving fees and legal fees
- Amortized fees are paid to the bank upon creation of the morgage
  - Referred to as points
  - Each point is 0.01% of the total opening loan balance
  - They have to be paied back, so effectively they create new, higher rate -- APR
Payday loans and APRs
- Loans are normally quoted in terms of amounts instead of percentages
Morgages and APRs
- More complicated to calculate because of the multiple payments
Open loans
- Minimum payments must be made as per the intial schedule
- Additional payments can be made throughout the loan
  - The extra payment is applied against the principal
  - In the final payment period the payment may exceed the balance
- Do not use financial formulas IPMT and PPMT
- Manual calculation of interest and principal payments at every period based on opening balance
- Closed loans
  - Payments can not be increased during the loan
  - Paying the balance in full before the end of the loan terms leads to penalty
Floating Rates
- Interest rate may not be fixed throughout the term of the load period
- Rates are based on central index, such as US Federal Reserve Prime or LIBOR
- Rates can change only on fixed dates -- reset dates
- When the interest rates drop the amorization period on the loan accelerates
- Maximum interest rate

=RATE(number_of_periods, periodic_payment, loan_amount_before_fees)
=IF(PMT() formula > opening balance + interest, PMT() formula, opening balance + interest)

Maximum Interest rate = (Payment / Balance) * Payment frequency