Question

Amortization of a Loan

EXAMPLE EXERCISES

For the following exercises,

create an amortization table for a $21,500 car loan with 4.5% APR over 72

months.

1. What is the monthly payment?

2. How much is actually paid over the life of the loan? How much interest?

3. How much is saved if you round up to the next $50 increment? Next $100?

For the following exercises, create an amortization table for $135,000 home purchase at 4.6% fixed APR. Remember mortgages are generally paid monthly.

4. 30 years

5. 15 years

6. How do the 15-year and 30-year loans compare in total amount paid and interest paid?

7. How much is saved if you round each payment up to the nearest $100

Answer 1

Calculate the amount of installment and prepare the amortization table in the following manner:

A C D 1 2 3 4 5 6 Calculation of Amount of installment Particulars Amount Amount of loan 21500 Rate of interest per month -4.5%/12 Number of payments 72 Amount of installment =-PMT(B4,B5,B3,0) 9 Amortization table Number of installment Amount outstanding at the beginning Installment Amount at the end Nm 21500 =E11 =E12 =E13 =E14 =E15 =B11*4.5%/12 =B12*4.5%/12 =B13*4.5%/12 =B14*4.5%/12 =B15*4.5%/12 =B16*4.5%/12 =B17*4.5%/12 =B18*4.5%/12 =B19*4.5%/12 =B20*4.5%/12 =B21*4.5%/12 =B22*4.5%/12 =B23*4.5%/12 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 -341.29 =341.29 =341.29 =341.29 =341.29 =E16 =E17 =E18 =E19 =E20 =E21 =E22 =B11+C11-D11 =B12+C12-D12 =B13+C13-D13 =B14+C14-D14 =B15+C15-D15 =B16+C16-D16 =B17+C17-D17 =B18+C18-D18 =B19+C19-D19 =B20+C20-D20 =B21+C21-D21 =B22+C22-D22 =B23+C23-D23 00 20 10 21 11 22 12 23 13

А =E23 =E24 =E25 =E26 =E27 =E28 =E29 =E30 =E31 =E32 =E33 =E34 =E35 =B24*4.5%/12 =B25*4.5%/12 =B26*4.5%/12 =B27*4.5%/12 =B28*4.5%/12 =B29*4.5%/12 =B30*4.5%/12 =B31*4.5%/12 =B32*4.5%/12 =B33*4.5%/12 =B34*4.5%/12 =B35*4.5%/12 =B36*4.5%/12 =B37*4.5%/12 =B38*4.5%/12 =B39*4.5%/12 =B40*4.5%/12 =B41*4.5%/12 =B42*4.5%/12 =B43*4.5%/12 =B44*4.5%/12 =B45*4.5%/12 =B46*4.5%/12 =B47*4.5%/12 =B48*4.5%/12 =341.29 =341.29 =341.29 -341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =B24+C24-D24 =B25+C25-D25 =B26+C26-D26 =B27+C27-D27 =B28+C28-D28 =B29+C29-D29 =B30+C30-D30 =B31+C31-D31 =B32+C32-D32 =B33+C33-D33 =B34+C34-D34 =B35+C35-D35 =B36+C36-D36 =B37+C37-D37 =B38+C38-D38 =B39+C39-D39 =B40+C40-D40 =B41+C41-D41 =B42+242-D42 =B43+C43-043 =B44+C44-044 =B45+C45-D45 =B46+C46-D46 =B47+C47-D47 =B48+C48-D48 =E36 1=E37 =E38 -E39 -E40 =E41 =E42 =E43 41 31 42 32 43 33 44 34 45 35 46 36 47 37 48 38 =E45 =E46

=E48 =E49 -E50 =E52 =E53 =E54 =E55 -E56 =E57 =E58 =E59 =E60 C =B49*4.5%/12 =B50*4.5%/12 =B51*4.5%/12 =B52*4.5%/12 =B53*4.5%/12 =B54*4.5%/12 =B55*4.5%/12 =B56*4.5%/12 =B57*4.5%/12 =B58*4.5%/12 =B59*4.5%/12 =B60*4.5%/12 =B61*4.5%/12 =B62*4.5%/12 =B63*4.5%/12 =B64*4.5%/12 =B65*4.5%/12 =B66*4.5%/12 =B67*4.5%/12 =B68*4.5%/12 =B69*4.5%/12 =B70*4.5%/12 =B71*4.5%/12 =B72*4.5%/12 =B73*4.5%/12 D =341.29 =341.29 =341.29 =341.29 =341.29 3341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 3341.29 =341.29 =341.29 E =B49+C49-D49 =B50+C50-D50 =B51+C51-D51 =B52+C52-D52 =B53+C53-D53 =B54+C54-D54 =B55+C55-D55 =B56+C56-D56 =B57+C57-D57 =B58+C58-D58 =B59+C59-D59 =B60+C60-D60 =B61+C61-061 =B62+C62-D62 =B63+C63-D63 =B64+C64-D64 =B65+C65-D65 =B66+C66-D66 =B67+C67-D67 =B68+C68-D68 =B69+C69-D69 =B70+C70-D70 =B71+C71-D71 =B72+C72-D72 =B73+C73-D73 =E61 =341.29 =E62 =E63 =E64 =E65 =E66 =E67 =E68 =E69 =341.29 =341.29 =341.29 =E70 =341.29 -341.29 =341.29 -341.29 =341.29 =E71 =E72

А =E58 -E59 =E60 =E61 =E63 =E64 =E65 =E67 =E68 =E69 =B59*4.5%/12 =B60*4.5%/12 =B61*4.5%/12 =B62*4.5%/12 =B63*4.5%/12 =B64*4.5%/12 =B65*4.5%/12 =B66*4.5%/12 =B67*4.5%/12 =B68*4.5%/12 =B69*4.5%/12 =B70*4.5%/12 =B71*4.5%/12 =B72*4.5%/12 =B73*4.5%/12 =B74*4.5%/12 =B75*4.5%/12 =B76*4.5%/12 =B77*4.5%/12 =B78*4.5%/12 =B79*4.5%/12 =B80*4.5%/12 =B81*4.5%/12 =B82*4.5%/12 D =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 -341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 =341.29 E =B59+C59-D59 =B60+C60-D60 =B61+C61-D61 =B62+C62-D62 =B63+C63-D63 =B64+C64-D64 =B65+C65-D65 =B66+C66-D66 =B67+C67-D67 =B68+C68-D68 =B69+C69-D69 =B70+C70-D70 =B71+C71-D71 =B72+C72-D72 =B73+C73-D73 =B74+C74-D74 =B75+C75-D75 =B76+C76-D76 =B77+C77-D77 =B78+C78-D78 =B79+C79-D79 =B80+C80-D80 =B81+C81-081 =B82+C82-D82 =F71 -E72 -F75 EE76 =E77 =E78 79 69 80 70 81 71 =E79 =E80 =E81 82 72

The result will be as follows

1 2 3 4 5 6 7 А в Calculation of Amount of installment Particulars Amount Amount of loan 21500 Rate of interest per month 0.38% Number of payments 72 Amount of installment $341.29 8 Amortization table Number of installment 9589599 9 9 as a Amount outstanding at Interest Installment Amount at the end the beginning 21500 80.625 341.29 21239.335 21239.335 79.64750625 341.29 20977.69251 20977.69251 78.6663469 341.29 20715.06885 20715.06885 77.6815082 341.29 20451.46036 20451.46036 76.69297636 341.29 20186.86334 20186.86334 75.70073752 341.29 19921.27408 19921.27408 74.70477778 341.29 19654.68885 19654.68885 73.7050832 341.29 19387.10394 19387.10394 72.70163976 341.29 19118.51558 19118.51558 71.69443341 341.29 18848.92001 18848.92001 70.68345004 341.29 18578.31346 18578.31346 69.66867547 341.29 18306.69213 18306.6921368.65009551 341.29 18034.05223

B 18034.05223 17760.38993 |17485.70139 17209.98277 16933.2302 |16655.43982 |16376.60772 |16096.73 15815.80273 15533.82199 |15250.78383 14966.68427 |14681.51933 14395.28503 |14107.97735 13819.59226 13530.12573 13239.57371 |12947.93211 12655.19685 12361.36384 12066.42895 11770.38806 11473.23702 11174.97166 |67.62769586 |341.29 66.60146222 |341.29 |65.57138021 | 341.29 | 64.53743538 341.29 63.49961327 |341.29 62.45789931 1341.29 61.41227894 | 341.29 60.36273748 | 341.29 |59.30926025 | 341.29 58.25183247 |341.29 |57.19043935 | 341.29 56.12506599 |341.29 |55.05569749 | 341.29 53.98231886 |341.29 |52.90491505 | 341.29 51.82347098 |341.29 50.7379715 |341.29 49.64840139 | 341.29 |48.5547454 341.29 47.45698819 |341.29 46.3551144 341.29 |45.24910858 |341.29 |44.13895524 | 341.29 |43.02463882 | 341.29 41.90614371 341.29 |17760.38993 17485.70139 |17209.98277 16933.2302 |16655.43982 |16376.6072 |16096.73 15815.80273 |15533.82199 |15250.78383 |14966.68427 14681.51933 |14395.28503 14107.9735 |13819.59226 13530.12573 |13239.57371 12947.93211 |12655.19685 12361.36384 12066.42895 11770.38806 |11473.23702 11174.97166 10875.5878

| E 8 6 42 44 45. 46 48 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 (11174.97166 141.90614371 341.29 (10875.5878 ]40.78345425 341.29 10575.08126 ]39.65655471 341.29 (10273.44781 ]38.52542929 1341.29 9970.683239 37.39006215 341.29 (9666783301 ]36.25043738 341.29 | 9361.743739 35.10653902 341.29 9055.560278 133.95835104 1341.29 8748.228629 32.80585736 341.29 8439.744486 31.64904182 341.29 8130.103528 30.48788823 341.29 (7819.301416 ]29.32238031 1341.29 (7507.333796 28.15250174 1341.29 (7194.196298 ]26.97823612341.29 (6879.884534 25.799567 ]341.29 (6564.394101 ]24.61647788 341.29 6247720579 23.42895217341.29 (5929.859531 ]22.23697324 341,29 5610.806504 ]21.04052439 341.29 15290.557029 19.83958886 341.29 (4969.106618 ]18.63414982 341.29 (4646.450768 ]17.42419038 341.29 | 4322.584958 ]16.20969359 341.29 3997.504651 ]14.99064244 341.29 3671.205294 ]13.76701985 341.29 (10875.5878 10575.08126 10273.44781 9970.683239 9666783301 9361.743739 (9055.560278 8748.228629 8439.744486 8130.103528 | 7819.301416 7507.333796 7194.196298 (6879.884534 (6564.394101 6247720579 5929.859531 5610.806504 5290.557029 (4969.106618 4646450768 4322.584958 3997.504651 3671.205294 3343.682314 52 - 8 8 8 60 | _61 _62

65 66 в 3671.205294 3343.682314 3014.931122 2684.947114 2353.725666 2021.262137 1687.55187 1352.59019 1016.372403 678.8937993 340.1496511 13.76701985 /341.29 12.53880868 341.29 11.30599171 |341.29 |10.06855168 341.29 18.826471247 341.29 7.579733014 (341.29 б.32831951 |341.29 |5.072213211 341.29 |3.811396511 341.29 |2.545851748 |341.29 |1.275561192 341.29 |3343.682314 |3014.931122 2684.947114 |2353.725666 2021.262137 |1687.55187 |1352.59019 |1016.372403 678.8937993 | 340.1496511 |0.135212278 69 70

1.

The monthly payment is $341.29

2.

Amount actually paid over the life of the loan is as follows:

1 Amount of installment 2 Number of installments 3 Total amount paid=Amount*Number 4 amount of loan Interest paid=Amount of loan-Total 5 Amount paid 341.29 72 24572.88 $21,500 $3,073

3.

Amount saved after rounding to next $50 and next $100

А 1 Particulars 2 Amount of loan 3 Amount of payment 4 Rate of interest 5 Number of payments B Amount Amount 21500 21500 =350 400 -4.5%/12 =4.5%/12 =NPER(B4,B3,-B2,0) =NPER(C4,C3,-C2,0) 6 7 Total amount paid =B5*B3 8 Amount paid as per original terms 24572.88 9 Amount saved =B8-B7 = 5*13 24572.88 =C8-C7

The result will be as follows:

A 1 Particulars 2 Amount of loan 3 Amount of payment 4 Rate of interest 5 Number of payments B C Amount Amount 21500 21500 350 $400 0.00375 0.00375 70 6 7 Total amount paid 8 Amount paid as per original terms 9 Amount saved 24483.13618 24055.50749 24572.88 24572.88 89.74382419 517.3725115