Question

Throughout this question, assume annual interest rate is 4.5% 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 $600,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 4.5% and with fixed monthly installment.

Question 1 (10 pts),

(a) Calculate the monthly payment, denote it as XA using mathematical formulas.

(b) 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 (10 pts), 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 variable rate loan, with a monthly payment of (¾)XA , in the first three years (36 months), and a monthly payment of XB for the remaining 12 years.

Calculate using mathematical formulas the value of XB that will make the present value of cash flows in Contract B equal to that in Contract A.

Question 3. (10 pts) 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 �& between year 6 and year 10 in this scenario? Show your work.

Question 4. (10 pts) Mortgage interests are tax deductible, meaning that the one can subtract the amountThroughout this question, assume annual interest rate is 4.5% with monthly compounding.
                                                                                                                   

Answer 1

Question 1 (a) :

Using the PMT function excel, we pass the following arguments to calculate X_A:

rate = 0.045 / 12 - we convert the annual interest rate into a monthly interest rate

nper = 15 *12 - 15 years * 12 months per year

pv = 600,000 - the amount of loan

PMT = $4,589.96

Question 1 (b) :

We build the excel sheet loan amortization schedule as below :

• payment is calculated using the PMT function as explained above
• Principal outstanding at beginning of year 1 = principal outstanding at end of year 0
• Interest = principal outstanding at beginning of year * (annual interest rate / 12)
• principal = payment - interest
• principal outstanding at end of year = principal outstanding at beginning of year - principal

Month	Principal outstanding at beginning	Payment	Interest	Principal	Principal outstanding at end
0		$4,589.96			$600,000
1	$600,000	$4,589.96	$2,250	$2,340	$597,660
2	$597,660	$4,589.96	$2,241	$2,349	$595,311
3	$595,311	$4,589.96	$2,232	$2,358	$592,954

178	$13,667	$4,589.96	$51	$4,539	$9,129
179	$9,129	$4,589.96	$34	$4,556	$4,573
180	$4,573	$4,589.96	$17	$4,573	($0)

Question 2 :

We first calculate the principal outstanding at the beginning of month 37 by extending the loan schedule upto month 37. Now, we calculate X_B using the PMT function and passing the following arguments :

rate = 0.045 / 12 - same as above, converting annual interest rate into monthly interest rate

nper = 12 * 12 - 12 years remaining * 12 months per year

pv = principal outstanding at the beginning of month 37

Question 3 :

Here, we calculate the new PMT from month 61 as below :

rate = 0.045 / 12

nper = 5 *12 - since the loan will be repaid over 5 years instead of 10

pv = principal outstanding at beginning of month 61

Monthly payment between year 6 and year 10 = $8,971.27