Question

At one point in time when you took out a mortgage, a paper copy of an amortization table was provided to you detailing how much of each payment went towards the principal and how much went towards the interest as well as showing the remaining amount owed after each payment was made. Suppose that you want to purchase a home for $240,000 using a down payment as $91,088 and you finance the rest of the purchase by taking out a 15-year mortgage at 4.5% interest compounded monthly. Construct an amortization table for the entire length of the loan making sure to include columns for the payment number, the amount of the payment, the amount of each payment that goes towards the principal, the amount of each payment that goes towards the interest, and the amount still owed on the loan after the particular payment is made (you may want to create other columns in addition to these, but whether or not you do will be dependent on your spreadsheet set-up). A couple of tips/hints that I will offer are that you may want to refer to the last example in the section 11.6 and the last example in the section 6.3 lectures for a little help with getting the equations set up, the amounts that go towards principal and interest will differ from one payment to the next, and after the 180th payment has been made you should owe $0 on the loan.

Answer 1

Amount financed = 240,000 - 91,088 = $148,912. This is Pv. Nper = 15 years*12 = 180 and rate = 4.5%/12

Amount paid each month = PMT(4.5%/12, 180, -148912) = 1139.07

The amortization table is shown below:

Payment no.	Loan balance at the start of the month	Amount of payment	Interest	Principal paid	Loan balance at the end of the month
1	148,912.00	1,139.17	558.42	580.75	148,331.25
2	148,331.25	1,139.17	556.24	582.92	147,748.33
3	147,748.33	1,139.17	554.06	585.11	147,163.22
4	147,163.22	1,139.17	551.86	587.30	146,575.91
5	146,575.91	1,139.17	549.66	589.51	145,986.41
6	145,986.41	1,139.17	547.45	591.72	145,394.69
7	145,394.69	1,139.17	545.23	593.94	144,800.75
8	144,800.75	1,139.17	543.00	596.16	144,204.59
9	144,204.59	1,139.17	540.77	598.40	143,606.19
10	143,606.19	1,139.17	538.52	600.64	143,005.54
160	22,963.44	1,139.17	86.11	1,053.05	21,910.39
161	21,910.39	1,139.17	82.16	1,057.00	20,853.39
162	20,853.39	1,139.17	78.20	1,060.97	19,792.42
163	19,792.42	1,139.17	74.22	1,064.95	18,727.48
164	18,727.48	1,139.17	70.23	1,068.94	17,658.54
165	17,658.54	1,139.17	66.22	1,072.95	16,585.59
166	16,585.59	1,139.17	62.20	1,076.97	15,508.62
167	15,508.62	1,139.17	58.16	1,081.01	14,427.61
168	14,427.61	1,139.17	54.10	1,085.06	13,342.55
169	13,342.55	1,139.17	50.03	1,089.13	12,253.41
170	12,253.41	1,139.17	45.95	1,093.22	11,160.20
171	11,160.20	1,139.17	41.85	1,097.32	10,062.88
172	10,062.88	1,139.17	37.74	1,101.43	8,961.45
173	8,961.45	1,139.17	33.61	1,105.56	7,855.89
174	7,855.89	1,139.17	29.46	1,109.71	6,746.18
175	6,746.18	1,139.17	25.30	1,113.87	5,632.31
176	5,632.31	1,139.17	21.12	1,118.05	4,514.27
177	4,514.27	1,139.17	16.93	1,122.24	3,392.03
178	3,392.03	1,139.17	12.72	1,126.45	2,265.58
179	2,265.58	1,139.17	8.50	1,130.67	1,134.91
180	1,134.91	1,139.17	4.26	1,134.91	0.00