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 and opt for the 30-year lower mortgage payments, despite its higher interest 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%.

Answer the following questions using excel.

What is Jane and Patricks monthly mortgage payment prior to the refinancing? (use the PMT function in excel)

Construct an amortization schedule in excel for the first five years of the loan.

During the first 5 years, how much has the couple paid towards the mortgage? What proportion of this was applied toward interest? What is the balance on their loan today?

Had the couple initially opted for the 15-year mortgage, how much higher would their monthly payment be? What would be the balance on their loan today if they had opted for the 15-year mortgage?

What is Jane and Patricks monthly mortgage payment after refinancing their mortgage?