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)

Col1 Col 2

Col 3

(Col 1 x.06)

Col 4

(Col 2 – Col 3)

Col 5

(Col 1 – Col 4)

YEAR Beginning Balance Total payment Payment Interest Payment Principal Ending Balance

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.

How Our Website Works


It has never been easier to place your order. Fill in the initial requirements in the small order form located on the home page and press “continue” button to proceed to the main order form or press “order” button in the header menu. Starting from there let our system intuitively guide you through all steps of ordering process.


All your payments are processed securely through PayPal. This enables us to guarantee a 100% security of your funds and process payments swiftly.


Next, we match up your order details with the most qualified freelance writer in your field.


Once we have found the most suitable writer for your assignment, they start working on a masterpiece just for you!


Once finished, your final paper will be available for download through your personal dashboard. You will also receive an email notification with a copy of your paper attached to it. Sometimes, the writer may leave a note for you about the order in case there is any additional information that they need to give you.

Leave a Reply

Your email address will not be published.