Question

Mortgage Loan Analysis: Mr. Davidson plans to buy a new house at Sugar Land in June 2019. The sale price of the house is $500,000. He plans to pay 20% down payments and borrow additional 80% from Bank of America with a 30-year, 3.875% fixed-rate mortgage loan. He is expected to pay an equal MONTHLY payment starting from July 2019 for a total of 30 years.

(1) Calculate the required monthly payment for Mr. Davidson.

(2) Construct 2019~2021 amortization table (30 months) for Mr. Davidson.

1.    Mr. Davidson should prepare his 2019 tax filings in early 2020. Estimate the total mortgage interest payments for his 2019 tax deduction.  

Answer 1

1: Monthly payment = $1880.95

(Using PMT function in excel)

2: TABLE

Loan Amount	Interest Rate	Term in Years	Monthly Payment
$400,000.00	3.88%	30	$1,880.95
Month	StartingBalance	Interest	Principal	EndingBalance	TotalInterest
1	$400,000.00	$1,291.67	$589.28	$399,410.72	$1,291.67
2	$399,410.72	$1,289.76	$591.18	$398,819.53	$2,581.43
3	$398,819.53	$1,287.85	$593.09	$398,226.44	$3,869.29
4	$398,226.44	$1,285.94	$595.01	$397,631.43	$5,155.22
5	$397,631.43	$1,284.02	$596.93	$397,034.50	$6,439.24
6	$397,034.50	$1,282.09	$598.86	$396,435.64	$7,721.33
7	$396,435.64	$1,280.16	$600.79	$395,834.85	$9,001.49
8	$395,834.85	$1,278.22	$602.73	$395,232.12	$10,279.71
9	$395,232.12	$1,276.27	$604.68	$394,627.44	$11,555.98
10	$394,627.44	$1,274.32	$606.63	$394,020.81	$12,830.30
11	$394,020.81	$1,272.36	$608.59	$393,412.22	$14,102.65
12	$393,412.22	$1,270.39	$610.55	$392,801.67	$15,373.05
13	$392,801.67	$1,268.42	$612.53	$392,189.14	$16,641.47
14	$392,189.14	$1,266.44	$614.50	$391,574.64	$17,907.91
15	$391,574.64	$1,264.46	$616.49	$390,958.15	$19,172.37
16	$390,958.15	$1,262.47	$618.48	$390,339.67	$20,434.84
17	$390,339.67	$1,260.47	$620.48	$389,719.19	$21,695.31
18	$389,719.19	$1,258.47	$622.48	$389,096.71	$22,953.78
19	$389,096.71	$1,256.46	$624.49	$388,472.22	$24,210.24
20	$388,472.22	$1,254.44	$626.51	$387,845.72	$25,464.68
21	$387,845.72	$1,252.42	$628.53	$387,217.19	$26,717.10
22	$387,217.19	$1,250.39	$630.56	$386,586.63	$27,967.49
23	$386,586.63	$1,248.35	$632.60	$385,954.03	$29,215.84
24	$385,954.03	$1,246.31	$634.64	$385,319.39	$30,462.15
25	$385,319.39	$1,244.26	$636.69	$384,682.70	$31,706.41
26	$384,682.70	$1,242.20	$638.74	$384,043.96	$32,948.62
27	$384,043.96	$1,240.14	$640.81	$383,403.15	$34,188.76
28	$383,403.15	$1,238.07	$642.88	$382,760.28	$35,426.83
29	$382,760.28	$1,236.00	$644.95	$382,115.33	$36,662.83
30	$382,115.33	$1,233.91	$647.03	$381,468.29	$37,896.74

3: Interest payments in 2019 = 6 months =

$7,721.33

(As per table above)

WORKINGS

Same formulae can be dragged down till 360 months (30 years) to complete the table.