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. 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 4% plus the last digit of your student number. For example if your student number ends in a ‘0’ then the interest rate is 4%, but if your student number ends in a ‘9’ then the interest rate is 13%. Assume that interest rates do not change over the life of the laon.

Answer 1

Let's say my student number ends in a ‘0’ so the interest rate is 4%.

Amortisation Table:

Year	Initial Principal	Payment	To Taxes	To Principal	Final principal
0					200000
1	200000	$ 11,566.02	8000	$   3,566.02	$   1,96,433.98
2	$     1,96,433.98	$ 11,566.02	7857.359	$   3,708.66	$   1,92,725.32
3	$     1,92,725.32	$ 11,566.02	7709.013	$   3,857.01	$   1,88,868.31
4	$     1,88,868.31	$ 11,566.02	7554.733	$   4,011.29	$   1,84,857.03
5	$     1,84,857.03	$ 11,566.02	7394.281	$   4,171.74	$   1,80,685.29
6	$     1,80,685.29	$ 11,566.02	7227.411	$   4,338.61	$   1,76,346.68
7	$     1,76,346.68	$ 11,566.02	7053.867	$   4,512.15	$   1,71,834.53
8	$     1,71,834.53	$ 11,566.02	6873.381	$   4,692.64	$   1,67,141.89
9	$     1,67,141.89	$ 11,566.02	6685.675	$   4,880.34	$   1,62,261.54
10	$     1,62,261.54	$ 11,566.02	6490.462	$   5,075.56	$   1,57,185.98
11	$     1,57,185.98	$ 11,566.02	6287.439	$   5,278.58	$   1,51,907.40
12	$     1,51,907.40	$ 11,566.02	6076.296	$   5,489.72	$   1,46,417.68
13	$     1,46,417.68	$ 11,566.02	5856.707	$   5,709.31	$   1,40,708.37
14	$     1,40,708.37	$ 11,566.02	5628.335	$   5,937.69	$   1,34,770.68
15	$     1,34,770.68	$ 11,566.02	5390.827	$   6,175.19	$   1,28,595.49
16	$     1,28,595.49	$ 11,566.02	5143.82	$   6,422.20	$   1,22,173.29
17	$     1,22,173.29	$ 11,566.02	4886.932	$   6,679.09	$   1,15,494.20
18	$     1,15,494.20	$ 11,566.02	4619.768	$   6,946.25	$   1,08,547.95
19	$     1,08,547.95	$ 11,566.02	4341.918	$   7,224.10	$   1,01,323.85
20	$     1,01,323.85	$ 11,566.02	4052.954	$   7,513.07	$      93,810.78
21	$        93,810.78	$ 11,566.02	3752.431	$   7,813.59	$      85,997.19
22	$        85,997.19	$ 11,566.02	3439.888	$   8,126.13	$      77,871.06
23	$        77,871.06	$ 11,566.02	3114.842	$   8,451.18	$      69,419.88
24	$        69,419.88	$ 11,566.02	2776.795	$   8,789.22	$      60,630.66
25	$        60,630.66	$ 11,566.02	2425.226	$   9,140.79	$      51,489.87
26	$        51,489.87	$ 11,566.02	2059.595	$   9,506.43	$      41,983.44
27	$        41,983.44	$ 11,566.02	1679.338	$   9,886.68	$      32,096.76
28	$        32,096.76	$ 11,566.02	1283.87	$ 10,282.15	$      21,814.61
29	$        21,814.61	$ 11,566.02	872.5843	$ 10,693.44	$      11,121.17
30	$        11,121.17	$ 11,566.02	444.8469	$ 11,121.17	$               -0.00

Please do rate me and mention doubts in the comments section.