Question 2, Part 1 (100 points):

Say that you are interested in purchasing a house that is currently priced at $325,000. The down payment will be 20% of the home value, which you have in cash and the remaining will be financed using a standard 30 year loan at a fixed rate of 4.75% (the current average rate). Determine the monthly payment and create a complete amortization schedule. What is the net interest paid on this loan? WRITE HERE:

You are also interested in shortening the maturity of the loan and instead taking out a 15 year fixed interest loan at a rate of 4.02%. For simplicity, you'd like to be able to use the same amortization table to examine both loans, so you will have to create a IF statement that will stop the payments from occurring after their maturity. In other words, you'd like to stop making payments after month 180 for the 15 year loan (Hint: you will need to place the IF statement in your payment cells). What is your monthly payment and what is the net interest that you will be paying on the loan? What is the difference in your net interest cost between the two loans? WRITE HERE:

Question 2, Part 2 (50 points):

You start to think about the likelihood that you will be living in the same house/location for the next 15 to 30 years and are interested in how much you will owe on the your mortgage if you choose to move and sell your house before the loan matures. Create a table using IPMT, PPMT and PV functions that allow you to determine what portion of your payment will consist of principal, what portion will consist of interest, and the remaining balance on your loan as of that date.

Say you feel that you may need to move in 5 years for employment reasons. What is the remaining balance on your loan if you choose (a) the 15 year mortgage and (b) the 30 year mortgage? Where does the difference come from?