(Round your calculation results to 2 decimal places, whenever necessary)

You are currently in the 9th year of a 30-year fixed rate, fully amortized mortgage with monthly payments, with the lock-in period of 10 years (prepayment penalty is 2% of the remaining balance). Assume that rate doesn’t change over the life of the loan. You originally borrowed $180,000 with a 9.50% annual interest rate. Interest rates are expected to be lower, so you are considering the feasibility of refinancing the loan. To refinance, closing costs are expected to be 3.85% of the new loan amount. Assume no prepayment penalty with new loans.

A) Using a spreadsheet, calculate the new loan payments for every eighth of a percent from a revised interest rate of 7% to 9%. Assume you will refinance at the end of the 9th year of the mortgage with prepayment penalty and closing costs included in the new loan and you do not wish to extend the length of the mortgage beyond its current length (i.e. the maturity of the new loan is 21 years from now).

B) How low must interest rates go in order for you to breakeven?

C) Using the spreadsheet graphics, plot the new interest rates versus the new mortgage payments and show the breakeven point.