Question

Long questions 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 X A. 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 XA, in the first three years (36 months), and a monthly payment of X for the remaining 12 years. Calculator in excel the value of X 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 customer's 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?

Answer 1

Question 1]

1]

Monthly loan payment is calculated using PMT function in Excel :

rate = 3.6% / 12   (converting annual rate into monthly rate)

nper = 15*12 (15 year loan with 12 monthly payments each year)

pv = -750000 (Loan amount. This is entered with a negative sign because it is an amount borrowed)

PMT is calculated to be $5,398.53

The monthly loan payment is $5,398.53

A1 X for =PMT(3.6%/12,15*12,-750000) D E F B C A $5,398.53 1

2]

Interest in any month = principal outstanding at beginning of month * 3.6% / 12

Principal portion of monthly payment = monthly payment minus interest portion of payment

principal outstanding at end of month = principal outstanding at beginning of month minus principal portion of monthly payment

G H 1 Month end $5,398.53 Principal Principal outstanding at outstanding at beginning Payment Interest Principal 1 $ 750,000.00 $ 5,398.53 $ 2,250.00 $ 3,148.53 $ 746,851.47 2 $ 746,851.47 $ 5,398.53 $ 2,240.55 $ 3,157.97 $ 743,693.50 3 $ 743,693.50 $ 5,398.53 $ 2,231.08 $ 3,167.44 $ 740,526.06 4 $ 740,526.06 $ 5,398.53 $ 2,221.58 $ 3,176.95 $ 737,349.11 5 $ 737,349.11 $ 5,398.53 $ 2,212.05 $ 3,186.48 $ 734,162.63 6 $ 734,162.63 $ 5,398.53 $ 2,202.49 $ 3,196.04 $ 730,966.60 7 $ 730,966.60 $ 5,398.53 $ 2,192.90 $ 3,205.63 $ 727,760.97 8 $ 727,760.97 $ 5,398.53 $ 2,183.28 $ 3,215.24 $ 724,545.73 9 $ 724,545.73 $ 5,398.53 $ 2,173.64 $ 3,224.89 $ 721,320.84 10 $ 721,320.84 $ 5,398.53 $ 2,163.96 $ 3,234.56 $ 718,086.28 11 $ 718,086.28 $ 5,398.53 $ 2,154.26 $ 3,244.27 $ 714,842.01 12 $ 714,842.01 $ 5,398.53 $ 2,144.53 $ 3,254.00 $ 711,588.01 13 $ 711,588.01 $5,398.53 $ 2,134.76 $ 3,263.76 $ 708,324.25 14 $ 708,324.25 $ 5,398.53 $ 2,124.97 $ 3,273.55 $ 705,050.70 15 $ 705,050.70 $ 5,398.53 $ 2,115.15 $ 3,283.37 $ 701,767.32 16 $ 701,767.32 $ 5,398.53 $ 2,105.30 $ 3,293.22 $ 698,474.10 17 $ 698,474.10 $ 5,398.53 $ 2,095.42 $ 3,303.10 $ 695,171.00 18 $ 695,171.00 $ 5,398.53 $ 2,085.51 $ 3,313.01 $ 691,857.98 19 $ 691,857.98 $ 5,398.53 $ 2,075.57 $ 3,322.95 $ 688,535.03

163 164 166 167 168 | А В C D E Principal Principal outstanding at outstanding at 1 Month beginning Payment Interest Principal end 162 $ 99,558.39 $ 5,398.53 $ 298.68 $ 5,099.85 $ 94,458.54 163 $ 94,458.54 $ 5,398.53 $ 283.38 $ 5,115.15 $ 89,343.39 165 164 $ 89,343.39 $ 5,398.53 $ 268.03 $ 5,130.50 $ 84,212.90 165 $ 84,212.90 $ 5,398.53 $ 252.64 $ 5,145.89 $ 79,067.01 166 $ 79,067.01 $ 5,398.53 $ 237.20 $ 5,161.32 $ 73,905.69 167 $ 73,905.69 $ 5,398.53 $ 221.72 $ 5,176.81 $ 68,728.88 169 168 $ 68,728.88 $ 5,398.53 $ 206.19 $ 5,192.34 $ 63,536.54 169 $ 63,536.54 $ 5,398.53 $ 190.61 $ 5,207.92 $ 58,328.62 170 $ 58,328.62 $ 5,398.53 $ 174.99 $ 5,223.54 $ 53,105.08 171 $ 53,105.08 $ 5,398.53 $ 159.32 $ 5,239.21 $ 47,865.87 173 172 $ 47,865.87 $ 5,398.53 $ 143.60 $ 5,254.93 $ 42,610.95 173 $ 42,610.95 $ 5,398.53 $ 127.83 $ 5,270.69 $ 37,340.25 174 $ 37,340.25 $ 5,398.53 $ 112.02 $ 5,286.50 $ 32,053.75 175 $ 32,053.75 $ 5,398.53 $ 96.16 $ 5,302.36 $ 26,751.38 177 176 $ 26,751.38 $ 5,398.53 $ 80.25 $ 5,318.27 $ 21,433.11 177 $ 21,433.11 $ 5,398.53 $ 64.30 $ 5,334.23 $ 16,098.89 178 $ 16,098.89 $ 5,398.53 $ 48.30 $ 5,350.23 $ 10,748.66 180 179 $ 10,748.66 $ 5,398.53 $ 32.25 $ 5,366.28 $ 5,382.38 181 180 $ 5,382.38 $ 5,398.53 $ 16.15 $ 5,382.38 $ 0.00 171 175 179

А G н | Principal outstanding at beginning Payment 750000 =$H$2 =F2 =$H$2 =F3 =$H$2 =F4 =$H$2 1 Month 2 1 32 4 3 =PMT(3.6%/12, 15*12,-750000) =$H$2 ☺ =F8 76 87 9 8 10 9 11 10 12 11 =F9 Interest =B2*3.6%/12 =B3*3.6%/12 =B4*3.6%/12 =B5*3.6%/12 =B6*3.6%/12 =B7*3.6%/12 =B8*3.6%/12 =B9*3.6%/12 =B10*3.6%/12 =B11*3.6%/12 =B12*3.6%/12 =B13*3.6%/12 =B14*3.6%/12 =B15*3.6%/12 =B16*3.6%/12 =B17*3.6%/12 =B18*3.6%/12 =B19*3.6%/12 =B20*3.6%/12 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 =$H$2 F Principal outstanding at end =B2-E2 =B3-E3 =B4-E4 =B5-E5 =B6-E6 =B7-E7 =B8-E8 =B9-E9 =B10-E10 =B11-E11 =B12-E12 =B13-E13 =B14-E14 =B15-E15 =B16-E16 =B17-E17 =B18-E18 =B19-E19 =B20-E20 Principal =C2-D2 =C3-D3 =C4-D4 =C5-D5 =C6-D6 =C7-07 =C8-D8 =C9-09 =C10-D10 =C11-D11 =C12-D12 =C13-D13 =C14-D14 =C15-D15 =C16-D16 =C17-D17 =C18-D18 =C19-D19 =C20-D20 =F10 =F11 =F12 =F13 =F14 =F15 =F16 =F17 =F18 =F19

ДА В Principal outstanding 1 Month lat beginning Payment 162 =F162 =$H$2 163 =F163 =$H$2 165 164 =F164 =$H$2 166 165 =F165 =$H$2 167 166 =F166 =$H$2 168 167 =F167 =$H$2 169 168 =F168 =$H$2 170 169 =F169 =$H$2 171 170 =F170 =$H$2 172 171 =F171 =$H$2 173 172 =F172 =$H$2 174 173 =F173 =$H$2 175 174 =F174 =$H$2 176 175 =F175 =$H$2 177 176 =F176 =$H$2 178 177 =F177 =$H$2 =F178 =$H$2 180 179 =F179 =$H$2 181 180 =F180 =$H$2 102 Interest =B163*3.6%/12 =B164*3.6%/12 =B165*3.6%/12 =B166*3.6%/12 =B167*3.6%/12 =B168*3.6%/12 =B169*3.6%/12 =B170*3.6%/12 =B171*3.6%/12 =B172*3.6%/12 =B173*3.6%/12 =B174*3.6%/12 =B175*3.6%/12 =B176*3.6%/12 =B177*3.6%/12 =B178*3.6%/12 =B179*3.6%/12 =B180*3.6%/12 =B181*3.6%/12 Principal =C163-D163 =C164-D164 =C165-D165 =C166-D166 =C167-D167 =C168-D168 =C169-D169 =C170-D170 =C171-D171 =C172-D172 =C173-D173 =C174-D174 =C175-D175 =C176-D176 =C177-D177 =C178-D178 =C179-0179 =C180-D180 =C181-D181 Principal outstanding at end =B163-E163 =B164-E164 =B165-E165 =B166-E166 =B167-E167 =B168-E168 =B169-E169 =B170-E170 =B171-E171 =B172-E172 =B173-E173 =B174-E174 =B175-E175 =B176-E176 =B177-E177 =B178-E178 =B179-E179 =B180-E180 =B181-E181 178