misho-kr/Loan Amortization in Spreadsheets.md

## Loan Amortization in Spreadsheets.md

      
    Raw
  

              Loan Amortization in Spreadsheets.md
            
          
    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