This question is based on the mortgage example in Kohn, pp$.372-376,$

which is also discussed in the lecture notes for Unit $6$Part A$.$

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\mathrm{.}0\%.$

Steps:

$1)$ Fill in cells F$2$:I$2$ of the worksheet for the question using the information above.

$2)$ In cell J$2,$ calculate the monthly payment using the Excel PMT function and the appropriate

cell references. Highlight the cell so I can easily see your answer.

Part B$)$ Create the amortization table for the mortgage and use that table to determine the current

balance due.

$1)$ Fill in the necessary cells in column A with the number of the period that each payment is

received.

$2)$ 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.

$3)$ Fill in the necessary cells in column C with the interest payment made each period, using the

appropriate formula and cell references.

$4)$ Fill in the necessary cells in column B with the principal payment each period, using the

appropriate formula and cell references.

$5)$ 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.

$6)$ Identify the cell in column E that represents the current balance due on the mortgage $($method

$1$ for determining current balance due$).$ Highlight this cell so I can easily see your answer.

Part C$)$ Calculate the current balance due on the mortgage using the Excel PV function $($method

$2).$

In column B just below the amortization table, use the Excel PV function and the appropriate cell

references to calculate the current balance due $($method $2$ for determining current balance due$).$

Highlight this cell so I can easily see your answer.

Part D: Suppose the interest rate $($APR$)$ is now $1\mathrm{.}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 through the month in which

the original mortgage was going to end and that the new mortgage would be for that length of

FIN $5552$ Keeton

Spring $2024$

$6$

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.

Steps:

$1)$ Following the example in the sample spreadsheet, fill in the appropriate cells in columns D

and E with the refinancing information given above $($the new interest rate expressed as an APR

and the closing cost$).$

$2)$ Calculate the new payment in the indicated cell in column B using the Excel PMT function

and the appropriate cell references. Highlight this cell so I can easily see your answer.

$3)$ Calculate the payment savings per period in the indicated cell in column B using the

appropriate formula and cell references. Highlight this cell so I can easily see your answer.

$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. Highlight this cell so I can easily see your

answer.

$5)$ Show in the indicated cell in Column B what your results imply about whether to refinance

$($yes or no$)$ by using the Excel IF function and the appropriate cell references. $($If you are not

familiar with the Excel IF function, consult the document posted in the module for the

assignment.$)$ Highlight this cell so I can easily see your answer.

Part E$)$ Suppose the interest rate falls by only $0\mathrm{.}5$ percentage point instead of $1\mathrm{.}5$ percentage

points. What will be the decision to refinance in this case?

Make the appropriate change in the number in the cell in Column D for the new interest rate and

see if this affects the refinancing decision. In the indicated cell in column B$,$ show what the

decision to refinance is with this alternative interest rate $($yes or no$).$ Highlight this cell so I can

easily see your answer. $($Important: when you are done, make sure to change the number in cell

with the new interest rate back to what it was so I can still see your answer to Part D$).$

Part F$)$ How would your refinancing decision be affected if you did not expect to stay in the

house until the end of the mortgage? $($Do not do any calculations. Just try to reason it out.$)$

Enter your answer in the indicated cell in column B $($refinancing is more likely or less likely$).$

Highlight this cell so I can easily see your answer.