Assume you are planning on buying a house and need to obtain a loan in the amount of $175,000. The lender provided you the following options:

A 30-year mortgage with an annualized interest rate of 9.74%

A 15-year mortgage with an interest rate year of 9.46%.

Assume a 30-year mortgage for $442,264 as in the option 1, what would happen if the interest rate / year dropped from 9.21% to 7.95%. How much of each year's payment goes to paying interest and how much goes to reducing the principal under the two interest rates?

You Must use excel formula for all calculations. No credit will be given for not using excel formulas. You are required to calculate the followings:

For Option 1:

What is the annual payment required by the two alternative mortgages?

How much of each year's payment goes to paying interest and how much to reducing the principal balance?

For Option 2:

What is the annual payment required by the two alternative mortgages?

How much of each year's payment goes to paying interest and how much to reducing the principal balance?

Between option 1 and option 2, which mortgage would you prefer?