Concepts
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
- Annual Percentage Rate APR: Definition and explanation of the annual percentage rate that is charged for borrowing (or made by investing), expressed as a single percentage number that represents the actual yearly cost of funds over the term of a loan.
https://www.investopedia.com/terms/a/apr.asp#ixzz1YLQ140an - Mortgage Amortization: Microsoft’s template. Download this and unzip.
http://www.econedlink.org/lessons/docs_lessons/1022_Mortgage%20Amortization%20Template.xls - Mortgage Calculator: For those without access to Excel, this online mortgage calculator can be used instead of the template.
https://www.mortgagecalculator.org/ - Amortization: Investopedia definition.
https://www.investopedia.com/terms/a/amortization.asp#axzz1bzsmwkUM - Amortization Schedule: Investopedia definition.
https://www.investopedia.com/terms/a/amortization_schedule.asp#axzz1bzsmwkUM - Mortgage Basics: Use the following information to cover the basics of a home mortgage loan.
https://www.bankrate.com/mortgage/home-buying-guide/ - Truth in Lending Act, Regulation Z: This site provides an overview of the Truth in Lending Act and Regulation Z.
https://ncua.gov/regulation-supervision/manuals-guides/federal-consumer-financial-protection-guide/compliance-management/lending-regulations/truth-lending-act-regulation-z
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.