Question

Suppose you borrowed $1,000. The interest rate is 5% per year.  

You are supposed to payoff the principal in equal semi-annual payments in five years along with the interest rate payments. What are your semi-annual payments?

Now assume that you are supposed to make equal semi-annual payments in the form of annuities for the five years.

Draw the amortization table for the two options.

Answer 1

Amortization table for option 1 : Amortization table when principal is paid in equal semi annual payments

Loan = $1000, interest rate = 5%

Semi annual rate = Interest rate / 2 = 5% / 2 = 2.5% per half year

No of semi annual payments = no of years x 2 = 5 x 2 = 10

Principal repaid with each semi annual payment = Loan / No of semi annual payments = 1000 / 10 = $100

For half year = 1 or year = 0.5 years, Beginning balance of loan= Loan = $1000, Interest payment = Beginning balance x semi annual interest rate = 1000 x 2.5% = 25, Total payment = principal payment + interest = 100 + 25 = 125, Ending balance = Beginning balance - Principal payment = 1000 - 100 = 900

For half year = 2 or year = 1 years, Beginning balance of loan = Ending balance of 0.5 years = 900, Interest payment = Beginning balance x semi annual interest rate = 900 x 2.5% = 22.50, Total payment = principal payment + interest = 100 + 22.50 = 122.50, Ending balance = Beginning balance - Principal payment = 900 - 100 = 800

Similarly we can find the values for year other other semi annual payments, we get the following amortization table

Year	Half year	Beginning Balance	Principal Payment	Interest Payment	Total Payment	Ending Balance
0.50	1	1000	100	25.00	125.00	900
1.00	2	900	100	22.50	122.50	800
1.50	3	800	100	20.00	120.00	700
2.00	4	700	100	17.50	117.50	600
2.50	5	600	100	15.00	115.00	500
3.00	6	500	100	12.50	112.50	400
3.50	7	400	100	10.00	110.00	300
4.00	8	300	100	7.50	107.50	200
4.50	9	200	100	5.00	105.00	100
5.00	10	100	100	2.50	102.50	0

Amortization table for option 2 : Amortization table when payment is made in equal semi annual annuities

Loan = $1000, interest rate = 5%

Semi annual rate = Interest rate / 2 = 5% / 2 = 2.5% per half year

No of semi annual payments = no of years x 2 = 5 x 2 = 10

Now first we need to find the equal semi annual payment, We can find the equal semi annual payment by using PMT function in excel

Formula to be used in excel: =PMT(rate,nper,-pv)

Using PMT function in excel, we get equal semi annual payment = $114.2587 = $114.26

For Half year = 1 or year = 0.5 years, Beginning balance of loan= Loan = $1000, Interest payment = Beginning balance x semi annual interest rate = 1000 x 2.5% = 25, principal payment = Equal semi annual payment - interest = 114.2587 - 25 = 89.2587 = 89.26, Ending balance = Beginning balance - Principal payment = 1000 - 89.2587 = 910.7413 = 910.74

For Half year = 2 or year = 1 years, Beginning balance of loan= Ending balance for 0.5 years= $910.7413, Interest payment = Beginning balance x semi annual interest rate = 910.7413 x 2.5% = 22.7685 = 22.77, principal payment = Equal semi annual payment - interest = 114.2587 - 22.7685 = 91.4902 = 91.49, Ending balance = Beginning balance - Principal payment = 910.7413 - 91.4902 = 819.2511 = 819.25

Similarly we can find the values for other half years, we get the following amortization table.

	Amortization Schedule
Year	Half year	Beginning Balance	Monthly Payment	Interest	Principal	Ending Balance
0.5	1	1000.00	114.26	25.00	89.26	910.74
1	2	910.74	114.26	22.77	91.49	819.25
1.5	3	819.25	114.26	20.48	93.78	725.47
2	4	725.47	114.26	18.14	96.12	629.35
2.5	5	629.35	114.26	15.73	98.52	530.83
3	6	530.83	114.26	13.27	100.99	429.84
3.5	7	429.84	114.26	10.75	103.51	326.33
4	8	326.33	114.26	8.16	106.10	220.23
4.5	9	220.23	114.26	5.51	108.75	111.47
5	10	111.47	114.26	2.79	111.47	0.00