Simplify financial life: Calculate your home loan installments using MS Excel
Oct 24, 2013
Source : The Economic Times


DELHI: 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. 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

Simplify financial life: Calculate your home loan installments using MS Excel

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

Simplify financial life: Calculate your home loan installments using MS Excel

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

Simplify financial life: Calculate your home loan installments using MS Excel

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.


Latest Realty News

Real estate emerges as new investment destination for banks
Oct 24, 2013
DELHI: Real estate has emerged as a hot investment destination with banks, as projects in other sectors of the economy have faltered, prompting banks to rush to fund commercial realty despite slow demand and inventory pileup.
Gurgaon shows resilience to recession
Oct 24, 2013
DELHI: Golf Course Road continues to dominate Gurgaon’s suburbs as the prime address for both residential and commercial property. It is followed closely by Golf Course Extension Road, which is emerging as yet another zone for luxury real estate and golf-themed projects.
With 840 sq ft office space per person, Air India staff enjoy a palatial tower
Oct 24, 2013
DELHI: The 268 employees of Air India currently stationed in its 24-storey eponymous building at Nariman Point in Mumbai have a rare privilege no other office-goer in the area can think of.
What to Look for in a Township Property
Oct 24, 2013
DELHI: In the present era of Indian real estate, townships have become the most important model of real estate development in the foreseeable future. Blame infrastructure that has not stayed in tune with the actual real estate development in the large Indian cities. The citizens have become victims of intense chaos and lack of civic amenities.
Save money: Tips before you take a home loan
Oct 24, 2013
DELHI: Home loans can be very beneficial for property buyers, as they not only help you buy your dream home, but also help you save on taxes. But you must remember to choose the right home loan if you do not want to face the hassles in the process. Here are a few quick tips that you could keep in mind while applying for a home loan. These tips could help ease the complicated procedure a little bit and at the same time help you save some money.
Home buyers in no hurry, expect real estate prices to fall by up to 10%
Oct 24, 2013
DELHI: Home buyers across the country expect real estate prices to fall by up to 10% over the next six months and they are willing to wait for over a year to buy property, a study by IIMBangalore and MagicBricks has found, signalling that festive season offers by developers have failed to attract buyers.
Centre to appoint realty experts in 15 states to boost housing
Oct 24, 2013
DELHI: The Centre will soon appoint real estate experts and consultants in 15 major states for helping them to prepare affordable housing policy and streamline the rules for approving realty projects, a top government official said today.
Scapes to do a preview of ‘Scapes Siolim’ at Royal Fables
Oct 23, 2013
DELHI: Scapes, creators of luxury boutique collection of villas & world class hospitality will host an exclusive preview of their ambitious retreat project, ‘Scapes Siolim’ at Royal Fables – Season 5.
Sold as a dream, CWG flats turn nightmare for residents; Shabby maintenance, lack of amenities irk b...
Oct 23, 2013
DELHI: They were touted as India’s most sought-after apartments — a riverfront view, Olympic sports facilities, and top businessmen, politicians and celebrities for neighbours, but the Commonwealth Games Village has just been a bag of worries for the apartment owners.
Commercial office supply records a dip: CBRE
Oct 22, 2013
DELHI: Less than 3 million sq.ft of office space entered India’s prime real estate market in the quarter ending September 2013—dropping by more than 75% q-o-q over last quarter’s 10 million sq.ft of fresh office space supply; and by nearly 50% over the same period last year. Delayed deliveries from previous quarters, along with new projects coming on-stream, had led to an increase of upto 8% q-o-q and about 16% y-o-y in office supply addition in the previous April–June period; but the July–Septe

Latest Realty News Of State

Realty Talk's