ANSWER WITH EXCEL AND EXCEL FILE ONLY. SHOW FORMULAS

Let's say you approached a mortgage lender for a $$350,000$ loan to purchase a house. They are

willing to offer you a loan at $7\mathrm{.}5\%$ for a $15$ year term $($with mortgage payments due at the end of

each month during the life of the loan.$)$ Set up a model to answer the following questions.

a$)$ What will be your monthly mortgage payment if you decide to take the loan at the $7\mathrm{.}5\%$ rate?

b$)$ The lender also offers you a lower rate of $6\mathrm{.}875\%$ if you agree to pay an extra $2\%$ of the loan

amount $($$$7,000$ in this example$)$ upfront. This is known as "paying two mortgage points to

buy down the interest rate." What will be your monthly saving on mortgage payment at the

lower interest rate?

c$)$ Is the $$7,000$ upfront payment worth the savings in mortgage payments due to the lower rate

over the life of the loan? Back up your answer with calculations. $($Hint: Compare the present

value of monthly savings to the upfront payment.$)$

d$)$ Suppose you take the view that no matter which rate you sign up for, you will be able to

refinance your mortgage and obtain a much lower rate in five years. This means that the

savings from the lower rate of $6\mathrm{.}875\%$ last only for the next five years, and not the entire

fifteen years. Does this change your answer to c$)$ above? Back up your answer with

calculations.