For this case assume:

You have located a home that you wish to purchase and wish to evaluate bank financing options in

order to determine your budget. You$$ve been working with a few banks on potential mortgage terms

and wish to determine for yourself the payment schedule, monthly payment, and most importantly, just

how much interest you will pay over the life of the mortgage. You also want to run a few scenarios to

determine which is the best option for you.

To complete this exercise, you will use multiple aspects of Excel. These include:

$1.$ Setting up and formatting a loan payment $($amortization$)$ schedule

$2.$ Using the $$absolute cell reference$$ and $$autofill$$ features in Excel

$3.$ Using the formula function PMT to calculate monthly payment based on a given interest rate

and length of load

$4.$ Copy a worksheet and adjust given values to answer various $$what$-$if$$ scenarios.

The home you wish to purchase is listed at $$379,900$ and you expect that the seller will accept $$370,000.$

You have saved $$20,000$ as a down payment and are evaluating mortgages for $$350,000$ from a couple

of banks. Here are the mortgage terms:

$1.30-$year, fixed mortgage, $3\mathrm{.}15\%$

$2.15-$year fixed mortgage, $2\mathrm{.}85\%$

Case requirements:

Using Excel:

$1.$ Create a loan amortization schedule for both loan options for the life of the loan. Determine the

monthly payment, and the $ amount of interest and principal paid monthly. Use the format as

illustrated in the text, table $8-2$ and below as your guide in setting up the schedule.

$2.$ Note the following:

a$.$ In the above image, to calculate the monthly payment, you can use the PMT formula.

b$.$ Also, to determine interest and principal portions of the monthly payment, you can use

the Excel functions IPMT and PPMT formulas.

c$.$ You can also solve for payment using

i$.$ Business calculator

ii$.$ Algebraic equation

d$.$ To easily complete the amortization tables, use the following Excel functions:

i$.$ Absolute cell reference ii$.$ Autofill

Once the amortization schedules are completed, answer the following questions:

$1.$ What is the total amount of $ interest paid for each mortgage for the entire $15$ and $30$ year

period?

$2.$ Evaluate the pros and cons of the $15-$year versus $30-$year mortgage

$3.$ You$$ve learned that making one extra payment per year can result in less interest paid as well as

paying off the mortgage sooner. One can do this by making half the monthly payment every $2$

weeks, which results in essentially one extra monthly payment per year.

a$.$ Using the $15-$year mortgage amortization schedule, create a new amortization schedule

using a bi$-$weekly payment $(26$ payments per year$)$

i$.$ Hint: use the worksheet copy function

ii$.$ Then adjust the interest rate and number of payments cells for a bi$-$weekly

payment

b$.$ What is the $ savings in interest and how many months of payments do you save as

compared to the $15-$year monthly amortization?