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 has a successful job, walks in and applies for a $600,000 loan to buy a starter home in NYC. 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

Contract B: a 15-year variable rate loan, with a monthly payment of 3/4 Xa, in the first three years (36 months), and a monthly payment of Xa for the remaining 12 years.

Earlier Repayment Scenario: 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. Xc is her payments from years 6 to 10.

Xa = $4589.96 , Xb = $4987.47

Question: 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 onward. 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. 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

Contract A

Year	Month	Total months	Opening balance ('= previous month's closing balance)	Interest portion ('=600,000*4.5%/12)	Monthly payment ('=PMT(4.5%/12,15*12,-600000,0,0))	Principal repaid ('=monthly payment - interest)	Closing balance ('=opening balance- Principal repaid)	Tax rate	Tax shield ('=tax rate* interest portion)	PV of tax shield ('=PV(4.5%/12,"total months",PMT=0,FV="tax shield",0)
1	1	1	$ 600,000.00	$ 2,250.00	$ 4,589.96	$ 2,339.96	$ 597,660.04	24%	$ 540.00	$537.98
	2	2	$ 597,660.04	$ 2,241.23	$ 4,589.96	$ 2,348.73	$ 595,311.31	24%	$ 537.89	$533.88
	3	3	$ 595,311.31	$ 2,232.42	$ 4,589.96	$ 2,357.54	$ 592,953.76	24%	$ 535.78	$529.80
	4	4	$ 592,953.76	$ 2,223.58	$ 4,589.96	$ 2,366.38	$ 590,587.38	24%	$ 533.66	$525.73
	5	5	$ 590,587.38	$ 2,214.70	$ 4,589.96	$ 2,375.26	$ 588,212.12	24%	$ 531.53	$521.67
	6	6	$ 588,212.12	$ 2,205.80	$ 4,589.96	$ 2,384.16	$ 585,827.96	24%	$ 529.39	$517.63
	7	7	$ 585,827.96	$ 2,196.85	$ 4,589.96	$ 2,393.10	$ 583,434.85	24%	$ 527.25	$513.61
	8	8	$ 583,434.85	$ 2,187.88	$ 4,589.96	$ 2,402.08	$ 581,032.77	24%	$ 525.09	$509.60
	9	9	$ 581,032.77	$ 2,178.87	$ 4,589.96	$ 2,411.09	$ 578,621.69	24%	$ 522.93	$505.61
	10	10	$ 578,621.69	$ 2,169.83	$ 4,589.96	$ 2,420.13	$ 576,201.56	24%	$ 520.76	$501.63
	11	11	$ 576,201.56	$ 2,160.76	$ 4,589.96	$ 2,429.20	$ 573,772.36	24%	$ 518.58	$497.66
	12	12	$ 573,772.36	$ 2,151.65	$ 4,589.96	$ 2,438.31	$ 571,334.04	24%	$ 516.40	$493.71
2	1	13	$ 571,334.04	$ 2,142.50	$ 4,589.96	$ 2,447.46	$ 568,886.59	24%	$ 514.20	$489.78

Sum of PV

$54,313.28

=tax shield

Contract B- no early repay

Sum of PV

$62,092.22

=tax shield

Contract B- early repay

Sum of PV

$59,529.00

=tax shield

Note- in general contract b, when the customer is not making earlier repayment, in the first 3 year period the loan repaid is less than the minimum amount required to be paid to pay off full loan in 15 years. Hence, at the end of 15 years, there is still some principal outstanding. In this method, the total interest paid is higher over the 15 year period. Hence, the tax shield is higher as well.

Note- in general contract b, when the customer is making earlier repayment, in the first 3 year period the loan repaid is less than the minimum amount required to be paid to pay off full loan in 15 years. From year 6 onwards, the payment is higher. However, it is still not enough to repay full loan in 15 years. Hence, at the end of 15 years, there is still some principal outstanding. In this method, the total interest paid is higher than contract A over the 15 year period. Hence, the tax shield is higher as well. Additionally, amount Xc is not mentioned. Hence, Xb is assumed to be equal to Xc.

Note- Repayment schedule for all months could not be included due to space constraint. However, the formula remains the same over full life. Hence, formula is mentioned. Please download the image and zoom to view better.