A few years back, Dave and Jana bought a new home. They borrowed $230,415 at an annual fixed rate of 5.49% (15year term) with monthly payments of $1,881.46. They just made their 25th payment, and the current balance on the loan is $208,555.87. All monthly payments are made at the end of the month. Interest rates are at an alltime low, and Dave and Jana are thinking of refinancing to a new 15year fixed loan. Their bank has made the following offer: 15year term, 3.5%, plus outofpocket costs of $2,937. The outofpocket costs must be paid in full at the time of refinancing. Build a spreadsheet model to evaluate this offer. The savings from refinancing occur over time, and therefore need to be discounted back to current dollars using a monthly inflation rate r of 0.003.

Requirements:

a) Use your model to calculate the savings in current dollars associated with the refinanced loan versus staying with the original loan.

b) Assume that Dave and Jana have accepted the refinance offer of a 15year loan at 3.5% interest rate with outofpocket expenses of $2,937. Assume that there is no prepayment penalty, so that any amount over the required payment is applied to the principal. Construct a model so that you can use Goal Seek to determine the monthly payment that will allow Dave and Jana to pay off the loan in 12 years. Do the same for 10 and 11 years. Which option for prepayment, if any, would you choose and why? (Hint: Break each monthly payment up into interest and principal [the amount that is deducted from the balance owed]).