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.

 

Advertisement

Income from Capital Gains for CFP- 2

Determination of Holding Period: To classify the gain as long term or short term we are required to consider the holding period of a capital asset. Lets understand how to determine the holding period:

Different Situation	How to calculate the holding period
Shares held in a company in liquidation	The period after the date of commencement of liquidation is excluded
Cases covered u/s 49(1) [i.e. when asset is acquired by gift, inheritance etc]	The period for which the asset held by the previous owner should be included
Allotment of shares in amalgamated Indian company	From the date of acquisition of shares in the amalgamated company
Right shares	From the date of allotment
Bonus Shares	From the date of allotment
Demerger	The period of holding of shares in the demerged company shall be included in the total period of holding of shares

Assessment:

An immovable property will be termed as a short-term capital asset if it is held by the assessee for _______.

1. not more than 36 months
2. more than 36 months
3. exactly twelve months
4. more than 12 months

Answer:1

Assessment:

Mr. A was inherited a house after the demise of his father. His father purchased this home in April 1982. His father died in March 2009. Mr. A is now selling the same house in March 2011. He wants to know whether this house is long term asset or short therm asset for him.

Answer: Long term.

Reason: Since he is inherited the house, for the purpose of ascertaining holding period, the holding period of his father is to be added in the holding period of Mr. A. Hence his holding period will be 2+27 = 29 years and the gain is long term.

Till now we have understood the meaning of capital asset and holding period, now next to begin with is transfer.

Transfer Sec 2(47)

Transfer in relation to capital asset includes:

• Sale, exchange or relinquishment of the asset
• The compulsory acquisition thereof under any law
• Maturity or redemption of zero coupon bonds

Where requinshment means a verbal act of renouncing a claim, right or position etc. or the act of giving up and abandoning.