Question

In: Finance

5. Construct an amortization schedule for the mortgage. If the Halls would pay extra payment of...

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

Expert Solution

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.

jeff jeffy answered 2 years ago

Mortgage Payable The following amortization schedule can be used for the January, 20x3 mortgage payment on...

Mortgage Payable The following amortization schedule can be used for the January, 20x3 mortgage payment on the 7.0%, 30- year mortgage. Month Payment Interest Principal Balance January $3,500 $1,867 $1,633 $320,000 $318,367 Jan 31 The monthly payment for January of the mortgage payable was made. Required: What is the correct journal entry for this transaction and why?

Build a amortization table for the loan below add extra payment to pay off early by...

Build a amortization table for the loan below add extra payment to pay off early by no more the 6 months (the loan payment must last 4.5 years even with early payoff) . Show the money saved. $35,000 loan, 5 years, 5.9% interest, monthly payments with no balloon. Create an annuity solution to find the payment in Excel (that's the N I/Y PV PMT FV) and then do the amortization table for it.

calculate a loan amortization schedule for a $10,000 loan, 5% annual interest, one payment a year...

calculate a loan amortization schedule for a $10,000 loan, 5% annual interest, one payment a year for 10 years, starting on 1/1/2010. All calculations must be shown, i.e., do not use a “package” to complete this question. Loan period starts from 1/1/2010. I mean the first payment is to be made on 1/1/2010.

7. Construct a LOAN AMORTIZATION schedule for a 3 year 5% loan of $100, 000. Please...

7. Construct a LOAN AMORTIZATION schedule for a 3 year 5% loan of $100, 000. Please show your calculations clearly and include calculated answers in the table below. (Please only do this question if you are certain how to do it) Amortization Schedule for a Loan of $100 000 Repaid Over 3 Years at 5% Year Payment amount Interest Paid Principal repaid Outstanding loan balance 0 1 2 3

What is the amortization schedule for a mortgage of $1,600,000 in with 1). an initial ten...

What is the amortization schedule for a mortgage of $1,600,000 in with 1). an initial ten year fixed rate of 6.5%; twenty year amortization (monthly); ballooning at the end of the tenth year; and 2) Second ten year rate "capped" at 7.5%; monthly amortization over the remaining ten years on the balance rolled-over from #1.

Construct the amortization schedule for a $15000 debt that is to be amortized in 10 quarterly...

Construct the amortization schedule for a $15000 debt that is to be amortized in 10 quarterly payments at 3% interest per quarter on the unpaid balance.

Problem 5-50 Amortization Schedule (LG9) Create the amortization schedule for a loan of $4,300, paid monthly...

Problem 5-50 Amortization Schedule (LG9) Create the amortization schedule for a loan of $4,300, paid monthly over two years using an 9 percent APR. (Round your answers to 2 decimal places.) Month Beginning Balance Total Payment Interest Paid Principal Paid Ending Balance 1 2 3 4 5 6 7 ...

Problem 5-49 Amortization Schedule (LG9) Create the amortization schedule for a loan of $14,000, paid monthly...

Problem 5-49 Amortization Schedule (LG9) Create the amortization schedule for a loan of $14,000, paid monthly over three years using a 9 percent APR. (Round your answers to 2 decimal places.) Month Beginning Balance Total Payment Interest Paid Principal Paid Ending Balance 1 2 3 4 5 6 7...

Create a loan amortization schedule in Excell for a $275,000 mortgage that will be repaid over...

Create a loan amortization schedule in Excell for a $275,000 mortgage that will be repaid over 20 years with monthlypayments. The annual interest rate is 5.5 %. What is your monthly payment? $ What is the total dollar amount of payments made over the life of this loan? $__ What is the total dollar amount of interest paid over the life of this loan? $_ How many months will it take to pay off the loan if you pay an extra $100...

In excel, Produce the amortization schedule for a $800K mortgage at 10 years with a rae...

In excel, Produce the amortization schedule for a $800K mortgage at 10 years with a rae of 3.25 percent / year. Provide a column for labels and the excel functions used.