Three years ago you took out a 30-year fixed-rate mortgage for $125,000 with monthly payments. The interest rate (APR) on the mortgage was 13.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:I2 with the information on the mortgage given above.

2) Fill in cell J2 for the payment per period using the PMT function in Excel and the appropriate 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 C 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) Fill 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 PMT function and the appropriate cell references (method 2).

Part B) Suppose the interest rate (APR) is now 1.5 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 $5,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:

1) Fill in the cells D65:F65 with the refinancing information given above.

2) Calculate the new payment in the indicated cell in column B using the Excel PMT function and the appropriate cell references.

3) Calculate the payment savings per period in the indicated cell in column B using the appropriate formula and cell references.

4) Calculate the PV of the total payments savings in the indicated cell in column B using the Excel PV function and the appropriate cell references.

5) Indicate in the indicated cell what your results imply about whether to refinance (yes or no) by using the IF function and the appropriate cell references.