Question

You borrow ​$400,000 over a 20​ year term. The loan is structured as an amortized loan with annual payments and an interest rate of 8​%. Complete the cells in the amortization schedule below.

Year	Payment​ ($)	Interest in Payment​ ($)	Principal Repaid​ ($)	Principal Owing at End of Year​ ($)
1
2

Answer 1

The formula for calculating the monthly equalised installments is.

[P x R x (1+R)^N]/[(1+R)^N-1], where P stands for the loan amount or principal, R is the interest rate per month

Given LOan amount = 4,00,000

Term = 20 Years

Interest Rate = 8% per annum

Let us calculate the Equalised installments

(4,00,000 * 0.08 * (1.08)^20)/((1.08)^20-1)

= 400000 * 0.372877 / 3.660957 = 40740.88

Year	Opening Loan	Annualised Payment	Interest Payment	Principal Payment	Closing Principal Balance
1	4,00,000	40,741	32,000	8,741	3,91,259
2	3,91,259	40,741	31,301	9,440	3,81,819
3	3,81,819	40,741	30,546	10,195	3,71,624
4	3,71,624	40,741	29,730	11,011	3,60,613
5	3,60,613	40,741	28,849	11,892	3,48,721
6	3,48,721	40,741	27,898	12,843	3,35,878
7	3,35,878	40,741	26,870	13,871	3,22,007
8	3,22,007	40,741	25,761	14,980	3,07,027
9	3,07,027	40,741	24,562	16,179	2,90,848
10	2,90,848	40,741	23,268	17,473	2,73,375
11	2,73,375	40,741	21,870	18,871	2,54,504
12	2,54,504	40,741	20,360	20,381	2,34,123
13	2,34,123	40,741	18,730	22,011	2,12,112
14	2,12,112	40,741	16,969	23,772	1,88,340
15	1,88,340	40,741	15,067	25,674	1,62,667
16	1,62,667	40,741	13,013	27,728	1,34,939
17	1,34,939	40,741	10,795	29,946	1,04,993
18	1,04,993	40,741	8,399	32,341	72,652
19	72,652	40,741	5,812	34,929	37,723
20	37,723	40,741	3,018	37,723	0
		Total Interest	4,14,818