Question 5 (30 points) This question is based on the mortgage example in Kohn, pp. 372-376, which is also discussed in slides 14-24 and 35-45 of Lecture 6 Part A. Four years ago you took out a 30-year fixed-rate mortgage for $75,000 with monthly payments. The interest rate (APR) on the mortgage was 10.0%. Part A) Create an amortization table for the mortgage from the beginning to the present that is just like the sample table in the worksheet for this question. Calculate the current balance due on the mortgage in two ways: 1) by highlighting the appropriate number in the amortization table, and 2) by using the PV function in Excel. Tips: 1) Fill in cells F2:12 with the information on the mortgage given above 2) Fill in cell 12 for the payment per period using the PMT function in Excel and the appropriato cell references 3) Fill in the necessary cells in column A with the number of the period that each payment is received 4) Fill in the necessary cells in column B with the principal remaining (balance due) at the beginning of each period, using the appropriate formula and cell references, 5)Fill in the necessary cells in column with the interest payment made each period, using the appropriate formula and cell references 6) Fill in the necessary cells in column B with the principal payment each period, using the appropriate formula and cell references 7) Pill in the necessary cells in column E with the principal remaining at the end of each period, using the appropriate formula and cell references, Highlight the number in the column representing the current balance due on the mortgage (method 1), 8) Calculate the current balance due on the mortgage in the indicated cell in column B by using the Excel PV function and the appropriate cell references (method 2). Purt B) Suppose the interest rate (APR) is now 2 percentage points below what it was when you took out the mortgage. You are trying to decide whether you should refinance the mortgage. As in the Kohn example, assume that you expect to stay in the house till the year in which the original mortgage was going to end and that the new mortgage would be for that length of time. The closing cost for mortgage refinancing is $4,000. Determine whether you should refinance by constructing a table just like the one in the second part of the sample spreadsheet for this question Tips