Essentials of Business Analytics 1st Edition by Jeffrey Camm,James Cochran,Michael Fry,Jeffrey Ohlmann ,David Anderson

Exercise 2

Consider again the mortgage refinance problem faced by Dave and Jana in Problem 16. Assume that they have accepted the refinance offer of a 15-year loan at 3 percent interest rate with out-of-pocket expenses of $2,937. Recall that they are borrowing $208,555.87. Assume there is no prepayment penalty, so that any amount over the required payment is applied to the principle. 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). Recall that the monthly interest that is charged is the monthly loan rate multiplied by the remaining loan balance.