Loan Amortization - Mortgage


This lesson printed from:


Shopping for a home loan, or a mortgage, is a big decision that will affect the home buyer for as long as 30 years.  For many consumers, the purchase of a new home is one of life's biggest decisions. In this lesson, you will learn how to use an amortization schedule to shop around for the best deal to ensure that you select the most favorable terms.


You will use an Excel template that will calculate your monthly payments and the balance of your mortgage.  Plan to keep this template for future reference and use the template to select the most favorable terms in your decision to buy a home.


1.  Listen to your teacher introduce the concepts of a mortgage loan and explain amortization, amortization schedule, and APR.

2.  Download The Mortgage Amortization template for EconEdLink or Microsoft. Alternatively, use the online mortgage calculator .

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

4.  You will now have a table that you can use to answer questions and a resource build your personal finance skills.

5.  Use the template to answer the following questions:

a.  In January how much of the payment went to paying principal? 

b.  In January how much of the payment went to paying interest? 

c.  What happens to the total amount of the interest paid on the loan as more and more monthly payments are made? 

d.  How much total interest is paid over the length of the loan? 

6.  Change the data in the "Inputs" section to: 

Annual interest rate: 7%

7.  What happens to the payment?

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

9.  What happens to the payment? 

10.  When the Loan period is 20 years, how much total interest is paid on the loan? 

11.  Compare your answer in 10 to 5d.  How does the length of the loan affect the amount of interest a consumer pays? 

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

b. How much principal was paid towards the loan in December, 2010?

c. How much total interest was paid over the course of the loan? 

13.  Have your instructor verify that your results are correct; then complete the assessment activity.


Using an amortization table allows you to quickly see how changes in the principal, interest rate, and length of time of the loan intermix.  You 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.  You should 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.


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?  

2. In December, 2010, how much will Juan owe on his mortgage? 

3. How much interest will Juan pay over the course of the loan? 

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

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


1. Research the Truth in Lending Act, Regulation Z to find information about the annual percentage rate.  Write a short paper that includes information about what must be included in a loan agreement.  Please cite the Internet address. 

2. Research "Amortization Schedule".  Write a report on what an amortization schedule shows and include an example.  

3. Search the internet to find resources on how to get the best mortgage.  List these tips and cite the Internet source.