Mortgage Analysis:

When giving housing loans, financial institutions check, whether applicants qualify for the loan. For example, your down payment should exceed a certain percentage of the property value and your monthly payments (mortgage, property taxes etc.) should be less than a certain percentage of household's gross (pre-tax) monthly income.

Using the following parameters as variable inputs, please develop a model, which addresses the questions at the bottom

Inputs:

Household annual pre-tax income	$ 197,000
Household liquid assets available for down payment	$ 210,000
House price:	$899,000
Real estate (property) tax:	1.2%
Loan characteristics
Maturity:	30 years
APR:	4.15%
Payment frequency:	monthly
Minimum down payment requirement:	20%
All house-related payments (mortgage, property taxes etc.) as a percentage of gross (pre-tax) income should not exceed	28%

Questions:

-Create loan amortization schedule (double check your calculations by using alternative methods to calculate payments).

-Should this household receive the loan? What is the maximum mortgage loan that can be given to this household?

-Now assume that you have purchased the house exactly 5 years ago and you get an offer to refinance into a new 30 year mortgage with 4.00% APR. The fixed cost of refinancing is a one-time fee of $10,000.

-Is it to your benefit to refinance?

-What factors influence your decision? (Make any additional assumptions you deem necessary.)

Please create a model.