Content Partner

Grades K-2, 3-5

Register for the upcoming webinar Introducing the New 5th Edition AP Economics Curriculum on August 17th at 5pm ET

Log in to EconEdLink
Log in to EconEdLink

Webinars are free to attend or watch! To register log in to your EconEdLink account, or sign up for free.

Don't have an account yet? Sign up for free

Log in to EconEdLink
Log in to EconEdLink

Webinars are free to attend or watch! To register log in to your EconEdLink account, or sign up for free.

Don't have an account yet? Sign up for free

How to amortize a loan using an Excel spreadsheet.

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.

- 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.

- 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://web.archive.org/web/20190422173155/http://www.loanbiz.com/regz.htm

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.

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.

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.

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.

Content Partner

Grades K-2, 3-5

In this personal finance book, a serial spender discovers that there's a difference between wanting something and needing it.

Key Concepts: Borrower, Financial Planning, Lend

30 mins

Activity

Content Partner

Grades K-2, 3-5

In this economics and personal finance book, an inexperienced royal learns a valuable lesson about reading the fine print.

Key Concepts: Financial Investments, Financial Markets, Financial Planning

30 mins

Activity

Grades 6-8

In this personal finance lesson, students will learn to create a spending and savings plan using order of operations.

Key Concepts: Budgeting, Income, Money

60 mins

Lesson

Grades 6-8, 9-12

In this personal finance activity, students will identify the steps needed to meet a savings goal.

Key Concepts: Budgeting, Saving

20 min

Activity