Question

Question 1: You are required to produce an amortisation table for a home loan and a diagram demonstrating the link between loan repayments and principal outstanding. Please see slide 31 from Topic 2 (or p146 from text) for an example of the layout of the table. The home loan is for $200,000 and is to be amortised over a time period of 30 years requiring annual payments. All calculations should be executed in excel. From your table produce a diagram that demonstrates the relationship between the outstanding principal and the number of years into the loan.

The interest rate to be used is 12% plus the last digit of your student number. Assume that interest rates do not change over the life of the laon.

Answer 1

Answered using goal seek function in excel

Principal	$ 200,000.00
Period	30 year
interest rate	12%
Year	Interest	Annual payments	Balance
1	$    24,000.00	$             24,828.73	$      199,171.27
2	$    23,900.55	$             24,828.73	$      198,243.09
3	$    23,789.17	$             24,828.73	$      197,203.53
4	$    23,664.42	$             24,828.73	$      196,039.22
5	$    23,524.71	$             24,828.73	$      194,735.20
6	$    23,368.22	$             24,828.73	$      193,274.69
7	$    23,192.96	$             24,828.73	$      191,638.92
8	$    22,996.67	$             24,828.73	$      189,806.86
9	$    22,776.82	$             24,828.73	$      187,754.95
10	$    22,530.59	$             24,828.73	$      185,456.81
11	$    22,254.82	$             24,828.73	$      182,882.90
12	$    21,945.95	$             24,828.73	$      180,000.11
13	$    21,600.01	$             24,828.73	$      176,771.39
14	$    21,212.57	$             24,828.73	$      173,155.23
15	$    20,778.63	$             24,828.73	$      169,105.13
16	$    20,292.62	$             24,828.73	$      164,569.01
17	$    19,748.28	$             24,828.73	$      159,488.56
18	$    19,138.63	$             24,828.73	$      153,798.45
19	$    18,455.81	$             24,828.73	$      147,425.54
20	$    17,691.06	$             24,828.73	$      140,287.87
21	$    16,834.54	$             24,828.73	$      132,293.68
22	$    15,875.24	$             24,828.73	$      123,340.19
23	$    14,800.82	$             24,828.73	$      113,312.29
24	$    13,597.47	$             24,828.73	$      102,081.03
25	$    12,249.72	$             24,828.73	$        89,502.02
26	$    10,740.24	$             24,828.73	$        75,413.53
27	$      9,049.62	$             24,828.73	$        59,634.42
28	$      7,156.13	$             24,828.73	$        41,961.82
29	$      5,035.42	$             24,828.73	$        22,168.51
30	$      2,660.22	$             24,828.73	$                   0.00