Project 9: Mortgages, Loans and Amortization Templates
Enter your name in the box to the right
Name:
Finding Monthly Payments and Interest Totals
Mortgage
Annual
or Loan
Mortgage or Loan Interest
Term
Monthly
Amount
Rate
(in years)
Payment
$108,000.00
3.25%
30
$470.02
$15,265.00
7.50%
5
$305.88
$25,500.00
5.00%
10
$270.47
$30,000.00
4.00%
10
$303.74
$178,765.00
Totals
$1,350.10
Finding Loan Amounts for a Fixed Payment
Mortgage
Annual
or Loan
Mortgage or Loan Interest
Term
Monthly
Amount
Rate
(in years)
Payment
$17,995.54
6.25%
5
$350.00
Total of all
Payments
Total Interest Paid
$169,208.22
$61,208.22
$18,352.76
$3,087.76
$32,456.05
$6,956.05
$36,448.25
$6,448.25
$256,465.27
$77,700.27
Total of all
Payments
Total Interest Paid
$21,000.00
$3,004.46
Project 9: Mortgages, Loans and Amortization Templates
Enter your name in the box to the right
Name:
Mortgage
Annual
or Loan
Mortgage or Loan Interest
Term
Amount
Rate
(in years)
$120,000.00
4.00%
30
Monthly
Payment
$572.90
Total of all
Payments
Total Interest Paid
$206,243.41
$86,243.41
Amortization Schedule
Scroll Down to
see the
Complete
Amortization
Schedule
Month
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
Payment
Amount
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
Portion of
Payment
applied to
Interest
$400.00
$399.42
$398.85
$398.27
$397.68
$397.10
$396.51
$395.93
$395.34
$394.74
$394.15
$393.55
$392.96
$392.36
$391.75
$391.15
$390.54
$389.94
$389.33
$388.71
$388.10
$387.49
$386.87
$386.25
$385.62
$385.00
$384.37
Portion of
Payment applied
to Principal
$172.90
$173.47
$174.05
$174.63
$175.22
$175.80
$176.39
$176.97
$177.56
$178.16
$178.75
$179.34
$179.94
$180.54
$181.14
$181.75
$182.35
$182.96
$183.57
$184.18
$184.80
$185.41
$186.03
$186.65
$187.27
$187.90
$188.52
Outstanding
Balance
$120,000.00
$119,827.10
$119,653.63
$119,479.57
$119,304.94
$119,129.73
$118,953.93
$118,777.54
$118,600.57
$118,423.00
$118,244.85
$118,066.10
$117,886.76
$117,706.81
$117,526.27
$117,345.13
$117,163.38
$116,981.03
$116,798.06
$116,614.49
$116,430.31
$116,245.51
$116,060.10
$115,874.07
$115,687.42
$115,500.14
$115,312.24
$115,123.72
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$383.75
$383.12
$382.48
$381.85
$381.21
$380.57
$379.93
$379.29
$378.64
$377.99
$377.35
$376.69
$376.04
$375.38
$374.72
$374.06
$373.40
$372.74
$372.07
$371.40
$370.73
$370.05
$369.38
$368.70
$368.02
$367.34
$366.65
$365.96
$365.27
$364.58
$363.89
$363.19
$362.49
$361.79
$361.09
$360.38
$359.67
$358.96
$358.25
$357.53
$356.81
$356.09
$355.37
$354.65
$353.92
$353.19
$352.46
$351.72
$189.15
$189.78
$190.42
$191.05
$191.69
$192.33
$192.97
$193.61
$194.26
$194.90
$195.55
$196.20
$196.86
$197.52
$198.17
$198.83
$199.50
$200.16
$200.83
$201.50
$202.17
$202.84
$203.52
$204.20
$204.88
$205.56
$206.25
$206.93
$207.62
$208.32
$209.01
$209.71
$210.41
$211.11
$211.81
$212.52
$213.23
$213.94
$214.65
$215.37
$216.08
$216.80
$217.53
$218.25
$218.98
$219.71
$220.44
$221.18
$114,934.57
$114,744.78
$114,554.37
$114,363.32
$114,171.63
$113,979.30
$113,786.34
$113,592.73
$113,398.47
$113,203.57
$113,008.01
$112,811.81
$112,614.95
$112,417.43
$112,219.26
$112,020.43
$111,820.93
$111,620.77
$111,419.94
$111,218.44
$111,016.27
$110,813.43
$110,609.91
$110,405.71
$110,200.83
$109,995.27
$109,789.02
$109,582.08
$109,374.46
$109,166.14
$108,957.13
$108,747.42
$108,537.02
$108,325.91
$108,114.10
$107,901.58
$107,688.35
$107,474.41
$107,259.76
$107,044.40
$106,828.31
$106,611.51
$106,393.98
$106,175.73
$105,956.75
$105,737.04
$105,516.60
$105,295.43
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$350.98
$350.25
$349.50
$348.76
$348.01
$347.26
$346.51
$345.75
$345.00
$344.24
$343.48
$342.71
$341.94
$341.17
$340.40
$339.63
$338.85
$338.07
$337.29
$336.50
$335.71
$334.92
$334.13
$333.33
$332.53
$331.73
$330.93
$330.12
$329.31
$328.50
$327.69
$326.87
$326.05
$325.23
$324.40
$323.57
$322.74
$321.91
$321.07
$320.23
$319.39
$318.54
$317.70
$316.85
$315.99
$315.14
$314.28
$313.41
$221.91
$222.65
$223.40
$224.14
$224.89
$225.64
$226.39
$227.14
$227.90
$228.66
$229.42
$230.19
$230.95
$231.72
$232.50
$233.27
$234.05
$234.83
$235.61
$236.40
$237.19
$237.98
$238.77
$239.57
$240.36
$241.17
$241.97
$242.78
$243.59
$244.40
$245.21
$246.03
$246.85
$247.67
$248.50
$249.33
$250.16
$250.99
$251.83
$252.67
$253.51
$254.35
$255.20
$256.05
$256.91
$257.76
$258.62
$259.48
$105,073.51
$104,850.86
$104,627.46
$104,403.32
$104,178.44
$103,952.80
$103,726.41
$103,499.27
$103,271.37
$103,042.70
$102,813.28
$102,583.09
$102,352.14
$102,120.42
$101,887.92
$101,654.65
$101,420.60
$101,185.77
$100,950.15
$100,713.76
$100,476.57
$100,238.59
$99,999.82
$99,760.26
$99,519.90
$99,278.73
$99,036.76
$98,793.98
$98,550.40
$98,306.00
$98,060.79
$97,814.76
$97,567.91
$97,320.24
$97,071.74
$96,822.42
$96,572.26
$96,321.27
$96,069.44
$95,816.78
$95,563.27
$95,308.91
$95,053.71
$94,797.66
$94,540.75
$94,282.99
$94,024.37
$93,764.88
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$312.55
$311.68
$310.81
$309.94
$309.06
$308.18
$307.30
$306.41
$305.53
$304.63
$303.74
$302.84
$301.94
$301.04
$300.13
$299.22
$298.31
$297.40
$296.48
$295.56
$294.63
$293.70
$292.77
$291.84
$290.90
$289.96
$289.02
$288.07
$287.12
$286.17
$285.22
$284.26
$283.30
$282.33
$281.36
$280.39
$279.41
$278.44
$277.45
$276.47
$275.48
$274.49
$273.50
$272.50
$271.50
$270.49
$269.48
$268.47
$260.35
$261.22
$262.09
$262.96
$263.84
$264.72
$265.60
$266.48
$267.37
$268.26
$269.16
$270.06
$270.96
$271.86
$272.77
$273.67
$274.59
$275.50
$276.42
$277.34
$278.27
$279.19
$280.12
$281.06
$281.99
$282.93
$283.88
$284.82
$285.77
$286.73
$287.68
$288.64
$289.60
$290.57
$291.54
$292.51
$293.48
$294.46
$295.44
$296.43
$297.42
$298.41
$299.40
$300.40
$301.40
$302.41
$303.41
$304.43
$93,504.53
$93,243.32
$92,981.23
$92,718.27
$92,454.43
$92,189.72
$91,924.12
$91,657.63
$91,390.26
$91,121.99
$90,852.84
$90,582.78
$90,311.82
$90,039.97
$89,767.20
$89,493.53
$89,218.94
$88,943.44
$88,667.02
$88,389.68
$88,111.41
$87,832.22
$87,552.09
$87,271.03
$86,989.04
$86,706.10
$86,422.23
$86,137.40
$85,851.63
$85,564.90
$85,277.22
$84,988.58
$84,698.98
$84,408.41
$84,116.87
$83,824.36
$83,530.88
$83,236.42
$82,940.97
$82,644.54
$82,347.13
$82,048.72
$81,749.32
$81,448.92
$81,147.51
$80,845.11
$80,541.69
$80,237.27
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$267.46
$266.44
$265.42
$264.39
$263.36
$262.33
$261.30
$260.26
$259.22
$258.17
$257.12
$256.07
$255.01
$253.95
$252.89
$251.82
$250.75
$249.68
$248.60
$247.52
$246.44
$245.35
$244.26
$243.16
$242.06
$240.96
$239.85
$238.74
$237.63
$236.51
$235.39
$234.27
$233.14
$232.00
$230.87
$229.73
$228.58
$227.44
$226.28
$225.13
$223.97
$222.81
$221.64
$220.47
$219.29
$218.12
$216.93
$215.75
$305.44
$306.46
$307.48
$308.51
$309.53
$310.57
$311.60
$312.64
$313.68
$314.73
$315.78
$316.83
$317.88
$318.94
$320.01
$321.07
$322.14
$323.22
$324.30
$325.38
$326.46
$327.55
$328.64
$329.74
$330.84
$331.94
$333.05
$334.16
$335.27
$336.39
$337.51
$338.63
$339.76
$340.89
$342.03
$343.17
$344.31
$345.46
$346.61
$347.77
$348.93
$350.09
$351.26
$352.43
$353.60
$354.78
$355.97
$357.15
$79,931.83
$79,625.37
$79,317.89
$79,009.38
$78,699.85
$78,389.28
$78,077.68
$77,765.04
$77,451.36
$77,136.63
$76,820.86
$76,504.03
$76,186.14
$75,867.20
$75,547.19
$75,226.12
$74,903.97
$74,580.75
$74,256.46
$73,931.08
$73,604.62
$73,277.07
$72,948.43
$72,618.69
$72,287.85
$71,955.92
$71,622.87
$71,288.71
$70,953.45
$70,617.06
$70,279.55
$69,940.92
$69,601.16
$69,260.26
$68,918.23
$68,575.06
$68,230.74
$67,885.28
$67,538.67
$67,190.90
$66,841.97
$66,491.88
$66,140.62
$65,788.19
$65,434.58
$65,079.80
$64,723.84
$64,366.68
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$214.56
$213.36
$212.16
$210.96
$209.75
$208.54
$207.33
$206.11
$204.89
$203.66
$202.43
$201.20
$199.96
$198.71
$197.47
$196.21
$194.96
$193.70
$192.43
$191.17
$189.89
$188.62
$187.34
$186.05
$184.76
$183.47
$182.17
$180.87
$179.56
$178.25
$176.93
$175.61
$174.29
$172.96
$171.63
$170.29
$168.95
$167.60
$166.25
$164.90
$163.54
$162.17
$160.80
$159.43
$158.05
$156.67
$155.28
$153.89
$358.34
$359.54
$360.74
$361.94
$363.14
$364.36
$365.57
$366.79
$368.01
$369.24
$370.47
$371.70
$372.94
$374.19
$375.43
$376.68
$377.94
$379.20
$380.46
$381.73
$383.00
$384.28
$385.56
$386.85
$388.14
$389.43
$390.73
$392.03
$393.34
$394.65
$395.96
$397.28
$398.61
$399.94
$401.27
$402.61
$403.95
$405.30
$406.65
$408.00
$409.36
$410.73
$412.10
$413.47
$414.85
$416.23
$417.62
$419.01
$64,008.34
$63,648.80
$63,288.07
$62,926.13
$62,562.99
$62,198.63
$61,833.06
$61,466.27
$61,098.26
$60,729.02
$60,358.56
$59,986.85
$59,613.91
$59,239.73
$58,864.29
$58,487.61
$58,109.67
$57,730.47
$57,350.01
$56,968.27
$56,585.27
$56,200.99
$55,815.43
$55,428.58
$55,040.44
$54,651.01
$54,260.29
$53,868.26
$53,474.92
$53,080.27
$52,684.31
$52,287.02
$51,888.41
$51,488.48
$51,087.21
$50,684.60
$50,280.65
$49,875.35
$49,468.71
$49,060.70
$48,651.34
$48,240.61
$47,828.52
$47,415.05
$47,000.20
$46,583.97
$46,166.35
$45,747.34
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$152.49
$151.09
$149.68
$148.27
$146.86
$145.44
$144.01
$142.58
$141.15
$139.71
$138.27
$136.82
$135.36
$133.90
$132.44
$130.97
$129.50
$128.02
$126.54
$125.05
$123.56
$122.06
$120.56
$119.05
$117.54
$116.02
$114.50
$112.97
$111.44
$109.90
$108.35
$106.81
$105.25
$103.69
$102.13
$100.56
$98.99
$97.41
$95.82
$94.23
$92.63
$91.03
$89.43
$87.82
$86.20
$84.58
$82.95
$81.32
$420.41
$421.81
$423.21
$424.63
$426.04
$427.46
$428.89
$430.32
$431.75
$433.19
$434.63
$436.08
$437.54
$438.99
$440.46
$441.93
$443.40
$444.88
$446.36
$447.85
$449.34
$450.84
$452.34
$453.85
$455.36
$456.88
$458.40
$459.93
$461.46
$463.00
$464.54
$466.09
$467.65
$469.21
$470.77
$472.34
$473.91
$475.49
$477.08
$478.67
$480.26
$481.86
$483.47
$485.08
$486.70
$488.32
$489.95
$491.58
$45,326.93
$44,905.12
$44,481.91
$44,057.28
$43,631.24
$43,203.78
$42,774.90
$42,344.58
$41,912.83
$41,479.64
$41,045.01
$40,608.93
$40,171.39
$39,732.40
$39,291.94
$38,850.02
$38,406.62
$37,961.74
$37,515.38
$37,067.53
$36,618.19
$36,167.36
$35,715.02
$35,261.17
$34,805.81
$34,348.93
$33,890.53
$33,430.60
$32,969.13
$32,506.13
$32,041.59
$31,575.49
$31,107.85
$30,638.64
$30,167.87
$29,695.53
$29,221.62
$28,746.13
$28,269.05
$27,790.38
$27,310.12
$26,828.25
$26,344.78
$25,859.70
$25,373.00
$24,884.68
$24,394.73
$23,903.15
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$572.90
$79.68
$78.03
$76.38
$74.73
$73.07
$71.40
$69.73
$68.05
$66.37
$64.68
$62.99
$61.29
$59.58
$57.87
$56.15
$54.43
$52.70
$50.97
$49.23
$47.48
$45.73
$43.98
$42.21
$40.44
$38.67
$36.89
$35.10
$33.31
$31.51
$29.71
$27.90
$26.08
$24.26
$22.43
$20.59
$18.75
$16.90
$15.05
$13.19
$11.33
$9.45
$7.58
$5.69
$3.80
$1.90
$493.22
$494.87
$496.51
$498.17
$499.83
$501.50
$503.17
$504.85
$506.53
$508.22
$509.91
$511.61
$513.32
$515.03
$516.74
$518.47
$520.19
$521.93
$523.67
$525.41
$527.16
$528.92
$530.69
$532.45
$534.23
$536.01
$537.80
$539.59
$541.39
$543.19
$545.00
$546.82
$548.64
$550.47
$552.31
$554.15
$555.99
$557.85
$559.71
$561.57
$563.44
$565.32
$567.21
$569.10
$571.00
$23,409.93
$22,915.06
$22,418.55
$21,920.38
$21,420.54
$20,919.05
$20,415.88
$19,911.03
$19,404.51
$18,896.29
$18,386.38
$17,874.77
$17,361.45
$16,846.43
$16,329.68
$15,811.22
$15,291.02
$14,769.09
$14,245.43
$13,720.01
$13,192.85
$12,663.92
$12,133.24
$11,600.79
$11,066.56
$10,530.55
$9,992.75
$9,453.16
$8,911.77
$8,368.58
$7,823.58
$7,276.76
$6,728.12
$6,177.64
$5,625.34
$5,071.19
$4,515.20
$3,957.35
$3,397.64
$2,836.07
$2,272.62
$1,707.30
$1,140.09
$571.00
$0.00
PROJECT 3
MORTGAGES, LOANS AND AMORTIZATION
Project 9: Mortgages, Loans and Amortization Templates
Sample Computer Screens
Enter your name in the box to the right
Name:
Finding Monthly Payements and Interest Totals
Mortgage
Annual
or Loan
Mortgage or Loan Interest
Term
Monthly
Amount
Rate
(in years)
Payment
$108,000.00
3.25%
30
$470.02
$15,265.00
7.50%
5
$305.88
$25,500.00
5.00%
10
$270.47
$30,000.00
4.00%
10
$303.74
$178,765.00
Totals
Total of all
Payments
Total Interest Paid
$169,208.22
$61,208.22
$18,352.76
$3,087.76
$32,456.05
$6,956.05
$36,448.25
$6,448.25
$1,350.10
$256,465.27
Finding Loan Amounts for a Fixed Payement
Mortgage
Annual
or Loan
Mortgage or Loan Interest
Term
Monthly
Amount
Rate
(in years)
Payment
$17,995.54
6.25%
5
$350.00
$77,700.27
Total of all
PaymentsTemplates
Total Interest Paid
Project 9: Mortgages, Loans and Amortization
Enter your name in the box to the right
Mortgage or Loan
Amount
$120,000.00
$21,000.00
$3,004.46
Name:
Mortgage
Annual
or Loan
Interest
Term
Rate
(in years)
4.00%
30
Monthly
Payment
$572.90
Total of all
Payments
Total Interest Paid
$206,243.41
$86,243.41
Amortization Schedule
Scroll Down to
see the Complete
Amortization
Schedule
Month
0
1
2
3
4
Payment
Amount
$572.90
$572.90
$572.90
$572.90
Portion of
Payment
applied to
Interest
$400.00
$399.42
$398.85
$398.27
Portion of
Payment applied
to Principal
$172.90
$173.47
$174.05
$174.63
Outstanding
Balance
$120,000.00
$119,827.10
$119,653.63
$119,479.57
$119,304.94
DIRECTIONS:
1. Please read all directions. Study and analyze the computer screen before you start answering the
questions. You will find information on the screen necessary to answer most questions.
2.
Project 3 contains 2 spreadsheets: the first spreadsheet is titled Payments and Interest, the second
is titled Amortization Schedule. Use the file tabs in the lower left corner of the spreadsheet to
switch between spreadsheets.
3.
Only values in Blue can be changed.
4.
When you enter numeric data, do not include the $ or the , in the number. For example, to enter
$1,765.56 you should enter 1765.56. To enter a percentage you must enter the value in decimal
form. To enter an interest rate of 5% you must type .05.
5.
Reset the original values for Project 3 from the sample screens above.
6.
To print a copy of the spreadsheet select the print icon from the top tool bar. The Amortization
spreadsheet will not fit on 1 page, this spreadsheet is 8 pages long. It’s better to just submit the entire
completed file to Blackboard.
MATHEMATICS USED IN PROJECT 3
The mathematics of Project 3 is a combination of the present value annuity formulas and the simple
interest mathematics. That is, to calculate the interest paid for any month, the simple interest formula,
I=PRT is used. The principal is the outstanding balance, the rate is the stated annual rate and the time is
1/12 of a year (1 month). The portion paid to reduce the principal balance each month is the amount of the
monthly payment minus the amount of simple interest. The monthly payment is calculated from the same
formula that was used in Project 7. However, this time the calculation
R
will be called i because it is
12
the periodic interest rate.
monthly pmt.=
P i
1 − (i + 1)−12t
Where R = annual Rate, P = Principal, i =
For example, let P = $9,000.00, R = 10% (thus i =
R
, and t = time in years.
12
.1
=.00833333 ), t = 3 years (36 months). Thus,
12
for a $9,000 loan at 10% compounded monthly for 3 years the monthly payment is $290.40. We can
calculate the total amount paid by multiplying the payment amount by the number of payments to be
made. Total paid = $290.40 12 3 = $10,454.40 . Then we can determine the total interest paid by
subtracting the original loan amount from the total amount of all payments.
Total interest = $10,454.40 − $9,000.00 = $1,454.40
The computer makes an amortization schedule using the argument that follows: For the first month the
interest is 9000 .1
1
= 75 ( from I = PRT). The amount of the payment that is applied to the principal
12
then is found by subtracting the monthly interest from the payment, $290.40 − $75.00 = $215.40 . The
outstanding principal balance after the payment is made is found by subtracting the payment’s principal
amount from the principal balance, $9,000.00 − $215.40 = $8,784.60 .
At this point the calculations for month 2 can be carried out; interest = $8748.60.1
1
= $73.21 ,
12
Principal = $290.40 − 73.21 = $217.19 , and the outstanding principal balance is now
$8,784.60 − $217.19 = $8,567.41. And so the calculations go on month after month until the
outstanding balance has been reduced to zero. It is interesting to note that each month the interest portion
of the payment is lower because the outstanding principal balance has been reduced and so the principal
portion of the payment is greater.
PROJECT 3 QUESTIONS
If you have not already done so, reset the information using the sample screens at the beginning
of this project.
1.
If you borrow $10,000 at 10.5% for four years, what will be the monthly payment, the total interest paid by you
and the total paid back by you?
2.
Which loan will cost more, a $5,000 loan at 12% for 3 years or a $4,700 loan at 12% for 4 years? The cost of a
loan is the total interest paid.
3.
If you buy a house with a mortgage of $100,000 at 8.9% for 30 years, how much will your total payments be
over the 30 year period? How much of the total payments will be interest?
4.
For a loan of $25,000 at 11% for 10 years, use the amortization spreadsheet to determine in which month of the
loan will the portion of the monthly payment that goes toward principal be greater than the portion that pays
interest.
5.
If you need to buy a car and you can only afford a payment of $175.00 per month, approximately how much
could you borrow if the time on the loan is 4 years and the rate is 9%?
6.
Find the monthly payment and the total interest paid on the following loans:
PRINCIPAL
RATE
TIME(in years)
a. $25,000
10.5%
7
b. $31,000
10%
10
c. $12,000
9.8%
4
d.
$5,000
11%
2
7.
For a $20,000 loan at 12%, find a time in months that will yield a monthly payment of approximately $300.
8.
What is the difference in total interest paid on loans of $15,000 at 11% for 4 years and $15,000 at 11% for 5
years? What is the difference in the monthly payment for these loans?
9.
What is the difference in total interest paid on loans of $70,000 at 12% for 30 years and $70,000 at 12% for 25
years? What is the difference in the monthly payment for these loans?
10. Using the amortization schedule spreadsheet on a loan of $75,000 at 10% for 30 years and your calculator, find
the total interest paid in the first 5 years, then in the second 5 years and in the third and finally in the fourth 5
years.
11. Using the results from Question 10, why is the interest paid in the first five years greater than any other group of
five years?
12. If you want to buy a home with a price of $180,000 and the current interest rate is 5% for 30 years; how much
money can be saved over the life of the loan by making a down payment of 20% of the price of the house over a
10% down payment? Note the down payment will reduce the amount of the mortgage.
13. You have just graduated from college and found a job in your field. You bought a house and have a 30 year
mortgage of $103,500 at 6%, you have student loans totaling $26,000 at 4.5% for 10 years, a car loan of
$18,350 at 7.5% for 5 years and a personal credit loan of $5,000 at 9% for 2 years. What is your total
indebtedness, what is the total of your monthly loan payments and how much interest will you have paid when
all of these loans are retired?
PROJECT 3 ANSWERS
NAME
1. Monthly Payment
2. Circle the correct answer:
CLASS
HOUR
Total Interest
Total Paid
$5,000
3. Total Cost
$4,700
Interest
4. Month
5. Amount Borrowed
6. Payment
Interest
a)
b)
c)
d)
a)
b)
c)
d)
7. Time
8. Interest Difference
Payment Difference
9. Interest Difference
Payment Difference
10. Total interest, 1st 5 years
Total interest, 2nd 5 years
Total interest, 3rd 5 years
Total interest, 4th 5 years
11.
12. Savings
13. Total Indebtedness
Total Interest
Total Payment
Purchase answer to see full
attachment
