Question

Create a table showing the payment of a mortgage of $106,000 month by month. Create columns for:
    - Time (in years)
    - Interest (for that month)
    - Payment (always the same value)
    - Payment against principal
    - Remaining Principal

The mortgage is to last 31 years, and the nominal interest rate is 9.21%

(a)	What is the monthly payment?

The Answer Is = 863.81

(b)	What is the interest paid at the end of the 9th month?

(c)	How much of the principal is paid down by the payment made at the end of the 22nd month?

(d)	The first time that the portion of a monthly payment that is paying down the principal will be larger than the portion paying interest will occur at the end of month number________?

Answer 1

a.) Monthly Payment can be calculated in excel using PMT function. So it would be

=PMT(rate,nper,PV,FV)

monthly rate = 9.21%/12 = 0.7675%

No of periods = 31 * 12 = 372 months

Present Value (PV) = $106,000

=PMT(0.7675%,372,-106000,0) = $863.81

b.) At the end of 8th month, Remaining Principal is $105,586.95

Interest rate = 0.77%

Interest paid at the end of 9th month = 105586.95 * 0.7675% = $810.38

c.) Monthly Payment = $863.81

Interest paid at the end of 22nd month = $804.80

The Principal is paid at the end of the 22nd month = Monthly Payment - Interest paid

= $863.81 - $804.80 = $59.01

d.) At the end of month 283, Principal wil be larger than the interest portion paid.

Principal at the end of 283 month = $434.11

Interest paid at the end of 283 month = $429.70

Working--

Interest = Reamining Principal * interest rate

Payment against Principal = Monthly Payment - Interest paid

Remaining Principal = Remaining Principal - Payment against Principal

Principal = $106,000

Time	Interest	Payment	Payment against Principal	Remaining Principal
1	813.55	863.81	50.26	105949.74
2	813.16	863.81	50.65	105899.09
3	812.78	863.81	51.03	105848.06
4	812.38	863.81	51.43	105796.63
5	811.99	863.81	51.82	105744.81
6	811.59	863.81	52.22	105692.59
7	811.19	863.81	52.62	105639.97
8	810.79	863.81	53.02	105586.95
9	810.38	863.81	53.43	105533.52
10	809.97	863.81	53.84	105479.68
11	809.56	863.81	54.25	105425.43
12	809.14	863.81	54.67	105370.76
13	808.72	863.81	55.09	105315.67
14	808.30	863.81	55.51	105260.16
15	807.87	863.81	55.94	105204.22
16	807.44	863.81	56.37	105147.85
17	807.01	863.81	56.80	105091.05
18	806.57	863.81	57.24	105033.81
19	806.13	863.81	57.68	104976.14
20	805.69	863.81	58.12	104918.02
21	805.25	863.81	58.56	104859.46
22	804.80	863.81	59.01	104800.44
23	804.34	863.81	59.47	104740.98
24	803.89	863.81	59.92	104681.05
25	803.43	863.81	60.38	104620.67
26	802.96	863.81	60.85	104559.82
27	802.50	863.81	61.31	104498.51
28	802.03	863.81	61.78	104436.73
29	801.55	863.81	62.26	104374.47
30	801.07	863.81	62.74	104311.73
31	800.59	863.81	63.22	104248.51
32	800.11	863.81	63.70	104184.81
33	799.62	863.81	64.19	104120.62
34	799.13	863.81	64.68	104055.94
35	798.63	863.81	65.18	103990.76
36	798.13	863.81	65.68	103925.07
37	797.62	863.81	66.19	103858.89
38	797.12	863.81	66.69	103792.20
39	796.61	863.81	67.20	103724.99
40	796.09	863.81	67.72	103657.27
41	795.57	863.81	68.24	103589.03
42	795.05	863.81	68.76	103520.27
43	794.52	863.81	69.29	103450.97
44	793.99	863.81	69.82	103381.15
45	793.45	863.81	70.36	103310.79
46	792.91	863.81	70.90	103239.89
47	792.37	863.81	71.44	103168.45
48	791.82	863.81	71.99	103096.46
49	791.27	863.81	72.54	103023.91
50	790.71	863.81	73.10	102950.81
51	790.15	863.81	73.66	102877.15
52	789.58	863.81	74.23	102802.92
53	789.01	863.81	74.80	102728.12
54	788.44	863.81	75.37	102652.75
55	787.86	863.81	75.95	102576.80
56	787.28	863.81	76.53	102500.27
57	786.69	863.81	77.12	102423.15
58	786.10	863.81	77.71	102345.43
59	785.50	863.81	78.31	102267.12
60	784.90	863.81	78.91	102188.21
61	784.29	863.81	79.52	102108.70
62	783.68	863.81	80.13	102028.57
63	783.07	863.81	80.74	101947.83
64	782.45	863.81	81.36	101866.47
65	781.83	863.81	81.98	101784.49
66	781.20	863.81	82.61	101701.87
67	780.56	863.81	83.25	101618.63
68	779.92	863.81	83.89	101534.74
69	779.28	863.81	84.53	101450.21
70	778.63	863.81	85.18	101365.03
71	777.98	863.81	85.83	101279.19
72	777.32	863.81	86.49	101192.70
73	776.65	863.81	87.16	101105.55
74	775.99	863.81	87.82	101017.72
75	775.31	863.81	88.50	100929.22
76	774.63	863.81	89.18	100840.04
77	773.95	863.81	89.86	100750.18
78	773.26	863.81	90.55	100659.63
79	772.56	863.81	91.25	100568.38
80	771.86	863.81	91.95	100476.43
81	771.16	863.81	92.65	100383.78
82	770.45	863.81	93.36	100290.42
83	769.73	863.81	94.08	100196.34
84	769.01	863.81	94.80	100101.53
85	768.28	863.81	95.53	100006.00
86	767.55	863.81	96.26	99909.74
87	766.81	863.81	97.00	99812.73
88	766.06	863.81	97.75	99714.99
89	765.31	863.81	98.50	99616.49
90	764.56	863.81	99.25	99517.24
91	763.79	863.81	100.02	99417.22
92	763.03	863.81	100.78	99316.44
93	762.25	863.81	101.56	99214.88
94	761.47	863.81	102.34	99112.55
95	760.69	863.81	103.12	99009.43
96	759.90	863.81	103.91	98905.51
97	759.10	863.81	104.71	98800.80
98	758.30	863.81	105.51	98695.29
99	757.49	863.81	106.32	98588.96
100	756.67	863.81	107.14	98481.83
101	755.85	863.81	107.96	98373.86
102	755.02	863.81	108.79	98265.07
103	754.18	863.81	109.63	98155.45
104	753.34	863.81	110.47	98044.98
105	752.50	863.81	111.31	97933.67
106	751.64	863.81	112.17	97821.50
107	750.78	863.81	113.03	97708.47
108	749.91	863.81	113.90	97594.57
109	749.04	863.81	114.77	97479.80
110	748.16	863.81	115.65	97364.14
111	747.27	863.81	116.54	97247.60
112	746.38	863.81	117.43	97130.17
113	745.47	863.81	118.34	97011.83
114	744.57	863.81	119.24	96892.59
115	743.65	863.81	120.16	96772.43
116	742.73	863.81	121.08	96651.35
117	741.80	863.81	122.01	96529.34
118	740.86	863.81	122.95	96406.39
119	739.92	863.81	123.89	96282.50
120	738.97	863.81	124.84	96157.66
121	738.01	863.81	125.80	96031.86
122	737.04	863.81	126.77	95905.09
123	736.07	863.81	127.74	95777.35
124	735.09	863.81	128.72	95648.63
125	734.10	863.81	129.71	95518.93
126	733.11	863.81	130.70	95388.23
127	732.10	863.81	131.71	95256.52
128	731.09	863.81	132.72	95123.80
129	730.08	863.81	133.73	94990.07
130	729.05	863.81	134.76	94855.31
131	728.01	863.81	135.80	94719.51
132	726.97	863.81	136.84	94582.67
133	725.92	863.81	137.89	94444.79
134	724.86	863.81	138.95	94305.84
135	723.80	863.81	140.01	94165.83
136	722.72	863.81	141.09	94024.74
137	721.64	863.81	142.17	93882.57
138	720.55	863.81	143.26	93739.31
139	719.45	863.81	144.36	93594.95
140	718.34	863.81	145.47	93449.48
141	717.22	863.81	146.59	93302.89
142	716.10	863.81	147.71	93155.18
143	714.97	863.81	148.84	93006.34
144	713.82	863.81	149.99	92856.35
145	712.67	863.81	151.14	92705.22
146	711.51	863.81	152.30	92552.92
147	710.34	863.81	153.47	92399.45
148	709.17	863.81	154.64	92244.81
149	707.98	863.81	155.83	92088.98
150	706.78	863.81	157.03	91931.95
151	705.58	863.81	158.23	91773.72
152	704.36	863.81	159.45	91614.27
153	703.14	863.81	160.67	91453.60
154	701.91	863.81	161.90	91291.70
155	700.66	863.81	163.15	91128.55
156	699.41	863.81	164.40	90964.15
157	698.15	863.81	165.66	90798.49
158	696.88	863.81	166.93	90631.56
159	695.60	863.81	168.21	90463.35
160	694.31	863.81	169.50	90293.84
161	693.01	863.81	170.80	90123.04
162	691.69	863.81	172.12	89950.92
163	690.37	863.81	173.44	89777.49
164	689.04	863.81	174.77	89602.72
165	687.70	863.81	176.11	89426.61
166	686.35	863.81	177.46	89249.15
167	684.99	863.81	178.82	89070.33
168	683.61	863.81	180.20	88890.13
169	682.23	863.81	181.58	88708.55
170	680.84	863.81	182.97	88525.58
171	679.43	863.81	184.38	88341.21
172	678.02	863.81	185.79	88155.41
173	676.59	863.81	187.22	87968.20
174	675.16	863.81	188.65	87779.54
175	673.71	863.81	190.10	87589.44
176	672.25	863.81	191.56	87397.88
177	670.78	863.81	193.03	87204.85
178	669.30	863.81	194.51	87010.34
179	667.80	863.81	196.01	86814.33
180	666.30	863.81	197.51	86616.82
181	664.78	863.81	199.03	86417.79
182	663.26	863.81	200.55	86217.24
183	661.72	863.81	202.09	86015.15
184	660.17	863.81	203.64	85811.50
185	658.60	863.81	205.21	85606.30
186	657.03	863.81	206.78	85399.52
187	655.44	863.81	208.37	85191.15
188	653.84	863.81	209.97	84981.18
189	652.23	863.81	211.58	84769.60
190	650.61	863.81	213.20	84556.40
191	648.97	863.81	214.84	84341.56
192	647.32	863.81	216.49	84125.07
193	645.66	863.81	218.15	83906.92
194	643.99	863.81	219.82	83687.09
195	642.30	863.81	221.51	83465.58
196	640.60	863.81	223.21	83242.37
197	638.89	863.81	224.92	83017.45
198	637.16	863.81	226.65	82790.79
199	635.42	863.81	228.39	82562.40
200	633.67	863.81	230.14	82332.26
201	631.90	863.81	231.91	82100.35
202	630.12	863.81	233.69	81866.66
203	628.33	863.81	235.48	81631.18
204	626.52	863.81	237.29	81393.89
205	624.70	863.81	239.11	81154.77
206	622.86	863.81	240.95	80913.83
207	621.01	863.81	242.80	80671.03
208	619.15	863.81	244.66	80426.37
209	617.27	863.81	246.54	80179.83
210	615.38	863.81	248.43	79931.40
211	613.47	863.81	250.34	79681.07
212	611.55	863.81	252.26	79428.81
213	609.62	863.81	254.19	79174.62
214	607.67	863.81	256.14	78918.47
215	605.70	863.81	258.11	78660.36
216	603.72	863.81	260.09	78400.27
217	601.72	863.81	262.09	78138.18
218	599.71	863.81	264.10	77874.08
219	597.68	863.81	266.13	77607.95
220	595.64	863.81	268.17	77339.79
221	593.58	863.81	270.23	77069.56
222	591.51	863.81	272.30	76797.26
223	589.42	863.81	274.39	76522.87
224	587.31	863.81	276.50	76246.37
225	585.19	863.81	278.62	75967.75
226	583.05	863.81	280.76	75686.99
227	580.90	863.81	282.91	75404.08
228	578.73	863.81	285.08	75119.00
229	576.54	863.81	287.27	74831.73
230	574.33	863.81	289.48	74542.25
231	572.11	863.81	291.70	74250.55
232	569.87	863.81	293.94	73956.61
233	567.62	863.81	296.19	73660.42
234	565.34	863.81	298.47	73361.95
235	563.05	863.81	300.76	73061.20
236	560.74	863.81	303.07	72758.13
237	558.42	863.81	305.39	72452.74
238	556.07	863.81	307.74	72145.01
239	553.71	863.81	310.10	71834.91
240	551.33	863.81	312.48	71522.43
241	548.93	863.81	314.88	71207.56
242	546.52	863.81	317.29	70890.26
243	544.08	863.81	319.73	70570.54
244	541.63	863.81	322.18	70248.36
245	539.16	863.81	324.65	69923.70
246	536.66	863.81	327.15	69596.56
247	534.15	863.81	329.66	69266.90
248	531.62	863.81	332.19	68934.71
249	529.07	863.81	334.74	68599.98
250	526.50	863.81	337.31	68262.67
251	523.92	863.81	339.89	67922.78
252	521.31	863.81	342.50	67580.28
253	518.68	863.81	345.13	67235.14
254	516.03	863.81	347.78	66887.36
255	513.36	863.81	350.45	66536.91
256	510.67	863.81	353.14	66183.77
257	507.96	863.81	355.85	65827.93
258	505.23	863.81	358.58	65469.34
259	502.48	863.81	361.33	65108.01
260	499.70	863.81	364.11	64743.91
261	496.91	863.81	366.90	64377.01
262	494.09	863.81	369.72	64007.29
263	491.26	863.81	372.55	63634.73
264	488.40	863.81	375.41	63259.32
265	485.52	863.81	378.29	62881.03
266	482.61	863.81	381.20	62499.83
267	479.69	863.81	384.12	62115.70
268	476.74	863.81	387.07	61728.63
269	473.77	863.81	390.04	61338.59
270	470.77	863.81	393.04	60945.55
271	467.76	863.81	396.05	60549.50
272	464.72	863.81	399.09	60150.41
273	461.65	863.81	402.16	59748.25
274	458.57	863.81	405.24	59343.01
275	455.46	863.81	408.35	58934.66
276	452.32	863.81	411.49	58523.17
277	449.17	863.81	414.64	58108.53
278	445.98	863.81	417.83	57690.70
279	442.78	863.81	421.03	57269.67
280	439.54	863.81	424.27	56845.40
281	436.29	863.81	427.52	56417.88
282	433.01	863.81	430.80	55987.08
283	429.70	863.81	434.11	55552.97