Question

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 34 from Topic 2 (or p146 from textbook) for an example of the layout of the table.

The home loan is for $250,000 and is to be amortised over a time period of 15 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 8% Assume that interest rates do not change over the life of the loan.

Answer 1

Loan Amount	Interest Rate	Term in Years	Annual Payment
$250,000.00	8.000%	15	$29,207.39
Year	StartingBalance	Interest	Principal	EndingBalance	TotalInterest
1	$250,000.00	$20,000.00	$9,207.39	$240,792.61	$20,000.00
2	$240,792.61	$19,263.41	$9,943.98	$230,848.64	$39,263.41
3	$230,848.64	$18,467.89	$10,739.50	$220,109.14	$57,731.30
4	$220,109.14	$17,608.73	$11,598.65	$208,510.49	$75,340.03
5	$208,510.49	$16,680.84	$12,526.55	$195,983.94	$92,020.87
6	$195,983.94	$15,678.72	$13,528.67	$182,455.27	$107,699.59
7	$182,455.27	$14,596.42	$14,610.96	$167,844.30	$122,296.01
8	$167,844.30	$13,427.54	$15,779.84	$152,064.46	$135,723.55
9	$152,064.46	$12,165.16	$17,042.23	$135,022.23	$147,888.71
10	$135,022.23	$10,801.78	$18,405.61	$116,616.62	$158,690.49
11	$116,616.62	$9,329.33	$19,878.06	$96,738.57	$168,019.82
12	$96,738.57	$7,739.09	$21,468.30	$75,270.27	$175,758.90
13	$75,270.27	$6,021.62	$23,185.76	$52,084.50	$181,780.52
14	$52,084.50	$4,166.76	$25,040.63	$27,043.88	$185,947.28
15	$27,043.88	$2,163.51	$27,043.88	$0.00	$188,110.79