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

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
BS Buildtech launches ‘Vaibhav Heritage Height’ in Noida
Oct 22, 2013
NOIDA: B S Buildtech launches “Vaibhav Heritage Height” which is a luxurious residential development for privileged few, who want the best of ultra-modern living. Uncover the blend of fresh, dynamic and opulent lifestyle with Vaibhav Heritage Height. This masterpiece of architectural creativity is designed to offer an elegant lifestyle. Each home here is distinctive in design that beautifully accentuates the landscape of this community. The lavish and lush ambience, the project design & architec
Petition against PDA-Omaxe JDA dismissed:Punjab HC
Oct 22, 2013
DELHI:PDA Omaxe City, a Joint Development Agreement (JDA) between real estate major Omaxe Ltd. and PDA is all set to see the light of the day as the petition challenging the veracity and legality of the JDA project was dismissed as withdrawn in Punjab and Haryana High Court.
Office space supply falls sharply by over 75% in Jul-Sept
Oct 22, 2013
DELHI: Office space supply fell by more than 75 per cent during July-September period in the top seven cities compared with the previous quarter due to high vacancy in completed buildings and poor commitments in the under- construction projects, according to property consultant CBRE.
Realty prices at local ‘micro-markets’ double in 2 years in Delhi-NCR
Oct 21, 2013
DELHI: Property prices in NCR’s upcoming residential markets, which real estate experts term ‘residential micro-markets’, have nearly doubled in the last two years. This trend, analysts say, is owing to new options being made available in these micro-markets which has become an attractive proposition for buyers who can’t afford to enter the prime real estate market.
Unitech inks Rs 1,000 crore office leasing deal with Accenture
Oct 20, 2013
DELHI: Realty firm Unitech Group has leased 8.1 lakh square feet of space in its under-construction Gurgaon IT SEZ to Accenture in a deal that will give Rs 1,000 crore of rental income over 15 years.

Latest Realty News Of State

Realty Talk's