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 90 APR (monthly compounding) while the 15 year fixed rate was at 3 25 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 a 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 the 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 70 and 30 year fixed rates at 3 75 The couple decided to refinance their loan over 15 years at 2 70 Follow the instructions below and answer all questions using formulas in excel Use the template provided below to generate cashflows and answer the below questions In generating the cashflow scheduled you can mimic the example in your notes and book which outlines an amortizing loan What is Jane and Patricks 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 (see notes) Construct a 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 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 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 What is Jane and Patricks monthly mortgage payment after refinancing their mortgage 5 years after they bought the house

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Oct 03, 2024

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.90%

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.90% APR (monthly compounding) while the 15-year fixed rate was at 3.25% 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 a 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 the 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.70% and 30-year fixed rates at 3.75%. The couple decided to refinance their loan over 15- years at 2.70%.

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

Use the template provided below to generate cashflows and answer the below questions. In generating the cashflow scheduled you can mimic the example in your notes and book which outlines an amortizing loan.
What is Jane and Patricks 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 (see notes).

Construct a 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.

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?

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?