You have located a home that you wish to purchase

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.15%
2). 15-year fixed mortgage, 2.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.

FIGURE 8-2 Amortization Table for a $150,000 Loan, 6 Percent Annual Interest Rate, 10 year Term

Loan Amortization Schedule

Amount Borrowed: $150,000
Interest Rate: 6.0%
Term: 10 years
Required Payments: $20,380.19 (found using equation 8-4a)

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-monthly payment (26 payments per year)
i. Hint: use the worksheet copy function to create a copy of the first worksheet,
ii. Then adjust the interest rate and number of payments cells for a bi-monthly 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?
Provide a detailed summary of the loan analysis, including the pros and cons of the two mortgages. Include what you learned from the exercise, both from a finance and budget perspective as well as interest expense perspective.