Please answer the following question, using the information in this screenshot :

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 1 . Using excel, calculate the present value (time 0 value) 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?

Question la) Using the PMIT function excel we pans the following ame rate-0045/12-we convert the annual interest miondly per 15 *12 15 years * 12 months per year pv600,000 PMT- $4,589.96 Ouestion1b) Webuild the cocel sheet loan amortization schokk below * payment is calculated using the PMIT function as eplained ahove Principal eutstanding at beginning ef year 1-principal euestanding atl end of year Intcrest principal outstanding at beginning ofyeu(a intercst rate 12 principal-payment-nt beginning of year- principal Principal Mont at beginning Payment Inlerest Principal atend 54,589.96 $600,000 $4,589.96 52.250 52340 $997 660 5597,660 54,589.96 52241 5239 5995311 559531 54,589 96 52232 5238 5592954 13,667 $4,58996 55 $4539 139 178 $4,58996 54 $4556 $4.573 180 4573 $4,58996 $17 S4573 0 Question 2 We finst calculate the peincipal outstanding at the beginning omoh37 by extending the loan schodule upto month 37. Nowwe calclate wing the PMT functioe and passing the following argumenss rate 0.04S /12 - same as above, comerting a r n monthly interest rate nr- 12 12-12 years remaining 12 months por year pv-principal outstanding at the bepnning of mon 37 1 Monthatbeginning 5 $556,844 $3,44247 20 $354 $555,430 548 30 $4 987 22 Question Here, we calculate the new PMT from month 61 as beloa ne 0.045/12 nper- s *12-since the loan will be repaid over 5 years incad of 10 pv principal outstanding at beginning of moeth 6 3 Month at beginning $484,384 S17 $48 213 180 67 $434 047 64 62 $44,047 $8.971 2 563 $466,853 571 27 $22 12 66 64 $45.622 $0.971.27 1724 $2.24s 91 5668 Question la) Using the PMIT function excel we pans the following ame rate-0045/12-we convert the annual interest miondly per 15 *12 15 years * 12 months per year pv600,000 PMT- $4,589.96 Ouestion1b) Webuild the cocel sheet loan amortization schokk below * payment is calculated using the PMIT function as eplained ahove Principal eutstanding at beginning ef year 1-principal euestanding atl end of year Intcrest principal outstanding at beginning ofyeu(a intercst rate 12 principal-payment-nt beginning of year- principal Principal Mont at beginning Payment Inlerest Principal atend 54,589.96 $600,000 $4,589.96 52.250 52340 $997 660 5597,660 54,589.96 52241 5239 5995311 559531 54,589 96 52232 5238 5592954 13,667 $4,58996 55 $4539 139 178 $4,58996 54 $4556 $4.573 180 4573 $4,58996 $17 S4573 0 Question 2 We finst calculate the peincipal outstanding at the beginning omoh37 by extending the loan schodule upto month 37. Nowwe calclate wing the PMT functioe and passing the following argumenss rate 0.04S /12 - same as above, comerting a r n monthly interest rate nr- 12 12-12 years remaining 12 months por year pv-principal outstanding at the bepnning of mon 37 1 Monthatbeginning 5 $556,844 $3,44247 20 $354 $555,430 548 30 $4 987 22 Question Here, we calculate the new PMT from month 61 as beloa ne 0.045/12 nper- s *12-since the loan will be repaid over 5 years incad of 10 pv principal outstanding at beginning of moeth 6 3 Month at beginning $484,384 S17 $48 213 180 67 $434 047 64 62 $44,047 $8.971 2 563 $466,853 571 27 $22 12 66 64 $45.622 $0.971.27 1724 $2.24s 91 5668