In: Accounting
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.
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 |