Part 2 - Mortgage

Now that you have enough money for a down payment on the house that you want to buy, it's time to look into getting a mortgage. You have agreed to buy the house for $125,000 and to make a 20% down payment on the house. You have also found a bank that will finance the remaining balance of the house.

You must now choose between a 20-year mortgage and a 30-year mortgage. The 20-year mortgage has an annual interest rate of 4.52% and the 30-year mortgage has an annual interest rate of 4.96%. Both rates are compounded monthly.

What are the advantages and disadvantages of the 30-year and the 20-year mortgages?

We can answer this question by creating an Excel spreadsheet that details the payments made each month as well as how much of the payment goes toward interest, and how much goes toward the balance of the home loan. In effect, create an amortization schedule for each type of mortgage, and compare the results.

1.First, create an amortization schedule for the 30-year mortgage. At the top of the spreadsheet, set the parameters for this situation. First, the present value of the mortgage (PV). This is the cost of the house minus the down payment that you have already made. The other parameters are the annual interest rate (r) and the number of years to pay off the mortgage (t).

2.Set up the following 6 columns for the 30-year mortgage:

Period - Keeps track of the number of periods, in this case months, of the loan. Start with Period 0.

Years - Keeps track of the number of years of the mortgage.

Payment - Gives the amount of your monthly mortgage payment. This amount will stay constant throughout the entire 30 years. To calculate the payment amount, we could use the present value equation from Section 3-4, but instead we will use the Excel function "PMT". The "PMT" function calculates the monthly payment given the parameters:

rate - This is the interest rate per month.

nper - This is the total number of periods of the mortgage.

pv - This is the present value or initial amount of the mortgage.

The payment amount is automatically given as a negative value. In order to change this to a positive number, simply put a negative sign in front of the PMT.

Interest Paid - Gives the amount of the monthly payment that goes toward interest on the loan. This can be calculated by multiplying the previous unpaid balance by the interest rate per period.

Equity Paid - Gives the amount of the monthly payment that goes toward paying off the loan itself. This can be calculated by taking the monthly payment and subtracting the interest paid. The leftover is the equity paid.

Unpaid Balance - Gives the amount of the mortgage that is still unpaid. Only the equity paid goes toward the unpaid balance. It will decrease over the life of the loan until it becomes $0 when the loan is paid off.

3.Fill out the spreadsheet for 30 years when the Unpaid Balance of the loan should be $0. Calculate the total amount of the monthly payments, and well as the total amount of interest paid and equity paid.

4.Repeat this process by creating another amortization schedule for the 20-year loan.

5.Compare your results for the 30-year and 20-year mortgages by making a table with the Total Payments, and Total Interest Paid for the 30-year and 20-year mortgages.