Tuesday, November 5, 2013

How to calculate EMIs, Home Loan payments, home affordability, pre-payment of a loan in Microsoft Excel?

Author: Sachin Gupta | Find me on Twitter

Many a times, we have been inundated with queries such as how to calculate the Equated Monthly Installments (EMIs), what is the formula for checking the home affordability, what amount will I have to pay if I pre pay my home loan? These are basic yet important questions and therefore, understanding these concepts is crucial for real estate investment. Here we present the formulas in Microsoft Excel for you to calculate EMIs, Interest payments, home affordability, pre payment, changing the loan tenure.

Equated Monthly Installments (EMIs):
As the name suggests, EMIs are the monthly payments you will make for loan against property or any other thing.

Here is an example:
Loan Amount (Rs) - 100000
Interest Rate (%) - 11
Loan Tenure (Years) - 20

EMI (Rs) - 1032
Total Interest Payable (Rs) - 147725
Total of Payments (Principal + Interest) (Rs) - 247725

The formula for calculating EMI in excel is given below:
=PMT(rate, nper, pv, [fv], [type])
Rate = Interest Rate in percent, nper=Loan tenure in months, pv=present value or principal amount, fv=future value

During the EMI Calculations, leave out ‘fv’ and ‘type’ and fill in the other values.
=PMT((Interest Rate/12)%, Loan Tenure*12,- Loan Amount)
=PMT((11/12)%, 20*12,- 100000)
EMI = Rs. 1032

In this calculation, we divide interest rate by 12 to arrive at the monthly interest charged.


Interest that is paid on each EMI:
=IPMT(rate, per, nper, pv, [fv], [type])
IPMT – Interest paid for a given EMI
Rate – rate of interest
Per - The month for which you want to find the interest and must be in the range 1 to nper.
Nper- total number of months
Pv – present value or principal amount
Fv- future value
Type- optional

=IPMT((11/12)%, 1, 240, -100000)
=Rs. 916.67 (It means, on your first EMI of Rs 1032, the interest paid will be Rs 916.67)

=IPMT((11/12)%, 240, 240, -100000)
=Rs. 9.38 (It means, on your 240th EMI of Rs 1032, the interest paid will be Rs 9.38)

=IPMT((11/12)%, 200, 240, -100000)
=Rs. 322.15 (It means, on your 200th EMI of Rs 1032, the interest paid will be Rs 322.15)

Similarly, you can calculate for other monthly EMIs by just changing the ‘per’ value from 1 to 240


Total Interest paid during the tenure of the loan:
=CUMIPMT(rate, nper, pv, start_period, end_period, type)
=CUMIPMT((11/12)%, 240, 100000, 1, 240, 0)
=(Rs. 147725.21)

In our example, start period is 1 and end period is 240. You can also calculate the total interest paid say for a period of 13 to 228. In other words, how much interest did you pay from second year on-wards up to the end of 19th year.

=CUMIPMT((11/12)%, 240, 100000, 13, 228, 0)
=(Rs. 136089.79)


Home Affordability:
Home affordability is the measure of the value of the home that you can afford given your current household income. Detailed analysis of home affordability is given here.


Pre Payment or changing the tenure of the loan:
Suppose, you secured a home loan of Rs 100000 in 2008 and have paid 60 EMIs thus far. You have now decided to pre pay your entire loan amount. What will be the value that you will have to pay now? Here is the answer:
=IPMT(rate, per, nper, pv, [fv], [type])/rate
=IPMT((11/12)%, 61, 240, -100000)/ (11/12)%
=(Rs. 90813.93)

Similarly, you can calculate for any period. Say, for example, you have paid 88 EMIs and now want to pre pay the loan amount. Just replace the value of ‘per’ to 89 from 61.
=IPMT((11/12)%, 89, 240, -100000)/ (11/12)%
=(Rs. 84471.24)

Having arrived at the loan balance using the above formula, you can either pre pay the entire balance amount or reduce the tenure of the loan to arrive at new EMIs using the payment formula.
=PMT(rate, nper, pv, [fv], [type])

Thanks


Have any Questions?

9 comments:

  1. Hello,
    Thanks for share a valuable information from your blog.
    Assotech Noida is the complete furnished office space areas at an affordable price in Noida. Assotech Realty launched these commercial hub.

    ReplyDelete
  2. First of all I would like to thank you much for providing such a use full article. This article really helps every Indian middle class peoples who has taken home loan...saverable.com

    ReplyDelete
  3. Today, if you look at financial systems around the globe then you can contact from AllWealthdeals. He is much better financial adviser in Delhi & NCR region.
    loan against property in noida

    ReplyDelete
  4. Hi dear friends,
    how r u ?
    I like Your Blog this is very Informative Blog loan against property and thanx for suggest me if u wants to know about home loans then visit Loan Against Property in Delhi

    ReplyDelete
  5. I have been visiting various blogs for Loan Against properties in India. I have found your blog to be quite useful. Keep updating your blog with valuable information... Regards

    ReplyDelete
  6. Read your blog its really informative and helpful keep updating with newer post on Home loan interest rates Best home loan

    ReplyDelete
  7. Really understood the information of calculate EMIs, Home Loan payments, home affordability, pre-payment of a loan etc.
    Completed luxury projects in chennai

    ReplyDelete
  8. Shreyas Enterprises home loans in Vizag

    Shreyas Enterprises Vizag is engaged in offering Home Loans to the clients In Visakhapatnam. A crew of experienced personnels
    works with enthusiasm for rendering best services

    ReplyDelete