For many people, owning a house is one of the most significant financial goals. With property prices rising faster than our savings or incomes, a home loan is the best way to fulfill that need.
The EMI of a loan is based on the loan amount, the interest rate and the tenure of the borrowing. If you understand how the bank works out the EMI, you would find it easier to evaluate various loan options. Moreover, you can rejig your loan amount to fit your repayment capacity.
For example, if you can manage an EMI of no more than Rs 25,000 due to certain constraints, you can find out the maximum loan you can take for a specified period. The calculation of the home loan EMI rests on the theory of time value of money. The theory says that a rupee receivable today is more valuable than a rupee receivable at a future date. This is because the rupee received today can be invested to earn interest.
For example, Rs 100 receivable today can be invested at, say, 9% interest and therefore enables one to earn additional Rs 9 in a year. On the same basis, if we assume an annual interest rate of 9%, then the present value of Rs 100 receivable one year from now is Rs 91.74. In case of a Rs 10 lakh home loan at 10% interest for 15 years, one is eventually agreeing to repay the loan, along with the interest cost, over a 180-month period.
Given the interest rate, the bank in turn, calculates the EMI in such a way that the present value of the stream of monthly payments over 180 months should be equal to the amount of home loan. The repayment of home loan through EMI over regular intervals (monthly) is known as annuity and therefore the methodology of computing home loan EMIs is through the present value of an annuity. However, if you know MS Excel, you don't need to bother about the theory.
Given the amount of home loan, interest rate and tenure, the software will compute the EMI in seconds. Moreover, one can make different permutations and combinations based on the inputs (amount, interest rate and tenure). The Excel function that calculates the home loan EMI is known as PMT.
The EMI of a loan is based on the loan amount, the interest rate and the tenure of the borrowing. If you understand how the bank works out the EMI, you would find it easier to evaluate various loan options. Moreover, you can rejig your loan amount to fit your repayment capacity.
For example, if you can manage an EMI of no more than Rs 25,000 due to certain constraints, you can find out the maximum loan you can take for a specified period. The calculation of the home loan EMI rests on the theory of time value of money. The theory says that a rupee receivable today is more valuable than a rupee receivable at a future date. This is because the rupee received today can be invested to earn interest.
For example, Rs 100 receivable today can be invested at, say, 9% interest and therefore enables one to earn additional Rs 9 in a year. On the same basis, if we assume an annual interest rate of 9%, then the present value of Rs 100 receivable one year from now is Rs 91.74. In case of a Rs 10 lakh home loan at 10% interest for 15 years, one is eventually agreeing to repay the loan, along with the interest cost, over a 180-month period.
Given the interest rate, the bank in turn, calculates the EMI in such a way that the present value of the stream of monthly payments over 180 months should be equal to the amount of home loan. The repayment of home loan through EMI over regular intervals (monthly) is known as annuity and therefore the methodology of computing home loan EMIs is through the present value of an annuity. However, if you know MS Excel, you don't need to bother about the theory.
Given the amount of home loan, interest rate and tenure, the software will compute the EMI in seconds. Moreover, one can make different permutations and combinations based on the inputs (amount, interest rate and tenure). The Excel function that calculates the home loan EMI is known as PMT.
Let us understand this function in detail with the help of an example: Mr A wants to take a home loan of Rs 30 lakh for 20 years. The interest rate offered by the bank is 11% per annum on a monthly reducing basis. Open an Excel sheet and go to 'formulas'. Select 'insert' function and select 'financial' from the drop box menu. In the financial function, select PMT. When a box appears on the screen, follow the steps given in the graphic.
The inputs
The PMT function requires you to input the variables. The first is the rate, which is the interest rate charged by the bank. In this case, it is 11%. But since the EMI will be paid monthly, this rate needs to be divided by a factor of 12.
The second input is Nper, which is the tenure of the loan. In our example, the tenure is 20 years. But since the loan is repaid in monthly installments, the Nper needs to be multiplied by the factor of 12.
The third input is the Pv, which is the amount of the loan. In this case, it is Rs 30 lakh. The fourth input Fv should be left blank.
Finally, the last input Type asks whether the EMI payment will be made at the end of every month or at the beginning of every month. If the payment is to be made at the beginning of every month, then put 1 in this field. But if the payment is to be made at the end of every month, put 0 or leave it blank. We have assumed that EMI payments are made at the end of every month.
Input variables
Now let us input all these variables to find the EMI of the loan. The EMI comes to Rs 30,966 (see box 2). We notice in the following box, that the EMI appeared with a negative sign. Since the amount has to be paid every month, the negative sign depicts it better.
Alter variables
Now let us change the tenure or Nper to 25 years. As we can see the EMI is reduced to Rs 29,403 (box 3).
In the same way, one can change the interest rate or loan amount or tenure and can easily play with numbers. For example, increasing the loan amount to Rs 40 lakh (keeping the same interest rate and tenure), the EMI jumps to Rs 41,287. Or, at the original loan of Rs 30 lakh for 20 years but at different interest rate of say 10%, the EMI reduces to Rs 28,950. Check out how the EMI changes with alterations in the inputs.
Another way to use this function is to assess your optimal home loan, given your income level and repayment capacity. Keeping the interest rate and tenure constant, one can vary the home loan or Pv and accordingly reach an EMI that is affordable.
Such affordable EMI can only be reached using trial and error by repeatedly changing the home loan amount. However, there are other functions available with Excel that will give you an exact calculation of the optimal loan or affordable EMI, but the same are beyond the scope of this article.
The inputs
The PMT function requires you to input the variables. The first is the rate, which is the interest rate charged by the bank. In this case, it is 11%. But since the EMI will be paid monthly, this rate needs to be divided by a factor of 12.
The second input is Nper, which is the tenure of the loan. In our example, the tenure is 20 years. But since the loan is repaid in monthly installments, the Nper needs to be multiplied by the factor of 12.
The third input is the Pv, which is the amount of the loan. In this case, it is Rs 30 lakh. The fourth input Fv should be left blank.
Finally, the last input Type asks whether the EMI payment will be made at the end of every month or at the beginning of every month. If the payment is to be made at the beginning of every month, then put 1 in this field. But if the payment is to be made at the end of every month, put 0 or leave it blank. We have assumed that EMI payments are made at the end of every month.
Input variables
Now let us input all these variables to find the EMI of the loan. The EMI comes to Rs 30,966 (see box 2). We notice in the following box, that the EMI appeared with a negative sign. Since the amount has to be paid every month, the negative sign depicts it better.
Alter variables
Now let us change the tenure or Nper to 25 years. As we can see the EMI is reduced to Rs 29,403 (box 3).
In the same way, one can change the interest rate or loan amount or tenure and can easily play with numbers. For example, increasing the loan amount to Rs 40 lakh (keeping the same interest rate and tenure), the EMI jumps to Rs 41,287. Or, at the original loan of Rs 30 lakh for 20 years but at different interest rate of say 10%, the EMI reduces to Rs 28,950. Check out how the EMI changes with alterations in the inputs.
Another way to use this function is to assess your optimal home loan, given your income level and repayment capacity. Keeping the interest rate and tenure constant, one can vary the home loan or Pv and accordingly reach an EMI that is affordable.
Such affordable EMI can only be reached using trial and error by repeatedly changing the home loan amount. However, there are other functions available with Excel that will give you an exact calculation of the optimal loan or affordable EMI, but the same are beyond the scope of this article.
