# CCCHSTC Loan Amortization Loan Terms Excel Spreadsheet

Description

3 attachmentsSlide 1 of 3attachment_1attachment_1attachment_2attachment_2attachment_3attachment_3.slider-slide > img { width: 100%; display: block; }
.slider-slide > img:focus { margin: auto; }

Unformatted Attachment Preview

Name(s):_______________________________
Loan Terms
Borrowed
Rate
Term
Annual Loan Payment
1 Calculate the total amount paid
(principal and interest) using the loan
payment and number of payments
2
Calculate the interest payment for
period 3 using the excel IPMT function
3
Calculate the principal payment for
period 4 using the excel PPMT function
(annual)
(years)
(principal and interest)
Pmt Num
0
1
2
3
4
5
6
7
8
9
Total
Loan Amortization Table
Interest
Payment
Loan Amortization Table
Principal
Payment
Loan
Balance
Name(s):_______________________________
Loan Terms
Borrowed
Rate
Term
Annual Loan Payment
a.
\$ 100,000.00
7.5%
(annual)
9
(years)
\$15,676.72 (principal and interest)
Calculate the total amount paid
(principal and interest) using the loan
payment and number of payments
\$141,090.44
b.
Calculate the interest payment for
\$6,227.50
c.
Calculate the principal payment for
\$10,157.91
Pmt Num
0
1
2
3
4
5
6
7
8
9
Total
Loan Amortization Table
Interest
Payment
\$ 7,500.00
\$ 6,886.75
\$ 6,227.50
\$ 5,518.81
\$ 4,756.96
\$ 3,937.98
\$ 3,057.58
\$ 2,111.14
\$ 1,093.72
\$ 41,090.44
Loan Amortization Table
Principal
Payment
\$
8,176.72
\$
8,789.97
\$
9,449.22
\$ 10,157.91
\$ 10,919.75
\$ 11,738.73
\$ 12,619.14
\$ 13,565.57
\$ 14,582.99
\$ 100,000.00
\$
\$
\$
\$
\$
\$
\$
\$
\$
\$
Loan
Balance
100,000.00
91,823.28
83,033.31
73,584.10
63,426.19
52,506.44
40,767.70
28,148.57
14,582.99

Total amounts paid equals to Annual payments * number of years = C7 * C6
Alternatively, we can say that Total Payments equals the Total Interest payments +
Total Pricinciple payments = G16+ H16
Both of these methods yield the same answer.
Loan Terms
Borrowed
Monthly Interest Rate
Term in months
Monthly Loan Payment
a)
What is the monthly payment? (should be the same as
cell C8)
b)
How much total interest will you pay when paid in full
according to the loan terms?
c)
How much prinicpal will you pay when paid in full?
d)
How much will the house cost in total (principal +
interest) when paid in full?
e)
Calculate the interest payment for period 12 using the
excel IPMT function
f)
Calculate the principal payment for period 12 using the
excel PPMT function
g)
How much money would you save in interest if the
interest rate was 3.25%, instead of 5.05%?
(price of home minus downpayment)
(monthly)
(months)
(principal and interest)
Tota
Pmt Num
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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Loan Amortization Table
Interest
Principal
Payment
Payment
Loan
Balance
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
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
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
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
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
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
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
Total
Activity 3
36 points
This assignment corresponds to developing loan amortization tables. There are two computer exercises
listed below. During the Discussion Section, you learn how to do Exercise 1.
Work on Exercise 2 and upload your finished work (i.e., one Excel file) on:
Blackboard => Assignments and Assessments => Activity 3-Take Home
Use the template Activity 3-Workbook in this exercise. There are two spreadsheets in the EXCEL
workbook, one for each exercise. Only spreadsheet Exercise 2 will be graded. If you have any
questions, use TAs office hours.
Blackboard by the due date.
Exercise 1:
The example exercise is to work through a loan amortization example using Excel. Open Activity 3Workbook. Go to the Exercise 1 worksheet.
The example loan conditions are (enter these values under Loan Terms):
Loan amount borrowed (principal or pv) \$100,000
Loan interest (rate) is 7.5%
Loan term (number of payments or nper) is 9 years
Annual payments of principal and interest
1) Calculate the annual loan payment in cell C7 using the PMT function in Excel. The PMT
function is in the formulas under the Financial menu option. In the PMT Menu box, the Rate is
the interest rate, Nper is the number of payments or term, and PV is the principal amount
borrowed (enter this as a negative value). FV and Type should be blank or you can enter 0. Use
your mouse and use the cell reference to enter the required entries.
2) Write the needed formulas in the Loan Amortization Table given to calculate the interest payment
and the principal payment for each period payment.
1st, Interest Payment: Calculate the interest payment as follows: Interest payment = period interest
rate * the outstanding loan balance. Start from Pmt Num 1 and use the loan balance of the
previous period. You need to use absolute and relative cell addresses to accomplish this task!
2nd, Principle Payment: When you make payments on a loan, part of your payment goes for
interest on the loan and part goes to pay back the loan (principle). Subtract the Interest Payment
from the Annual Loan payment (i.e., principal and interest that you calculated using PMT) to
calculate the amount paid on principal.
3rd, Loan Balance: Subtract the principal payment from the previous period outstanding balance.
In each period, the loan balance is whatever loan balance was left from the previous payment
minus principle payment. (Note: Loan Balance in period 0 is the amount borrowed).
1
4th, copy and paste the formulas for the remaining 8 payments.
5th, enter formulas to sum the totals of Interest Payments and Principle Payments in your table.
a) Calculate the total amount paid (Principal + Interest) using values in Term (cell C6) and Loan
Payment (cell C7).
b) Use the Excel IPMT formula to calculate the interest payment for payment 3 in D19. Again,
enter PV as a negative value.
c) Use the Excl PPMT formula to calculate the principal payment for payment 4 in D17.
d) Check to see if the results of a, b and c are the same as calculated by your Loan Amortization
Table.
2
Exercise 2: Home Mortgage (36 points)
Consider you just graduated college and want to purchase a house. Your dream home is a 4
bedroom, 2 bath, with 2,000 square feet house and is listed at \$315,000.
Assume you have enough money to put a 5% down payment (\$315,000 * 5% = \$15,750). You will
qualify for a 30-year fixed mortgage of 5.05% APR. You will make monthly payments.
Fill in the Loan Terms table and make a loan amortization table for financing the house in the
Exercise 2 worksheet.
When calculating the monthly payment, the periodic interest rate and the repayment term
become:
Periodic interest rate = APR / 12
(i.e., Monthly Interest Rate is Annual Percentage Rate / 12)
Number of payments = Number of years * 12
Grading for Loan Terms table (6 points): 2 points for Borrowed, 2 points for Monthly Interest Rate, 2
points for Term in months.
for the Loan Amortization Table (9 points): 3 points for Interest Payment Column, 3 points for
Principle Payment column, 3 points for Loan Balance column
a) What is the monthly payment? (3 points)
b) How much total interest will you pay when paid in full according to the loan terms? (3 points)
c) How much principal will you pay when paid in full? (3 points)
d) How much will the house cost in total (principal + interest) when paid in full. (3 points)
e) What is the interest payment for period 12 using the excel IPMT function? (3 points)
f) What is the principal payment for period 12 using the excel PPMT function? (3 points)
g) How much money would you save in interest over the life of the loan if the interest rate was
3.25%, instead of 5.05%? (3 points)
Change cell c6 according to the new interest rate, excel automatically re-does all the
calculations. Now write down the number associated with part (b), lets call this number X,
undo this change (so now everything goes back to the initial value), subtract X from what you
initially calculated for part (b).
3