Sameer Bhardwaj | ET Bureau | Nov 4, 2013, 08.00AM IST | Economic Times
The home loan EMI has two components: interest and principal. The Income Tax Department treats these two separately while offering tax benefits to borrowers. The tax relief on principal is allowed under Section 80C, whereas the benefit for interest is allowed under Section 24. The borrowers have to rely on the loan amortisation table sent by the bank to determine the principal or interest repaid.
This is because the EMI does not constitute the two components in the same proportion. The banks charge a relatively higher interest in the initial EMIs and it diminishes over time. Similarly, the initial principal is relatively low compared with the later ones. The amortisation table lists the principal and interest of each EMI paid.
For tax planning, it is important to find out the cumulative interest and principal repaid over a definite period. Since the maximum tax savings under Section 80C are up to Rs 1 lakh, an idea of the cumulative principal paid in the current financial year helps plan the investments. Moreover, when one wants to sell the house that has been purchased using a home loan, the cumulative payments assist in determining the actual return or profit generated.
Suppose Mr A bought a house in 2007 for Rs 30 lakh, which was funded with a home loan of Rs 25 lakh, at 10% interest rate, for 20 years. The house is sold in 2012 for Rs 60 lakh. The gain may appear to be 100% in five years, or a 14.87% annualised return.
However, the loan has been serviced for five years and the seller needs to figure out the cumulative interest paid for 60 months (2007-12) to determine the actual return or profit. The total interest paid on the loan will be Rs 11.92 lakh, which reduces the annualised gain from 14.87% to 9.89%.
Besides the bank’s amortisation statement, one can also use MS Excel, which provides two sets of functions for loan calculations. The first includes standalone functions like PPMT and IPMT, while the second set includes cumulative functions: CUMPRINC and CUMIPMT. The former helps generate the full loan amortisation table, while the latter gives accumulated principal and interest paid over any two time periods. We look at the second set for a Rs 25 lakh loan. Open an Excel sheet and go to formulas. Select ‘insert’ function, and then ‘financial’ from the drop-box menu. In ‘financial’, select CUMPRINC, after which Box 1 will appear:
Let us look at the inputs of the CUMPRINC function. The first is rate, which is the interest rate charged by the bank. In our example, it is 10%. Since, the instalment will be paid monthly, the rate needs to be divided by a factor of 12.
The second input is Nper, which is nothing but the tenure of the loan. In our example, the term is 20 years. Since the loan is repaid in monthly instalments, the Nper needs to be multiplied by the factor of 12. The third input is Pv, which is the home loan, Rs 25 lakh.
The fourth and fifth inputs ask for the time period during which one wants to check for the cumulative principal paid. Since, in our example, the tenure of the loan is 20 years and the payment has to be made in monthly instalments, the minimum acceptable value for the Start_ period is 1, and the maximum acceptable for End_ period is 240.
The last input, Type, asks whether the EMI payment will be made at the end or the beginning of every month. If the payment is to be made at the start of every month, then 1 should be used in this column. On the other hand, if the payment is to be made at the end of every month, 0 should be used. The Type column cannot be left blank as in the case of other functions. We will assume that the EMI payments are made at the end of every month.
(Note: The fifth input is not visible in Box 1, but can be seen on an Excel sheet after moving the scroll bar, which is on the right side.)
Let us put in the variables in both these functions to see how much interest and principal are paid in the first five years, or 60 months.
These results are in Box 2 and Box 3. As can be seen, in the first 60 months, Rs 2.54 lakh is repaid as principal, whereas the interest paid is Rs 11.92 lakh. The CUMPRINC function also helps in determining the outstanding principal.
As is evident from Box 3, the outstanding principal after five years is Rs 22.46 lakh (Rs 25 lakh minus Rs 2.54 lakh).
One can change the inputs in the Start and End periods to determine the cumulative principal/ interest paid between any two periods. For example, if one wants to know the principal repayment in the eighth year for 9 months of the financial year, the Start period will be 85th month, and the End period will be 93rd month. As seen in Box 4, the cumulative principal paid between 85th month and 93rd month is Rs 61,516. Users can put in different combinations of variables depending on their requirements.
Source : http://goo.gl/0i4Aq9