Throughout this question, assume annual interest rate is 3.6% with monthly compounding. You are a loan officer in the mortgage department of a local bank. A customer, who is also a Stevens alum, walks in and applies for a $750,000 loan to buy a starter home in Hoboken. The standard terms your bank have been offering to previous customers are as followed:

- Contract A: a 15-year fixed rate loan, with an annual rate of 3.6% and with fixed monthly installment.

Question 1

1. Calculate the monthly payment, denote it as XA. Provide some details on how you get the number in excel or with a financial calculator.
2. Create an Excel spreadsheet and calculate how your interest payments, principal payments, and the outstanding loan balances changes over time for Contract A (You do NOT need to print the entire excel sheet; just few cells from the beginning and end would be enough. However, you should explain your approach.)

Question 2

Upon receiving terms of contract A, your customer realizes that monthly payment of XA (calculated above) is beyond her earning power in year 1, 2 and 3, but it will be well within her means from year 4 onwards. You then decide to offer her the following alternative:

Contract B: a 15-year fixed rate loan with a variable payment schedule, with a monthly payment of 12XA, in the first three years (36 months), and a monthly payment of XB for the remaining 12 years.

Calculator in excel the value of XB and create a modified version of amortization table for contract B.

Question 3

Suppose the customer chose Contract B and has made on-time payment for 5 years (60 monthly payments). At the beginning of year 6, she realizes that her career has taken off so well that she wants to pay off her remaining mortgage in the next five years instead of ten years. What is her monthly payment XC between year 6 and year 10 in this scenario? Show your work.

Question 4

Mortgage interests are tax deductible, meaning that the one can subtract the amount of interest (not the total payment) from her annual taxable income. Suppose the customers marginal income tax rate is 24% in year 1, 2 and 3, and 32% in year 4 and 5, and 35% from year 6 onwards . Using excel, calculate the present value (time 0 value, assuming monthly compounding) of all her future tax savings in contract A, contract B, and the earlier repayment scenario described in Question 3. The number you calculate is also called the tax shield value of debt. Which contract offers highest amount of tax shield value of debt?