Special Post: How to prepare loan repayment/amortization schedule

There have been so many queries all around on the preparation of loan amortization/repayment schedule. So here I am producing the systematic understanding on the preparation of the same.

The rate of interest which is quoted on the loan is always a nominal rate which is the uncompounded rate or unadjusted rate hence, banks always earns better rate in effective terms than what they indicate to the borrower.

Assume a loan of Rs.1,00,000 at a rate of 12%p.a. and the tenure is 10 years.

Calculation of EMI:

PV=-100000

N=10*12=120

I/Y=12/12=1

PMT(end mode) =1435

Lets prepare the pro-forma of the same.

Month	Opening Balance	Interest	EMI	Capital
1	100000	=100000*0.01=1000	1435	=1435-1000=435
2	=100000-435=99565	=99565*0.01=995.65	1435	=1435-995.65=439.35

 

The actual repayment schedule when prepared on excel looks like this:

Month	Opening	Interest	EMI	Capital
1	100000	1000	1435	435
2	99565	996	1435	439
3	99126	991	1435	444
4	98682	987	1435	448
5	98234	982	1435	453
6	97781	978	1435	457
7	97324	973	1435	462
8	96862	969	1435	466
9	96396	964	1435	471
10	95925	959	1435	476
11	95449	954	1435	481
12	94968	950	1435	485

 

Now you can observe the basics of equated payment mechanism which states as you go ahead in the repayment schedule the capital component in the installment increases and interest component decreases.

Download the same solution on excel sheet:repayment-schedule

This sheet allows you to insert the fields.