MATH 123 PROJECT 2
Introduction
The purpose of this project is to delve more deeply into the details of mortgage loans. Students will learn
how to create an amortization schedule, determine total payments, total interest, and use ratios to compare
and interpret quantities. In addition to an in-depth view of mortgages, other details of home ownership will
be investigated: property taxes, maintenance and insurance costs, closing fees, equity, and more. When
completed, this comprehensive mortgage loan project can be used to investigate many of the details in
involved in choosing a mortgage loan.
Data
Research your expected income after you graduate. A link to annual median salaries can be found at:
http://www.bls.gov/oes/current/oes_in.htm#27-0000. Assume the following: an affordable loan amount is 2.5
times your annual salary, a 4.25% APR, and a 30-year mortgage. With this information, the monthly
mortgage payment can be calculated.
In addition to monthly mortgage payments, there are additional monthly fees we will consider: property
taxes, maintenance fees, and home ownerβs insurance. We will also consider buyer and seller closing costs1
,
which occur at the time of purchase and selling of a property. Other fees/costs, such as down payments,
landscaping, etc.2 will not be considered here. Also, we will assume that no value in property is lost. That is,
Buying and selling prices are the same. Table 1 summarizes the fees/costs, their percentages/amounts, and
how to calculate them.
Table 1. Additional monthly fees and closing costs3
.
Fee Percentage/Amount How to Calculate
Property Tax 1.1% ππππ‘βππ¦ πππ =
0.011 Γ πππππππ‘π¦ ππππ’π
β
12
Maintenance Fees 1.5% ππππ‘βππ¦ πππ =
0.015 Γ πππππππ‘π¦ ππππ’π
12
Homeowners Insurance $100 per month ππππ‘βππ¦ πππ = $100
Closing Costs (Buyer) 4% πΉππ ππ‘ π‘πππ ππ ππ’ππβππ π = 0.04 Γ πππππππ‘π¦ ππππ’π
Closing Costs (Seller) 9% πΉππ ππ‘ π‘πππ ππ π ππππππ = 0.09 Γ πππππππ‘π¦ ππππ’π
*For this project, property value is equal to the cost of the home and the amount of the mortgage loan.
Background
Carl Sagan, a prominent scientist and educator, said: βWe live in a society exquisitely dependent on science
and technology, in which hardly anyone knows anything about science and technology.β He also suggested:
βScience confers power on anyone who takes the trouble to learn it.β As a lifelong educator in the
mathematical and scientific arts, I believe these statements are truer and truer every day. In this project, weβll
investigate some common beliefs and use logical reasoning to come to our own conclusions, based on a
quantitative analysis.
Buying a home is usually an exciting and nerve-wracking event, especially for first-time buyers who may be
beginning their family lives and/or careers. Most people in the United States believe that home ownership
1 More information on closing costs can be researched at: https://www.investopedia.com/mortgage/mortgageguide/closing-costs/ and https://www.zillow.com/sellers-guide/closing-costs-for-sellers/
2 Other fees may include: hazard insurance, heating and electricity, water, sewage, internet/cable, property management
fees, private mortgage insurance
3 Property taxes can vary widely; a national average is used. Maintenance fees usually range from 1%-4%. Homeowners
insurance can vary widely; $100 per month is assumed here. Buyer closing costs are typically 3%-6%, and seller closing
costs are typically 8%-10%. More information on closing costs can be found in the links above.
MATH 123 PROJECT 2
actually means, βowning their home.β However, for many, this is a myth. Most Americans who have home
mortgages will never own their own home, because most will never pay off their mortgage loan (and even if
they manage, property taxes need to be paid).
Not long ago, people lived in their homes for at least the timeframe of the mortgage (15-30 years), up to 40-
50 years. Today, that statistic has radically changed. The average length of a mortgage fluctuates, but
recently it has been an average of 10 years. In some years, it has been as low as 7 years4
. So how does this
work? If families arenβt really owning their homes, who does? Who are the familiesβ βlandlordsββfrom
whom are the families βrenting?β5
In addition to the myth of owning a home, many people believe they are making an investment that will grow
in time. Part of every mortgage payment goes toward paying off the loan (principal), and part goes to paying
interest on the loan. The cumulative sum of all those parts of payments going toward the principal is called
equity. For example, say a loan was for $150,000, and after so many years the buyer owes $130,000. The
difference, $20,000, is how much of the loan has been paid off, and this amount is called equity. However,
you will show that this equity can be less than the interest payments, and that the plethora of fees and costs
diminish the equity into hardly anything that could be considered an βinvestment.β
Calculations/Spreadsheet
Create an amortization schedule for the loan amount/housing cost based on the median salary you found and
a 4.25% APR, 30-year mortgage. Use the schedule and the formulas learned in class to answer the following
questions. Organize the data and results using a spreadsheet, i.e., Excel. Use programmed formulas for any
and all calculations. An example of a data table is shown in Figure 1. Be sure to display your entered and
calculated data in an organized fashion. Use currency/accounting, percent, and decimal formatting to present
your data in a professional matter. You will be graded on accuracy and presentation.
Total length of loan calculations (30 years)
1) What are the mortgage loan amount and mortgage monthly payment?
2) What is the total amount of all mortgage payments? What total interest is paid over the life of the loan?
3) What is the ratio of total interest paid to mortgage loan amount? Express this as a percentage.
4) Using the property tax and maintenance rates, calculate the additional monthly fees from these sources.
5) Using the fees calculated in 4) and the $100 amount for insurance, what is the total monthly payment?
(total monthly payment = mortgage monthly payment + taxes + maintenance + insurance).
6) What is the total amount paid over the course of the loan?
7) What is the ratio of total amount paid (the value calculated in 6) to mortgage loan amount? Express this
as a decimal.
Partial length of loan calculations (7 and 10 years)
8) What are the total closing costs (buyer AND seller)?
9) Use the amortization schedule and report the total equity, interest, mortgage amount, and total amounts
paid for 7 and 10 years.
10) What are the βGrand Totalsβ for 7 and 10 years? (Total paid + Closing costs). This represents the total
βrentβ paid for 7 and 10 years.
11) Subtract the total equity from the Grand Total for 7 and 10 years. This represents the total cost of
βrentingβ for 7 and 10 years.
12) Use the total cost of βrentingβ and calculate the βEffective Monthly Rent,β which is the total cost of
βrentingβ divided by the number of months. Do this for 7 and 10 years.
13) What is the ratio of total interest to total equity for 7 and 10 years? Express these as decimals.
4 https://themortgagereports.com/26307/homebuyer-tenure-how-long-are-people-staying-in-their-houses
5 Quotes are used here because technically speaking βlandlordβ and βrentβ are used in a different context. However,
βmath logicβ (quantitative reasoning) will show there is a very fine line in these definitions.
MATH 123 PROJECT 2
Figure 1. An example of a data table in Excel. Note that the amortization columns extend downward to
month 360, which is 30 years.
If you are having difficulties with any of the tasks in Excel, please watch the tutorial for this project.
However, it is crucial to read the entire project instructions, including the Calculations/Spreadsheet AND
Discussion/Paper sections. PROJECT 2 TUTORIAL (Ctrl + Click to follow link)
Discussion/Paper
Answer the following questions written in the format of a paper, i.e., not a bulleted-list, numbered answers,
etc. Use good grammar, complete sentences, and write the paper to an appropriate audience (a typical person
not trained in MATH 123). Use paragraphs to organize your answers, and think carefully about how you are
going to discuss them (you may want to read all of the questions first). Answers should be justified using
your calculations (quantitative reasoning), and be sure to explain the results in a way that most people can
understand. You will be graded on organization, accuracy, clarity, and basic composition.
MATH 123 PROJECT 2
Total length of loan discussion (30 years)
a) In problem 3, you determined the ratio of total interest paid to mortgage loan amount. Write at least a
sentence explaining what this means. Why is this percentage so much larger than the APR? In your opinion,
does the APR seem misleading?
b) How does the total monthly payment (problem 5) compare to the mortgage monthly payment (problem 1)?
Use a relative change statement with the mortgage monthly payment as the reference value, ππ
.
c) In problem 7, you determined the ratio of total amount paid to mortgage loan amount. Write at least a
sentence explaining what this means.
Partial length of loan discussion (7 and 10 years)
In problem 12, you calculated the βEffective Monthly Rent.β Basically, this number represents what it cost
per month to live in the loaned home. After all fees, costs, interest payments, and equity earned are all tallied
over the time from the first day of purchase, to the last day at selling, this amount is what is paid per month,
effectively, like a βrent.β If this value is more than the total monthly payment calculated in problem 5, then
this suggests that negative equity was earned. If the Effective Monthly Rent is less than the total monthly
payment, then we can view the difference as the amount of effective equity earned per month. Letβs refer to
this as the βEffective Monthly Equity.β (Effective Monthly Equity = Total Monthly Payment β Effective
Monthly Rent)
d) For very short periods of stay, like 5 years or less, the Effective Equity will be negative. Discuss how this
value could be negative; what is the cause of actually losing money on the investment, even though cash
is spent paying off the principal, and thus building equity? Where does this equity go?
e) What is the Effective Monthly Equity for 7 and 10 years? What is the total βEffective Equityβ for 7 and
10 years?
f) Some argue that Effective Monthly Rent should be compared to the Mortgage Monthly Payments, and not
the Total Monthly Payments(Effective Monthly Equity = Mortgage Monthly Payment β Effective Monthly
Rent). They argue that βrentersβ donβt have to pay property taxes, maintenance fees, and other costs
incurred by homeowners, so those fees and costs shouldnβt be included when figuring the build-up of
equity. If this were the case, calculate the Effective Monthly Equity and the Total Effective Equity for the
entire life of the loan (30 years).
g) Consider the answers to parts e) and f). Discuss whether you think this is a significant investment as many
people believe. Use quantitative reasoning to justify your statements.
h) In problem 13 you calculated the ratio of total interest to total equity for 7 and 10 years. Describe in
straightforward terms what these ratios mean.
i) Make a concluding argument about the work done in this project. You are welcome to express opinions in
your conclusion as long as they are supported by facts.
CAUTIONARY NOTE: Be aware that errors in the spreadsheet formulas will result in incorrect values, and
therefore, missed points. Furthermore, since the paper refers to those values, points could be deducted again
for inaccurate results. It is very important to make sure your data is accurate before discussing them in your
paper. Also, make sure to thoroughly justify your answers to the questions. If there is more than one piece of
evidence supporting an argument, be sure to mention it.
