Question

5. Construct an amortization schedule for the mortgage. If the Halls would pay extra payment of $150 a month starting 49th month, how quickly will they able to payoff the mortgage? Be sure to have a zero balance at the end of amortization.

Loan Amount	180,576	Loan Length in months		240
Interest (APR)	4.250%	Total interest payment			Hint: Be sure to compute the total interest payments using "Sum()" function.
Prepayment	$150	Total # of pmts with prepay			With the prepayments, note that your payments will be shortened.

Month	Beginning Principal	Monthly Payment	Interest	Principal pmt	Outstanding Balance
1	180,576

Answer 1

Monthly interest rate = Interest rate / 12

Monthly interest rate = 4.25% / 12

Monthly interest rate = 0.3542%

Monthly Payment = Principal * (Monthly interest rate / 1 - (1 + Monthly interest rate)^{-no of periods})

Monthly Payment = $180.576 * (0.3542% / 1 - (1 + 0.3542%)^-240

Monthly Payment = $1118.19

Interest = Beginning principal * Monthly interest rate

Interest = $180,576 * 0.3542%

Interest = $478.65

Principal payment = Monthly Payment - Interest

Principal payment = $1118.19 - $478.65

Principal payment = $478.65

Ending Balance = Beginning Balance + Interest - Monthly payment

Ending Balance = $180,576 + $639.54 - $1118.19

Ending Balance = $180,097.35

Amortization Table

Month	Beginning Principal	Monthly Payment	Interest	Principal Payment	Outstanding Balance
1	$180,576.00	$1,118.19	$639.54	$478.65	$180,097.35
2	$180,097.35	$1,118.19	$637.84	$480.34	$179,617.01
3	$179,617.01	$1,118.19	$636.14	$482.05	$179,134.96
4	$179,134.96	$1,118.19	$634.44	$483.75	$178,651.21
5	$178,651.21	$1,118.19	$632.72	$485.47	$178,165.74
6	$178,165.74	$1,118.19	$631.00	$487.19	$177,678.56
7	$177,678.56	$1,118.19	$629.28	$488.91	$177,189.65
8	$177,189.65	$1,118.19	$627.55	$490.64	$176,699.01
9	$176,699.01	$1,118.19	$625.81	$492.38	$176,206.63
10	$176,206.63	$1,118.19	$624.07	$494.12	$175,712.50
11	$175,712.50	$1,118.19	$622.32	$495.87	$175,216.63
12	$175,216.63	$1,118.19	$620.56	$497.63	$174,719.00
13	$174,719.00	$1,118.19	$618.80	$499.39	$174,219.61
14	$174,219.61	$1,118.19	$617.03	$501.16	$173,718.44
15	$173,718.44	$1,118.19	$615.25	$502.94	$173,215.51
16	$173,215.51	$1,118.19	$613.47	$504.72	$172,710.79
17	$172,710.79	$1,118.19	$611.68	$506.50	$172,204.29
18	$172,204.29	$1,118.19	$609.89	$508.30	$171,695.99
19	$171,695.99	$1,118.19	$608.09	$510.10	$171,185.89
20	$171,185.89	$1,118.19	$606.28	$511.91	$170,673.98
21	$170,673.98	$1,118.19	$604.47	$513.72	$170,160.27
22	$170,160.27	$1,118.19	$602.65	$515.54	$169,644.73
23	$169,644.73	$1,118.19	$600.83	$517.36	$169,127.36
24	$169,127.36	$1,118.19	$598.99	$519.20	$168,608.17
25	$168,608.17	$1,118.19	$597.15	$521.03	$168,087.13
26	$168,087.13	$1,118.19	$595.31	$522.88	$167,564.25
27	$167,564.25	$1,118.19	$593.46	$524.73	$167,039.52
28	$167,039.52	$1,118.19	$591.60	$526.59	$166,512.93
29	$166,512.93	$1,118.19	$589.73	$528.46	$165,984.47
30	$165,984.47	$1,118.19	$587.86	$530.33	$165,454.15
31	$165,454.15	$1,118.19	$585.98	$532.21	$164,921.94
32	$164,921.94	$1,118.19	$584.10	$534.09	$164,387.85
33	$164,387.85	$1,118.19	$582.21	$535.98	$163,851.87
34	$163,851.87	$1,118.19	$580.31	$537.88	$163,313.99
35	$163,313.99	$1,118.19	$578.40	$539.79	$162,774.20
36	$162,774.20	$1,118.19	$576.49	$541.70	$162,232.51
37	$162,232.51	$1,118.19	$574.57	$543.62	$161,688.89
38	$161,688.89	$1,118.19	$572.65	$545.54	$161,143.35
39	$161,143.35	$1,118.19	$570.72	$547.47	$160,595.88
40	$160,595.88	$1,118.19	$568.78	$549.41	$160,046.47
41	$160,046.47	$1,118.19	$566.83	$551.36	$159,495.11
42	$159,495.11	$1,118.19	$564.88	$553.31	$158,941.80
43	$158,941.80	$1,118.19	$562.92	$555.27	$158,386.53
44	$158,386.53	$1,118.19	$560.95	$557.24	$157,829.29
45	$157,829.29	$1,118.19	$558.98	$559.21	$157,270.08
46	$157,270.08	$1,118.19	$557.00	$561.19	$156,708.89
47	$156,708.89	$1,118.19	$555.01	$563.18	$156,145.71
48	$156,145.71	$1,118.19	$553.02	$565.17	$155,580.54
49	$155,580.54	$1,268.19	$551.01	$717.17	$154,863.37
50	$154,863.37	$1,268.19	$548.47	$719.71	$154,143.65
51	$154,143.65	$1,268.19	$545.93	$722.26	$153,421.39
52	$153,421.39	$1,268.19	$543.37	$724.82	$152,696.57
53	$152,696.57	$1,268.19	$540.80	$727.39	$151,969.18
54	$151,969.18	$1,268.19	$538.22	$729.96	$151,239.21
55	$151,239.21	$1,268.19	$535.64	$732.55	$150,506.66
56	$150,506.66	$1,268.19	$533.04	$735.14	$149,771.52
57	$149,771.52	$1,268.19	$530.44	$737.75	$149,033.77
58	$149,033.77	$1,268.19	$527.83	$740.36	$148,293.41
59	$148,293.41	$1,268.19	$525.21	$742.98	$147,550.43
60	$147,550.43	$1,268.19	$522.57	$745.61	$146,804.81
61	$146,804.81	$1,268.19	$519.93	$748.26	$146,056.56
62	$146,056.56	$1,268.19	$517.28	$750.91	$145,305.65
63	$145,305.65	$1,268.19	$514.62	$753.56	$144,552.09
64	$144,552.09	$1,268.19	$511.96	$756.23	$143,795.85
65	$143,795.85	$1,268.19	$509.28	$758.91	$143,036.94
66	$143,036.94	$1,268.19	$506.59	$761.60	$142,275.34
67	$142,275.34	$1,268.19	$503.89	$764.30	$141,511.05
68	$141,511.05	$1,268.19	$501.18	$767.00	$140,744.04
69	$140,744.04	$1,268.19	$498.47	$769.72	$139,974.32
70	$139,974.32	$1,268.19	$495.74	$772.45	$139,201.88
71	$139,201.88	$1,268.19	$493.01	$775.18	$138,426.69
72	$138,426.69	$1,268.19	$490.26	$777.93	$137,648.77
73	$137,648.77	$1,268.19	$487.51	$780.68	$136,868.08
74	$136,868.08	$1,268.19	$484.74	$783.45	$136,084.64
75	$136,084.64	$1,268.19	$481.97	$786.22	$135,298.41
76	$135,298.41	$1,268.19	$479.18	$789.01	$134,509.41
77	$134,509.41	$1,268.19	$476.39	$791.80	$133,717.60
78	$133,717.60	$1,268.19	$473.58	$794.61	$132,923.00
79	$132,923.00	$1,268.19	$470.77	$797.42	$132,125.58
80	$132,125.58	$1,268.19	$467.94	$800.24	$131,325.34
81	$131,325.34	$1,268.19	$465.11	$803.08	$130,522.26
82	$130,522.26	$1,268.19	$462.27	$805.92	$129,716.33
83	$129,716.33	$1,268.19	$459.41	$808.78	$128,907.56
84	$128,907.56	$1,268.19	$456.55	$811.64	$128,095.92
85	$128,095.92	$1,268.19	$453.67	$814.52	$127,281.40
86	$127,281.40	$1,268.19	$450.79	$817.40	$126,464.00
87	$126,464.00	$1,268.19	$447.89	$820.30	$125,643.70
88	$125,643.70	$1,268.19	$444.99	$823.20	$124,820.50
89	$124,820.50	$1,268.19	$442.07	$826.12	$123,994.39
90	$123,994.39	$1,268.19	$439.15	$829.04	$123,165.35
91	$123,165.35	$1,268.19	$436.21	$831.98	$122,333.37
92	$122,333.37	$1,268.19	$433.26	$834.92	$121,498.44
93	$121,498.44	$1,268.19	$430.31	$837.88	$120,660.56
94	$120,660.56	$1,268.19	$427.34	$840.85	$119,819.71
95	$119,819.71	$1,268.19	$424.36	$843.83	$118,975.88
96	$118,975.88	$1,268.19	$421.37	$846.82	$118,129.07
97	$118,129.07	$1,268.19	$418.37	$849.82	$117,279.25
98	$117,279.25	$1,268.19	$415.36	$852.82	$116,426.43
99	$116,426.43	$1,268.19	$412.34	$855.85	$115,570.58
100	$115,570.58	$1,268.19	$409.31	$858.88	$114,711.71
101	$114,711.71	$1,268.19	$406.27	$861.92	$113,849.79
102	$113,849.79	$1,268.19	$403.22	$864.97	$112,984.82
103	$112,984.82	$1,268.19	$400.15	$868.03	$112,116.78
104	$112,116.78	$1,268.19	$397.08	$871.11	$111,245.67
105	$111,245.67	$1,268.19	$394.00	$874.19	$110,371.48
106	$110,371.48	$1,268.19	$390.90	$877.29	$109,494.19
107	$109,494.19	$1,268.19	$387.79	$880.40	$108,613.79
108	$108,613.79	$1,268.19	$384.67	$883.51	$107,730.28
109	$107,730.28	$1,268.19	$381.54	$886.64	$106,843.63
110	$106,843.63	$1,268.19	$378.40	$889.78	$105,953.85
111	$105,953.85	$1,268.19	$375.25	$892.94	$105,060.92
112	$105,060.92	$1,268.19	$372.09	$896.10	$104,164.82
113	$104,164.82	$1,268.19	$368.92	$899.27	$103,265.55
114	$103,265.55	$1,268.19	$365.73	$902.46	$102,363.09
115	$102,363.09	$1,268.19	$362.54	$905.65	$101,457.44
116	$101,457.44	$1,268.19	$359.33	$908.86	$100,548.58
117	$100,548.58	$1,268.19	$356.11	$912.08	$99,636.50
118	$99,636.50	$1,268.19	$352.88	$915.31	$98,721.19
119	$98,721.19	$1,268.19	$349.64	$918.55	$97,802.63
120	$97,802.63	$1,268.19	$346.38	$921.80	$96,880.83
121	$96,880.83	$1,268.19	$343.12	$925.07	$95,955.76
122	$95,955.76	$1,268.19	$339.84	$928.35	$95,027.42
123	$95,027.42	$1,268.19	$336.56	$931.63	$94,095.78
124	$94,095.78	$1,268.19	$333.26	$934.93	$93,160.85
125	$93,160.85	$1,268.19	$329.94	$938.24	$92,222.61
126	$92,222.61	$1,268.19	$326.62	$941.57	$91,281.04
127	$91,281.04	$1,268.19	$323.29	$944.90	$90,336.14
128	$90,336.14	$1,268.19	$319.94	$948.25	$89,387.89
129	$89,387.89	$1,268.19	$316.58	$951.61	$88,436.28
130	$88,436.28	$1,268.19	$313.21	$954.98	$87,481.30
131	$87,481.30	$1,268.19	$309.83	$958.36	$86,522.94
132	$86,522.94	$1,268.19	$306.44	$961.75	$85,561.19
133	$85,561.19	$1,268.19	$303.03	$965.16	$84,596.03
134	$84,596.03	$1,268.19	$299.61	$968.58	$83,627.45
135	$83,627.45	$1,268.19	$296.18	$972.01	$82,655.45
136	$82,655.45	$1,268.19	$292.74	$975.45	$81,679.99
137	$81,679.99	$1,268.19	$289.28	$978.91	$80,701.09
138	$80,701.09	$1,268.19	$285.82	$982.37	$79,718.72
139	$79,718.72	$1,268.19	$282.34	$985.85	$78,732.87
140	$78,732.87	$1,268.19	$278.85	$989.34	$77,743.52
141	$77,743.52	$1,268.19	$275.34	$992.85	$76,750.67
142	$76,750.67	$1,268.19	$271.83	$996.36	$75,754.31
143	$75,754.31	$1,268.19	$268.30	$999.89	$74,754.42
144	$74,754.42	$1,268.19	$264.76	$1,003.43	$73,750.99
145	$73,750.99	$1,268.19	$261.20	$1,006.99	$72,744.00
146	$72,744.00	$1,268.19	$257.63	$1,010.55	$71,733.44
147	$71,733.44	$1,268.19	$254.06	$1,014.13	$70,719.31
148	$70,719.31	$1,268.19	$250.46	$1,017.72	$69,701.59
149	$69,701.59	$1,268.19	$246.86	$1,021.33	$68,680.26
150	$68,680.26	$1,268.19	$243.24	$1,024.95	$67,655.31
151	$67,655.31	$1,268.19	$239.61	$1,028.58	$66,626.74
152	$66,626.74	$1,268.19	$235.97	$1,032.22	$65,594.52
153	$65,594.52	$1,268.19	$232.31	$1,035.87	$64,558.64
154	$64,558.64	$1,268.19	$228.65	$1,039.54	$63,519.10
155	$63,519.10	$1,268.19	$224.96	$1,043.23	$62,475.87
156	$62,475.87	$1,268.19	$221.27	$1,046.92	$61,428.95
157	$61,428.95	$1,268.19	$217.56	$1,050.63	$60,378.32
158	$60,378.32	$1,268.19	$213.84	$1,054.35	$59,323.97
159	$59,323.97	$1,268.19	$210.11	$1,058.08	$58,265.89
160	$58,265.89	$1,268.19	$206.36	$1,061.83	$57,204.06
161	$57,204.06	$1,268.19	$202.60	$1,065.59	$56,138.47
162	$56,138.47	$1,268.19	$198.82	$1,069.37	$55,069.11
163	$55,069.11	$1,268.19	$195.04	$1,073.15	$53,995.95
164	$53,995.95	$1,268.19	$191.24	$1,076.95	$52,919.00
165	$52,919.00	$1,268.19	$187.42	$1,080.77	$51,838.23
166	$51,838.23	$1,268.19	$183.59	$1,084.60	$50,753.64
167	$50,753.64	$1,268.19	$179.75	$1,088.44	$49,665.20
168	$49,665.20	$1,268.19	$175.90	$1,092.29	$48,572.91
169	$48,572.91	$1,268.19	$172.03	$1,096.16	$47,476.75
170	$47,476.75	$1,268.19	$168.15	$1,100.04	$46,376.71
171	$46,376.71	$1,268.19	$164.25	$1,103.94	$45,272.77
172	$45,272.77	$1,268.19	$160.34	$1,107.85	$44,164.92
173	$44,164.92	$1,268.19	$156.42	$1,111.77	$43,053.15
174	$43,053.15	$1,268.19	$152.48	$1,115.71	$41,937.44
175	$41,937.44	$1,268.19	$148.53	$1,119.66	$40,817.78
176	$40,817.78	$1,268.19	$144.56	$1,123.63	$39,694.16
177	$39,694.16	$1,268.19	$140.58	$1,127.61	$38,566.55
178	$38,566.55	$1,268.19	$136.59	$1,131.60	$37,434.95
179	$37,434.95	$1,268.19	$132.58	$1,135.61	$36,299.34
180	$36,299.34	$1,268.19	$128.56	$1,139.63	$35,159.72
181	$35,159.72	$1,268.19	$124.52	$1,143.66	$34,016.05
182	$34,016.05	$1,268.19	$120.47	$1,147.72	$32,868.34
183	$32,868.34	$1,268.19	$116.41	$1,151.78	$31,716.56
184	$31,716.56	$1,268.19	$112.33	$1,155.86	$30,560.70
185	$30,560.70	$1,268.19	$108.24	$1,159.95	$29,400.74
186	$29,400.74	$1,268.19	$104.13	$1,164.06	$28,236.68
187	$28,236.68	$1,268.19	$100.00	$1,168.18	$27,068.50
188	$27,068.50	$1,268.19	$95.87	$1,172.32	$25,896.18
189	$25,896.18	$1,268.19	$91.72	$1,176.47	$24,719.70
190	$24,719.70	$1,268.19	$87.55	$1,180.64	$23,539.06
191	$23,539.06	$1,268.19	$83.37	$1,184.82	$22,354.24
192	$22,354.24	$1,268.19	$79.17	$1,189.02	$21,165.22
193	$21,165.22	$1,268.19	$74.96	$1,193.23	$19,972.00
194	$19,972.00	$1,268.19	$70.73	$1,197.45	$18,774.54
195	$18,774.54	$1,268.19	$66.49	$1,201.70	$17,572.85
196	$17,572.85	$1,268.19	$62.24	$1,205.95	$16,366.89
197	$16,366.89	$1,268.19	$57.97	$1,210.22	$15,156.67
198	$15,156.67	$1,268.19	$53.68	$1,214.51	$13,942.16
199	$13,942.16	$1,268.19	$49.38	$1,218.81	$12,723.35
200	$12,723.35	$1,268.19	$45.06	$1,223.13	$11,500.22
201	$11,500.22	$1,268.19	$40.73	$1,227.46	$10,272.77
202	$10,272.77	$1,268.19	$36.38	$1,231.81	$9,040.96
203	$9,040.96	$1,268.19	$32.02	$1,236.17	$7,804.79
204	$7,804.79	$1,268.19	$27.64	$1,240.55	$6,564.24
205	$6,564.24	$1,268.19	$23.25	$1,244.94	$5,319.30
206	$5,319.30	$1,268.19	$18.84	$1,249.35	$4,069.95
207	$4,069.95	$1,268.19	$14.41	$1,253.77	$2,816.18
208	$2,816.18	$1,268.19	$9.97	$1,258.21	$1,557.96
209	$1,557.96	$1,268.19	$5.52	$1,262.67	$295.29
210	$295.29	$295.29	$1.05	$294.24	$0.00

Based on the amortization table Halls would be able to pay off the loan in 210 months with a balance amount of $295.29 to be made to close the loan in the 210^th month.