Grade 9-12
,
Lesson

Loan Amortization – Mortgage

Updated: October 25 2019,
Author: Mike Fladlien
Teacher Version

Standards

National Standards in Economics

National Standards in Financial Literacy

State Standards

Concepts

Budgeting, Credit, Financial Planning, Interest

How to amortize a loan using an Excel spreadsheet.

Introduction

For most consumers, buying a home is one of life’s biggest decisions. Shopping for the most home at the most favorable rates is challenging even for the savvy shopper.  This lesson, gives the student a taste of how changes in the interest rate can affect the monthly payment made on a mortgage.  Students will see how the change in the interest rate and the length of the loan affects the total amount that is repaid.

Learning Objectives

  • Use an Excel spreadsheet to analyze amortization on a mortgage.
  • Make predictions about the monthly payment if the interest rate changes.
  • Make predictions about what will happen to the monthly payments if the time of the loan decreases while everything else stays constant.
  • Use a formatted spreadsheet to change variables and see how the changes affect the amount of interest paid each month and the amount of the payment that goes to paying off the mortgage.

Resource List

Process

1. Offer students a short primer on mortgages and loans for homes. Cover the basic information of how the loan is structured. Introduce the concepts such as down payment, principal vs. interest and how the ratio between the two changes over the course of the loan.

2. Explain key words: https://www.investopedia.com/terms/a/amortization.asp#axzz1bzsmwkUM , https://www.investopedia.com/terms/a/amortization_schedule.asp#axzz1bzsmwkUM , and Annual Percentage Rate .

3.  Have students download The Mortgage Amortization template.

4.  Use the template to input the following data:

Loan principal amount: $100,000

Annual Interest Rate: 6%

Loan period in years: 30

Base year of loan: 2010

Base month of loan: January.

5.  The students now have a table that you can use to ask questions and build their personal finance skills.

6.  Use the Socratic method and ask the following questions:

a.  In January how much of the payment went to paying principal? [$99.55]

b.  In January how much of the payment went to paying interest? [$500]

c.  What happens to the amount of the interest that is paid on the loan as more and more monthly payments are made? (The amount of interest decreases.)

d.  How much total interest is paid over the length of the loan? [$115,838]

7.  Change the data in the “Inputs” section to:  Annual interest rate: 7%

8.  What happens to the payment? [The payment increases to $665.30]

9.  Now change the interest rate back to 6%, but change the loan period in years to 20.

10.  What happens to the payment? [The payment increases to $716.43]

11.  When the Loan period is 20 years, how much total interest is paid on the loan? [$71,943.20)]

12.  Compare your answer in 10 to 5d.  How does the length of the loan affect the amount of interest a consumer pays? [The longer time to repay the loan, the higher the interest paid back.]

13.  Have students change the data in the inputs section to a home loan with the following characteristics:

Loan principal amount: $220,000

Annual interest rate: 5.25%

Loan period in years: 30

Base year of loan: 2010

Base month of the loan January

a. How much interest was paid in May? [$958.05]

b. How much principal was paid towards the loan in December, 2010? [$264.76]

c. How much total interest was paid over the course of the loan? [$217,346.00]

14.  Verify students’ results on their computers then assign the assessment activity.

Conclusion

Using an amortization table allows students to see how changes in the principal, interest rate, and length of time of the loan intermix.  Students should be able to predict that higher interest rates result in higher monthly payments.  In addition, longer time periods of the loan will increase the amount of interest consumers pay.  Students will be able to explain that in the early part of the loan, the most interest is paid and in the later part of the loan, most of the monthly payment pays off principal.

Extension Activity

1.  Ask students to research the Truth in Lending Act, Regulation Z to find information about the annual percentage rate.  Have your students write a short paper that includes information about what must be included in a loan agreement.  Ask students to cite the Internet address. [Regulation Z must tell the borrower the finance charge in dollars and the APR in percent.]

2.  Have students research “Amortization Schedule.”  The students should write a report on what an amortization schedule shows and include an example.  [An amortization schedule is a table that  breaks the monthly payments into principal payments and interest payments.  The balance is the amount of principal that the borrower owes.  Amortization is the process of paying off debt by making payments over a period of time.]

3.  Ask students to search the internet to find out how to get the best mortgage.  Have the students list these tips and cite the Internet source.
https://www.hgtv.com/design/real-estate/6-things-to-know-before-buying-a-new-home is a home buyer’s guide that lists tens things home buyers should consider when purchasing a home. Do your due diligence before going to a bank for a loan.  Ask about the APR.

Assessment

Use the Excel template to build an amortization table for Juan Sanchez.

1. Juan wants to buy a home for $85,000.  His loan will be financed at 6% interest for 30 years.  Juan must make a 10% down payment.  Using this information, what will Juan’s monthly payment be?  [$458.66]

2. In December, 2010, how much will Juan owe on his mortgage? [$75,560.53]

3. How much interest will Juan pay over the course of the loan? [$88,617.60]

4. If the interest rate suddenly rises to 6.25%, what will his payment be? [$471.02]

Print off the template for your teacher and turn in your print out with the answers to the questions above.

Subjects: