Understanding Mortgage Payments

Quantitative Goals: In the process of solving the problem and building the spreadsheets, students will increase their knowledge of arithmetic growth, percentages, forward modeling and visual display of data.

Spreadsheet Goals: Students will build spreadsheets to work through the step-by-step calculations. In the process of building their spreadsheets, students will learn to organize their thinking about a calculation. They will learn to pay attention and be careful when they work with equations (watching for parentheses, for example). They will learn that their first attempts are not necessarily correct (when their cell equations do not result in the same numbers as shown in the module), but that Excel will indeed produce the same answer as the module when they get the mistakes out of the cell equations -- that Excel does exactly what they tell it to do, which is not necessarily what they think they are telling it to do.

Finance (Content) Goals: Students will work with the concepts that:

• a mortgage payment consists of two parts: principal and interest
• both the interest rate and the length of the mortgage are very important to the total amount paid
• In the process of this work, students will increase their know-how of how mortgage payments and equity are calculated.

I use this module in my Personal Finance course. The course is aimed at nonbusiness majors who anticipate graduating in three or fewer semesters.

This module, the first of the course, comes in the sixth week of the semester. The preceding session is a lecture which introduces Excel and mortgages. The Mortgage Module problem-solving session happens in a lecture room equipped with computer and projector. I start the session by posing the question: "How are mortgage payments calculated?" The students brainstorm together as one large group that I facilitate.

The students soon decide that they need to know the cost of the house, the downpayment, and applicable financing (interest rate and length of the loan). We research local banks on the web for current mortgage interest rates. We discuss the basic calculation using paper and a calculator. We then move on to using the first spreadsheet (Slide 4) and discuss it before the end of the 75 minute session. They leave the session with a basic knowledge and will return in two days to the computer lab to work through the module. The students work through the module during class time to ask for help as needed in Excel. The end-of-module assignments will be due the following class period via e-mail.

The module is a PowerPoint presentation with embedded spreadsheets. The PowerPoint includes links to current interest rate information.

If the embedded spreadsheets are not visible, save the PowerPoint file to disk and open it from there.

An instructor version is available by request. The instructor version includes the completed spreadsheet. Send your request to Len Vacher (vacher@usf.edu) by filling out and submitting the Instructor Module Request Form.

The module is a computer-based activity. It is helpful to remind the students that it is still useful to have a pad of paper and a pencil at the ready to organize their thinking.

Remind the students of the first rule of building spreadsheets: save your work. Early and often.

End-of-module assignments can be used as a post-test.

The instructor version includes a pre-test.