Activity 9.1
Mortgages
10 points
Due at the beginning of class, Friday, March 27, 2009
In this activity, you will investigate some considerations that arise
when looking for a home mortgage loan. You will determine basic characteristics
of several options to compare the consequences of the various choices. Purchasing
a home is a big decision. Generally, a down payment of at least 10 percent of
the purchase price of the home is required. A lender will loan you the money
for the rest of the purchase price but with additional closing costs .
These closing costs include direct fees for things
such as a home appraisal and title insurance and also fees for points
charged; each point is 1 percent of
the loan amount.
For a fixed rate mortgage, you are guaranteed that the interest
rate stated when you sign the loan papers will not change for the term of the
loan. With an adjustable rate mortgage (ARM), which is more
attractive to the lender, your interest rate changes over time. ARMs are generally
given as a fixed rate period and then an interval, such as 3/1, which means
the rate is fixed for three years and then will change every year based on a
rate index. In considering mortgage loan options, you will need to take into
account the following:
(1) Interest rate
(2) Amount of the loan
(3) Closing costs, including points
(4) Fixed rate or adjustable rate mortgage
(5) Any prepayment penalties.
You have decided to purchase a home and have enough money for a decent down payment as well as the money to cover direct fees for closing costs (which you’ll assume do not vary much from one bank to another). You are considering several mortgage options and want to evaluate them. Not counting points, you will need a loan for $120,000. If a mortgage option requires you to pay points, you will need to add that money to your loan amount. Assume the mortgage options you are considering have no prepayment penalties.
(Note that questions 1 and 2 are to be done "by hand" with your calculator.)
1. Lender I offers you a fixed rate 15-year mortgage at 5.25
percent APR, compounded monthly, with no points.
1. a. Find your monthly payments under this option, using the
formula (it's in the notes as well as textbook).
1. b. Find the total amount of interest (the money above and
beyond the amount you borrowed) you will pay over the life of the loan.
2. Lender II offers you a fixed rate 30-year mortgage at 5.75
percent APR, compounded monthly, with one point.
2. a. Find your monthly payments under this option, using the
formula (it's in the notes as well as textbook). Don’t forget to add the “point”
to the loan amount.
2. b. Find the total amount of interest (the money above and beyond the amount you borrowed) you will pay over the life of the loan.
3. Excel has a special function that computes the payment
required on a loan. Here are the instructions to use this function to check
the previous computations. (NOTE that the principal in the directions
below is different that the principal used in this assignment.)
Be sure to enter the answers to #3.f. and #3.g. into your Word document.
4. Suppose you think that the payments required for the 15-year
mortgage are too high for you to afford, but that you’d be able to afford higher
payments than the 30-year mortgage requires. You are considering obtaining a
30-year mortgage at the offered rate but paying it off sooner by making larger
payments than are required.
4. a. Use Excel to find the monthly payments needed to pay
the loan off in 20 years and record the monthly payment in your Word document.
The point of interest should be added to the loan amount in this
problem.
4. b. What monthly payments would you need to make in order to pay it off in 25 years? The point of interest should be added to the loan amount in this problem.
5. You want to compare how much interest is paid during the
first two years under each lender’s plan. Excel’s function to calculate interest
can help. Be sure to enter into your Word document the answers to the questions
below. When you are done following all the instructions in #5 below, you should
enter the resulting table into your Word document.
6. You have another mortgage option—Lender III offers you an adjustable rate 30-year mortgage, with no points, at 4.95 percent interest for the first year. After that time, the mortgage rate will likely change, but you don’t know what the new rate will be. Find your monthly payments for the first year under this option and enter that value into your Word document.
7. Let’s suppose that under Lender III’s adjustable rate
mortgage option, the interest rate increases by 1 percent for the second year.
Calculating interest paid for the first two years under Lender III’s plan is
more complicated. Here’s one way to do it.
7. a. First, use a method similar to what you did for Lenders
I and II to find the interest paid for each month of the first year under Lender
III’s plan and record that total in your Word document:
Total interest paid during the first year (as opposed
to over the whole loan) under Lender III’s plan: ________
7. b. Now use your work in #6 to find the total payments made
under this plan during the first year; then find the amount of principal paid
during the first year and the total principal remaining. Record those values
in your Word document:
Total principal paid during the first year (this depends on the total payments
and total interest during the first year): ___________________________
(Note that when calculating this number, if Excel gives you interest as a negative
number, either in red or in parentheses, then be careful if you subtract).
Total principal remaining: ______________________________________
7. c. Finally, use the IPMT function with the new principal remaining as the amount of the loan, the new interest rate of 5.95 percent, and 29 years remaining on the loan, to calculate the interest paid during each month of the second year of Lender III’s plan. Find the total interest paid during the two years under Lender III’s plan and record that total in your Word document:
Total interest paid for the first two years under Lender III’s plan: _______
Please paste the portions of your Excel table used to answer these questions
into your Word document.
8. Consider the monthly payments, the total interest, and any
other factors that you think are important and discuss the pros and cons of
each of the lender’s plans. Which lender would you choose and why?
Summary
In this activity, you learned how to use Excel’s loan payment and interest calculation
functions. You learned how to copy and paste values so they are no longer linked
to cells that may change. Finally, you examined and compared several realistic
options for home mortgage loans.