Understanding Mortgage Payments – I am going to pay HOW MUCHHOW MUCH for this

house?

You borrow for your first home but that is NOT the amount of money you are going to have to pay back.

You will be paying back MUCH MORE. Why? Do you know how to calculate how much more you

will have to pay?

Prepared for SSAC byJody Murphy – Colby-Sawyer College

© The Washington Center for Improving the Quality of Undergraduate Education. All rights reserved. 2006

Supporting Quantitative concepts and skillsForward modelingPercentagesVisual display of data Column graphs

SSAC2006.HF5691.JM1.1

1

Core Quantitative conceptMultivariable function

Overview

A loan for a home is called a mortgage. There are many financing options available for the loan. Typically you will have a monthly mortgage payment.

Each mortgage payment is composed of two parts: repayment of the balance of the loan (principal) and the cost of the loan (interest). In the

beginning of your mortgage, the majority of your payments are applied to interest. The division between principal and interest can be seen by

creating an amortization table.

Slide 3 introduces the problem.Slide 4 - 5 ask you to set up a worksheet to calculate the mortgage

payment using the PMT function in Excel.Slide 6 -10 ask you to expand the spreadsheet to include the interest and principal components and verify that the loan principal is zero at

maturity.Slide 11 asks you to expand the spreadsheet to include totals.Slide 12-13 ask you to create charts to illustrate payment components. Slide 14-15 contain your homework assignment.

You find the home of your dreams. Assuming you are still in your home at the end of the mortgage, and have not

refinanced, how much did this mortgage really cost you?

Problem

The purchase price of the home is $250,000. To avoid PMI you make a down payment of 20% and finance the balance. Your bank offers a

7% fixed rate 30-year mortgage. Calculate the required monthly payment and create an amortization table.

3

Calculating Mortgage Payments: Worksheet facts

Create the spreadsheet below by utilizing the facts in the problem.

= Cell with a number in it

= Cell with a formula in it

Color convention throughout this module

This is the first spreadsheet to create. You will be expanding this spreadsheet as needed.

B C2 Home Purchase Information:3 purchase price of home: 250,000.00$ 4 20% down payment: 50,000.00$ 5 amount to finance: 200,000.00$ 67 Mortgage Information:8 term: 30 year mortgage9 months: 360

10 interest rate: 7%11 mortgage : 200,000.00$

Calculating Mortgage Payments: Payment Calculation mathematically

Your monthly payment amount will depend on the size of the mortgage, the interest rate, and how many months you will make payments. In other words, your monthly payment (PMT) is a function of the principal (P), the interest rate (r), and the number of payments (n). Mathematically, this is expressed as

PMT = PMT(P, r, n)

PMT is a multivariable function. The values of P, r, and n, which are in the parentheses, are the arguments of the function. Specifically, here is the function:

Please note r is expressed as a percent (.07 per year, not 7 per year), and that n is the number in months. If that were not the case, there would be some other value than 12 in the equation.

)1)12

1((*12

)12

1(**

n

n

r

rrP

PMT

You may be happy to know that there is a built-in Excel function (PMT) that calculates the function for you. We will use that function in this module.

What would this equation be if you wanted to express r as 7 instead of .07?

Calculating Mortgage Payments: Payment Calculation using Excel

Using the PMT function in Excel determine your monthly mortgage payment.

You can insert the PMT function by going to Insert, Function, PMT, and following the

instructions as provided by Excel.

B C D E2 Home Purchase Information:3 purchase price of home: 250,000.00$ 4 20% down payment: 50,000.00$ 5 amount to finance: 200,000.00$ 67 Mortgage Information:8 term: 30 year mortgage9 months: 360

10 interest rate: 7%11 mortgage : 200,000.00$ 1213 Monthly Payment: 1,330.60$ using PMT function

Calculating Mortgage Payments: Interest and Principal Components

Expand your spreadsheet to include the setup for the amortization table of the mortgage for month zero (for reference). You will note at month zero

your remaining balance is the full value of your mortgage and your equity is the amount of your down payment.

+F16 =+C18-D18

B C D E F G2 Home Purchase Information:3 purchase price of home: 250,000.00$ 4 20% down payment: 50,000.00$ 5 amount to finance: 200,000.00$ 67 Mortgage Information:8 term: 30 year mortgage9 months: 36010 interest rate: 7%11 mortgage : 200,000.00$ 1213 Monthly Payment: 1,330.60$ using PMT function141516 month payment interest principal remaining balance equity17 0 200,000.00 50,000.00$

Calculating Mortgage Payments: Interest and Principal Components

Expand your spreadsheet to include the calculations of interest and principal breakdown for the first payment month of the loan.

+C$13

=(+F17*C$10)/12 =+C18-D18

B C D E F G2 Home Purchase Information:3 purchase price of home: 250,000.00$ 4 20% down payment: 50,000.00$ 5 amount to finance: 200,000.00$ 67 Mortgage Information:8 term: 30 year mortgage9 months: 36010 interest rate: 7%11 mortgage : 200,000.00$ 1213 Monthly Payment: 1,330.60$ using PMT function141516 month payment interest principal remaining balance equity17 0 200,000.00 50,000.00$ 18 1 1,330.60$ 1,166.67$ 163.94$

Calculating Mortgage Payments: Interest and Principal Components

Expand your spreadsheet to show the changes in remaining balance and equity after applying the first month’s payment.

=+F17-E18=+G17+E18

B C D E F G2 Home Purchase Information:3 purchase price of home: 250,000.00$ 4 20% down payment: 50,000.00$ 5 amount to finance: 200,000.00$ 67 Mortgage Information:8 term: 30 year mortgage9 months: 360

10 interest rate: 7%11 mortgage : 200,000.00$ 1213 Monthly Payment: 1,330.60$ using PMT function141516 month payment interest principal remaining balance equity17 0 200,000.00 50,000.00$ 18 1 1,330.60$ 1,166.67$ 163.94$ 199,836.06$ 50,163.94$

B C D E F G2 Home Purchase Information:3 purchase price of home: 250,000.00$ 4 20% down payment: 50,000.00$ 5 amount to finance: 200,000.00$ 67 Mortgage Information:8 term: 30 year mortgage9 months: 36010 interest rate: 7%11 mortgage : 200,000.00$ 1213 Monthly Payment: 1,330.60$ using PMT function141516 month payment interest principal remaining balance equity17 0 200,000.00 50,000.00$ 18 1 1,330.60$ 1,166.67$ 163.94$ 199,836.06$ 50,163.94$ 19 2 1,330.60$ 1,165.71$ 164.89$ 199,671.17$ 50,328.83$ 20 3 1,330.60$ 1,164.75$ 165.86$ 199,505.31$ 50,494.69$ 21 4 1,330.60$ 1,163.78$ 166.82$ 199,338.49$ 50,661.51$ 22 5 1,330.60$ 1,162.81$ 167.80$ 199,170.69$ 50,829.31$ 23 6 1,330.60$ 1,161.83$ 168.78$ 199,001.91$ 50,998.09$ 24 7 1,330.60$ 1,160.84$ 169.76$ 198,832.15$ 51,167.85$ 25 8 1,330.60$ 1,159.85$ 170.75$ 198,661.40$ 51,338.60$ 26 9 1,330.60$ 1,158.86$ 171.75$ 198,489.66$ 51,510.34$ 27 10 1,330.60$ 1,157.86$ 172.75$ 198,316.91$ 51,683.09$ 28 11 1,330.60$ 1,156.85$ 173.76$ 198,143.15$ 51,856.85$ 29 12 1,330.60$ 1,155.84$ 174.77$ 197,968.38$ 52,031.62$

Calculating Mortgage Payments: Interest and Principal Components

Expand your spreadsheet to include the next 11 of payments or the first full year of payments.

=B18+1

Highlight the cell you would like to

copy.

Edit

Copy

Highlight the cell you would like to

copy to.

Edit

Paste

B C D E F G2 Home Purchase Information:3 purchase price of home: 250,000.00$ 4 20% down payment: 50,000.00$ 5 amount to finance: 200,000.00$ 67 Mortgage Information:8 term: 30 year mortgage9 months: 360

10 interest rate: 7%11 mortgage : 200,000.00$ 1213 Monthly Payment: 1,330.60$ using PMT function141516 month payment interest principal remaining balance equity17 0 200,000.00 50,000.00$ 18 1 1,330.60$ 1,166.67$ 163.94$ 199,836.06$ 50,163.94$ 19 2 1,330.60$ 1,165.71$ 164.89$ 199,671.17$ 50,328.83$ 20 3 1,330.60$ 1,164.75$ 165.86$ 199,505.31$ 50,494.69$ 369 352 1,330.60$ 67.86$ 1,262.74$ 10,370.76$ 239,629.24$ 370 353 1,330.60$ 60.50$ 1,270.11$ 9,100.65$ 240,899.35$ 371 354 1,330.60$ 53.09$ 1,277.52$ 7,823.13$ 242,176.87$ 372 355 1,330.60$ 45.63$ 1,284.97$ 6,538.16$ 243,461.84$ 373 356 1,330.60$ 38.14$ 1,292.47$ 5,245.70$ 244,754.30$ 374 357 1,330.60$ 30.60$ 1,300.01$ 3,945.69$ 246,054.31$ 375 358 1,330.60$ 23.02$ 1,307.59$ 2,638.10$ 247,361.90$ 376 359 1,330.60$ 15.39$ 1,315.22$ 1,322.89$ 248,677.11$ 377 360 1,330.60$ 7.72$ 1,322.89$ 0.00$ 250,000.00$

Calculating Mortgage Payments: Interest and Principal Components

Expand your spreadsheet to include the remaining months of the loan (up to 360 months). Confirm that the remaining balance is zero

at month 360.

This must be zero. The mortgage is fully paid at month 360 (30 years

* 12 months).

Your spreadsheet

will have all 360 months. This example has rows hidden

due to spreadsheet

size.

B C D E F G2 Home Purchase Information:3 purchase price of home: 250,000.00$ 4 20% down payment: 50,000.00$ 5 amount to finance: 200,000.00$ 67 Mortgage Information:8 term: 30 year mortgage9 months: 36010 interest rate: 7%11 mortgage : 200,000.00$ 1213 Monthly Payment: 1,330.60$ using PMT function141516 month payment interest principal remaining balance equity17 0 200,000.00 50,000.00$ 18 1 1,330.60$ 1,166.67$ 163.94$ 199,836.06$ 50,163.94$ 19 2 1,330.60$ 1,165.71$ 164.89$ 199,671.17$ 50,328.83$ 20 3 1,330.60$ 1,164.75$ 165.86$ 199,505.31$ 50,494.69$

369 352 1,330.60$ 67.86$ 1,262.74$ 10,370.76$ 239,629.24$ 370 353 1,330.60$ 60.50$ 1,270.11$ 9,100.65$ 240,899.35$ 371 354 1,330.60$ 53.09$ 1,277.52$ 7,823.13$ 242,176.87$ 372 355 1,330.60$ 45.63$ 1,284.97$ 6,538.16$ 243,461.84$ 373 356 1,330.60$ 38.14$ 1,292.47$ 5,245.70$ 244,754.30$ 374 357 1,330.60$ 30.60$ 1,300.01$ 3,945.69$ 246,054.31$ 375 358 1,330.60$ 23.02$ 1,307.59$ 2,638.10$ 247,361.90$ 376 359 1,330.60$ 15.39$ 1,315.22$ 1,322.89$ 248,677.11$ 377 360 1,330.60$ 7.72$ 1,322.89$ 0.00$ 250,000.00$ 378379380 Total Payments 479,017.80$ 381 Total Interest Paid Over Life of Loan 279,017.80$ 382 Total Principal Paid Over Life of Loan 200,000.00$

Calculating Mortgage Payments: Interest and Principal Components

Expand your spreadsheet to include totals for the following three columns: payment, interest, and principal.

Wow! For your $200,000 mortgage you would actually pay more for interest than the amount of the original loan!

=SUM(C18:C377)

Calculating Mortgage Payments: Chart for the first year

Create a column chart for the first year of mortgage payments.

Look at all that yellow! Most of each monthly payment is applied to interest, not to principal.

Highlight Month, Interest and Principal for the first year. Select Insert and Chart. This will open the chart wizard. Select Column, Stacked Column,

and continue using the Chart Wizard until chart is complete.

First Year Payment Mix

$0$100$200$300$400$500$600$700$800$900

$1,000$1,100$1,200$1,300

1 2 3 4 5 6 7 8 9 10 11 12

Month

Per

cen

tag

e

principal

interest

Calculating Mortgage Payments: Chart for the life of the mortgage

Create a chart of the mortgage payments for the life of the mortgage.

Hint: eliminate borders and choose bright colors to clearly see chart.

As time goes on, more and more of the payment is applied to principal.

Mortgage Payment Mix

$-$100$200$300$400$500$600$700$800$900

$1,000$1,100$1,200$1,300$1,400

1 2 3 352 353 354 355 356 357 358 359 360

Month

Do

llar

Am

ou

nt

principal

interest

End of Module Assignment

1. Write a short paragraph on how mortgage payments are applied.

2. Using the facts from the original worksheet decrease the interest rate to 6%. Citing specific numerical values discuss how the change in the interest rate affects the total cost of the mortgage.

3. Using the facts from the original worksheet reduce the term to 15 years. Citing specific numerical values discuss how the change in the term affects the total cost of the mortgage.

4. Using the facts from the original worksheet, and assuming no prepayment penalties, increase your monthly payment by $100. Citing specific numerical values discuss how the change in the payment affects the total cost of the loan.

Continue on the next page.

End of Module Assignment

5. Imagine that you have graduated college, landed a great job and are ready to buy your first home. Research average starting salaries in your field. Using print or computer real estate listings find a home (house, condominium, etc.) that you would like to buy near your new job. Please cut out or print out the ad for the home. Find a local (your geographical area) bank offering 30 year fixed rate mortgages. Compare local bank mortgage offerings to mortgages found by utilizing www.bankrate.com .

6. Assume you will be putting down 20% and financing the balance on your home. Using the PMT function in Excel determine your monthly payment. Is it reasonable based on the average starting salaries in your field? If so, should you consider other options to pay off your mortgage quicker? If not, what other feasible options are available?

7. Using an Excel spreadsheet create an amortization table for the mortgage that best fits your personal situation.

8. Create a chart illustrating the components of your mortgage payments for the life of the mortgage.

9. The Federal Open Market Committee is scheduled to meet shortly, should you be concerned about “locking in” your interest rate? Why or why not?

Definitions

• PMI – Principal mortgage insurance. Insurance that usually needs to be paid when you put less than a 20% down payment on a house. Return to presentation.

Hints for using the PMT function within Excel

• Rate - the interest rate for the mortgage per month. If the rate is stated as an annual rate it must be divided by 12 months.

• Nper - the number of payments to be made for the mortgage.

• Pv -  the present value of the mortgage. The amount that is being financed.

• Fv  - the future value of the mortgage. After all payments are made the mortgage balance is expected to be zero.

• Type -  when the mortgage payments are expected to be made, 1 indicates at the beginning of the period and 0 indicates at the end of the period. Return to presentation.