Part I - Original Mortgage:

When Jane and Patrick Baker were "house hunting" five years ago, the fixed rate on a 30- year mortgage was 4.5% APR (monthly compounding) while the 15-year fixed rate was at 3.5% APR (monthly compounding). After walking through many homes, they finally reached a consensus and decided to buy a $500,000 home.

The couple decided to put 20% down payment in cash and take a loan for the remainder of the house price.They chose the 30-year mortgage, despite the higher interest rate.

Part II - Refinance the Mortgage (pay off old mortgage and open a new mortgage at a new rate):

Currently mortgage rates have come down and the refinancing frenzy in underway. Jane and Patrick have seen Bank of America advertise the 15-year fixed rates at 2.65% and 30-year fixed rates at 3.75%. The couple decided to refinance their loan over 15- years at 2.65%.

Follow the Instructions below and answer all questions using formulas in excel.

1)What is Jane and Patrick's monthly mortgage payment for the original 30-year mortgage

(prior to the refinancing)? Use the =PMT() function in excel for this calculation, check your answer with calculating the Annuity Payment.

2)Construct an cash-flow amortization schedule in excel for the first five years of the loan using formulas.Ensure you use formulas to construct this schedule rather than typing in values.Leave all formulas in the cells for review.

3)During the first 5 years, how much in total cash has the couple paid towards the mortgage?

What percentage of the cash was applied toward interest?

What is the balance on their loan as of the end of the 5^th year?

4)Had the couple originally taken a 15-year mortgage rather than a 30-year mortgage, how much higher would their monthly payment be?

What would be the balance on their loan be at the end of year 5 if they had opted for the 15-year mortgage?

5)What is Jane and Patrick's monthly mortgage payment after refinancing their mortgage?