Question

Prepare a table showing ten years of payments on a 10-year loan of $1,000,000 at annual interest = 5%. Include all information shown on the following sample table.

Year	Loan Payment	Loan Interest	Principal Payment	Balance remaining
0				1,000,000
1
2

Revise the table with a new calculation that results from an extra payment of $200,000 at the end of year 3.

Answer 1

Principal Loan = $1,000,000

Interest = 5%

Tenure = 10 Years

In order to create the amortization schedule, below steps must be followed:

1. For year 1, Start with Opening principal amount, note it.

2. Compute the Yearly Loan payment using the MS Excel function PMT

= PMT (rate, nper, pv, fv, [type])

= PMT (0.05, 10, -1000000, 0)

= $129,504.57

3. Compute the Loan Interest on Opening Principal amount using simple interest formula,

Loan Interest = (Principal x rate x Time) / 100

This is the interest paid for the year.

4. Subtract the Interest amount from yearly instalment amount in order to compute Principal Payment.

5. Subtract the Principal paid from Opening principal to compute Balance Remaining.

6. For next year, the closing principal of previous year is the opening principal of current year.

7. Repeat steps 2 to 6 till Year 10.

Below table gives the various values and computation for the repayment of loan.

Yearly Instalment	129504.57	=PMT(0.05,10,-1000000,0)
Year	Opening principal	Loan Interest	Loan Payment	Principal Payment	Closing Principal
1	1000000	50000	129505	79505	920495
2	920495	46025	129505	83480	837016
3	837016	41851	129505	87654	749362
4	749362	37468	129505	92036	657325
5	657325	32866	129505	96638	560687
6	560687	28034	129505	101470	459217
7	459217	22961	129505	106544	352673
8	352673	17634	129505	111871	240802
9	240802	12040	129505	117464	123338
10	123338	6167	129505	123338	0

For the case when extra payment of $200,000 is made at the end of year 3 (beginning of year 4), the revised table with a new calculation is as below:

Yearly Instalment	129504.57	=PMT(0.05,10,-1000000,0)
Year	Opening principal	Loan Interest	Loan Payment	Principal Payment	Closing Principal
1	1000000	50000	129505	79505	920495
2	920495	46025	129505	83480	837016
3	837016	41851	129505	87654	749362
4	749362	37468	329505	292036	457325
5	457325	22866	129505	106638	350687
6	350687	17534	129505	111970	238717
7	238717	11936	129505	117569	121148
8	121148	6057	129505	123447	-2299
9	-2299	-115	129505	129620	-131919
10	-131919	-6596	129505	136101	-268019

The loan is completely repaid in Year 8.