Saturday, May 1, 2021

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?

28 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. 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
  4. 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
  5. Really understood the information of calculate EMIs, Home Loan payments, home affordability, pre-payment of a loan etc.
    Completed luxury projects in chennai

    ReplyDelete
  6. Dwello is the right place to buy Homes. Buy your new homes with confidence with dwello. Dwello - Right Address is Waiting.
    Kukreja Classic
    La Palacio
    La Queen
    La Riveria
    Lakhani Royale

    ReplyDelete
  7. Thanks for sharing the article Landindia
    Land India are experts in land transaction and land banking and provide exceptional service to those interested in acquiring residential , commercial , industrial or agriculture land

    ReplyDelete
  8. ACE Parkway is ultra luxurious ongoing projects by ACE that offering super luxury apartments in noida sector 150 with flexible payment plan 15% now, 85% on possession.

    ReplyDelete
  9. Thanks for sharing the important Properties Latest Information with us.This site is very helpful for property in Delhi NCR and Lucknow property in Gurgaon, Residential flat in Delhi and Residential Plots in Lucknow. Click here for more information on Residential property, Industrial property,Agricultural property, Commercial property

    ReplyDelete
  10. Thanks for sharing this info. Gulberg Greens is another best option.

    ReplyDelete
  11. Thanks for sharing this info. Blue World City is another best option.

    ReplyDelete
  12. Thanks for informative. Capital Smart City is also best place.

    ReplyDelete
  13. Thanks for informative. <Trivelles Smart Villas is also best living place.

    ReplyDelete
  14. This is really informative blog. there are so many people who had confusion about how to calculate EMI, loan and all. This will be helpful for them.

    Apartments in Perambur
    New Flats for Sale in Perambur
    Apartments for Sale in Porur
    Luxury Apartments in Chennai​
    Luxury flats for sale in Chennai
    Highest building in Chennai

    ReplyDelete
  15. Your blog is very nice and informative. We are offering residential flats in Mahavir Spring Thane in the budget.

    ReplyDelete
  16. Dwello is the right place to buy Homes. Buy your new homes with confidence with dwello. Dwello - Right Address is Waiting.
    The Palm Oak
    The Signature
    Tirupati Garden
    Titanium Paradise
    Titanium Woods

    ReplyDelete
  17. Got lots of important about equity. I am a student and want to buy my first house. I took help from Home Equity Loan Mississauga. I have got a moneylender. Thank you so much for giving us amazing information.

    ReplyDelete
  18. You have shared exclusive information about real estate. Get your dram home inside the Godrej Nirvaan Thane .

    ReplyDelete
  19. Great information shared on EMI Loan calculation. I would really like to come back again right here for likewise good articles or blog posts.

    ReplyDelete
  20. It is very nice and helpful for your post shapoorji Pallonji Oxford Bavdhan Offers luxury Apartment.

    ReplyDelete
  21. Exclusive offers going on for all Shapoorji Pallonji Residential projects. No pre-EMI, Price protection, 100% refund guarantee, and a free set of modular kitchen, Fixed rent income, and much more for Shapoorji Pallonji Thane, Shapoorji Pallonji Bangalore, Shapoorji Pallonji Pune, Shapoorji Pallonji Mumbai, Shapoorji Pallonji Howrah, Shapoorji Pallonji Virar.

    ReplyDelete
  22. India is really big market for real estate but we can also say Blue World City is also an option for investment.

    ReplyDelete
  23. I really like your content and information. Nice article thanks for sharing this article with us. Know more about the best Home Buying Companies in NJ.

    ReplyDelete
  24. Nice post to read. Here you explain all the facts nicely & Perfectly. If you wish to know more about Sydney property market, can visit. Keep sharing….. Cheers…!!!

    ReplyDelete
  25. Do you really want to buy a dream home that can cater for your comprehensive need and provide optimum peace and comfort? Well, in such a case, you need to visit homewastate.com, where you can easily find new homes in Sammamish WA and nearby location.

    ReplyDelete
  26. Such an interesting and informative piece of guidance imparted by you. I am glad to discover this information here and I am sure that this might be beneficial for us. Home Buying Companies in NJ

    ReplyDelete
  27. Very helpful blog. Thanks for sharing. Astral City Binh Duong apartment project The project is positioned as a symbol of the pinnacle position in Binh Duong, see more astral city
    astral city bình dương
    astral city phát đạt
    căn hộ astral city bình dương
    astral city thuận an
    du an astral city
    dang realtor

    ReplyDelete