If you own a property and receive rental income from it, use this calculator to determine the return on your investment.
If you like idea of using real estate for financial leverage, this is the perfect tool for you. Financial leverage is investing with borrowed capital. In the present context it refers to buying a property with a home loan for rental income and associated tax benefits. For the first 1-3 years returns would be very low (negative). After a few years returns will be approximately equal to the actual appreciation in the property price. This calculator gives the annual return for 30 years. It allows the user to account for periods in which the property was vacant.
How does it work? The calculator takes into account all possible expenses and tax deductions associated with a property, in addition to the rent received and a (reasonable) rate of appreciation of the property. Since rental income cannot be assumed to be constant, you will need to indicate how many months in a year the property was occupied. The corresponding rent also has to be entered.
Payments/Expenses: These include the EMI (if applicable), all costs associated with the purchase (stamp duty, registration, VAT, service tax etc.), interior decoration costs, monthly society charges (or maintenance) that appreciates at some rate, municipal taxes (property, water and sewage) and home insurance premium. Tax from rental income is also accounted for.
Receipts/deductions: These include the entire interest component of the EMI as a tax deduction and of course the (post-tax) rent received. If there is a loss from the property, it is assumed to be adjusted against other taxable income in the same financial year.
Have I missed out any expense? Do let me know.
Output: The compounded annual growth rate (CAGR) is determined using Excels XIRR function, each year, for 30 years. For calculating CAGR, value of the property at the end of each year is taken as the final amount received from the investment after deducting the home loan balance.
The rate of appreciation of property value is crucial to the calculation. If you do not assume a realistic rate, results will reflect that. So is rental income for that matter.
Note:
- Should not be used for self-occupied property. Value of such property is (ground) zero!
- The calculator assumes all initial costs (payment to the builder, stamp duty, registration charges, and interiors) are made on the same date!
- The EMI payment (and hence tax deduction) is also assumed to start only when the apartment if fully furnished and read for rent.
- You could specify a gap (in months) between initial costs and start of EMI payment.
- Points 3 and 4 may sound quite impractical since things never ever pan out this way. However, I am reasonably convinced that the return calculation (especially after 5 years or so) will not be affected too much because of this. If you are not convinced let me know, I will happy to explain why I think so.
- The entire EMI is treated as an expense, not just the interest component.
Download the Real Estate Returns Calculator
If you don't wish to enter rent for each year and would only like to see a projection of returns for a given rent, this is the one for you:
Download Real Estate Returns Calculator (simpler version)
Credits:
- Subramoney coaxed me into making this calculator. I thank him for several useful suggestions.
- Parag Rijwani, Assistant Professor, Nirma Institute of Management for pointing out errors and for suggesting the simpler version.
Related articles
- Financial Leverage in Real Estate
- The Changed Model of Real Estate Returns
Register for the Hyderabad DIY Investor Workshop Nov 27th 2016
Check out the latest mutual fund returns listing
Buy our New Book!You Can Be Rich With Goal-based Investing A book by P V Subramanyam (subramoney.com) & M Pattabiraman. Hard bound. Price: Rs. 399/- (Rs. 359/- at Amazon.in). Read more about the book and pre-order now! |
Why is there an error 523 in all cells beyond year 12? Even with changing the EMI tenure to 20years, it remains there. Am I doing something wrong or is it some bug. Please check it out.
And thanks for a very required kind of decent calculator.
Ramesh,
I don't seem to get it! Works fine up to 30 years for me. Please check the inputs again.
Never mind.
It was a problem in LibreOffice. It works fine in Excel 2007.
Good stuff,
Thank you.
Sir you are becoming prolific with your calculators... great! keep them coming. Room pottu yosipeengalo? You should think of creating a brand... may be pattucal 🙂
Pattu Sir,
Thanks for providing these useful calculators. Can you help me out with a calculator. I have booked a house and i intend to give it on rent. i had taken homeloan of 20 lakhs for 20 years. i want to complete the loan in about 12-15 years by channeling the rent into a corpus. Say i would receive Rs 5000 per month as rent. after two years it becomes Rs 120000 plus rent. shall i pay check Rs 100000 towards principal of loan. Or i have to grow it into a bigger corpus say Rs 1000000 and then pay the principal. i am not worried about rate of interest. rather i am interested to close the loan in 12 years. My crude calculations showed that if the rent increased 10% every year i can close the loan in 14 years. but it would be informative if it is in a calculator form of yours. Thanks in advance.
Dear Vignesh,
Thank you. First of all 10% rent increase per year may not be possible with the same tenant! Typically 5% is what you plan for. I have received many request for this kind of calculator. I will work on it and let you know.
Have you ever considered writing an ebook or guest authoring on other sites?
I have a blog based on the same subjects you discuss and would
love to have you share some stories/information. I know my subscribers would value your work.
If you're even remotely interested, feel free to shoot me an e mail.
Best New Year Gift.
Thanks Raj.
Great and shocked to see my CAGR is such a poor 4.5% only. Thanks for your hard work. Pl Keep do your service for a novice like me.
Thank you. RE CAGR depends on when you got the property, home loan duration etc.
A few things, can we also factor in things like the start date when the downpayments were payments were made, and the date from which EMI started. In my case I closed down my home loan in less than 7 years, overall tenure was 15 years. If we can factor in things like these, off course optional, then it will be helpful.
Regards
Sujit
Thanks for your inputs. Yes these are valid points which will impact the CAGR. Will try and implement them soon.
Hi Pattu,
Good job!
One thing I would like to clarify - In the cash flow sheet, the cell K7 is not linked to society charges in the returns calculator sheet. It is showing constant 5000. I think it should be linked.
Yes you are correct. Thank you for pointing this out. I have uploaded modified versions.
Great calculator. It would be a good idea to show the value of the property after each year as well. This will help to arrive at a ballpark figure in case one wants to sell the property. (Or adjust the 'annual increase in property value' to a more reasonable figure)
Thank you. Yes I understand. However, since the rate of appreciation is only nominal and an expectation, the actual value may differ. In any case this is easy enough to implement. Will do so.
I have now added the property value for each year next to the Cagr. Thank you.
Pattu Sir,
Could you make a calculator to find the annual return (ROI) for the plot purchased 12 yrs back?
Tks in advance.
Simply enter this formula in excel [(present value)/(purchase price +expenses)] ^(1/12)-1
If you got the property for 10 L (incl expenses) and it now worth 50L,
= (5000000/1000000)^(1/12)-1
Copy and paste this in excel and see how it works. You can then enter your own numbers.
http://freefincal.com/
Hi Puttu,
This is a very good & informative blog on Finance. I hope you can add more insights to what I am thinking. I am currently in a dilemma to go for a apartment or a plot. This is purely for investment purpose and I want to sell this after 10 years and invest in a bigger plot. Currently, I have some saving and want to invest in Realestate. I understand that I can get rental returns and also tax benefit from apartment (since this will be my 2nd house) but what I have learned studying the web is that plot usually gives more appreciation and returns (given the fact that legality needs to be checked). Can you please throw more lights on your thinking on this.
Thanks,
Rajeev
Sorry, I cannot. I am against RE as an investment.
Thanks Pattu for your feed back
25000 per month rent?
the same property after 30 years you are showing as appreciation from 50 lakhs to 800 odd is it practically possible?
surendra
Those are dummny nos. You can fill in what you like. If there is an inadqueacy or an error, let me know.
can you do all this on you tube video explaining step by step?
I am too lazy to do this. Will see what I can do.
This is too good. Amount of time and effort you must have put to make this excel calculator is remarkable.
As an NRI, I have few queries which may be simple but as a non-finance person, handicapped in complex excel calculations i hope you can address.
Query 1: Only source of Income
As a NRI if I have a property and rent is the only source of Income in India. Considering the final income value does not exceed Rs. 2,50,000 after deductions; then there will be no tax slab. In the excel sheet there is no option to make tax slab zero.
Query 2: Other sources of Income:
Considering I have other sources of income and fall in respectable tax bracket, as a NRI, i can claim full deduction on Interest paid towards loan. Can these be accommodated into excel sheet?
Thanks
Pradip
Hi,
The links dont work. Can you please let me know where to find the latest files? They look very interesting.
Thanks for doing this.
Amit.
The links are now available. Thanks.
Thank you guys!
I have a query on home loan.
If I do not take a home loan for my "second" house, do I have to still declare notional rent while filing tax?
Notional rent is while the house is unoccupied and is independent of how you got the house. So yes I think you should declare it.
Thanks for the quick response.
Dear Pattu,
The IRR/ XIRR / CAGR do not factor in the potential opportunity cost of own funds deployed. I did not see a placeholder to enter if not year wise, atleast one % number for ROI for my deployed funds. Does this not give a wrong impression of the CAGR should I decide at appropriate sell prices?
Overall, fantastic calculator. Great education for lay people like me. Appreciate all your efforts in going against the tide to demystify the complexities of the finance world.
Regards,
Bashyam
Dear Pattu,
Maybe in the rush to address hundreds of queries, my July query has not received a response from you. Please review and let me know what you think.
Regards,
Bashyam
I did not include it as I it need not be calculated. Suppose I have 30L and wanted to compare the XIRR from a potential property purchase, I can has some return number (from investment instead of buying) in mind for 3Y, 5Y 10% etc. and compare with RE XIRR.
Dear Pattu,
Maybe I'm misunderstanding here- please help me understand better. Say one has already invested in property with own funds 10 lacs and home loan 40 lacs. So, at end of year 1, 2,3 etc the return net of expenses, tax/rental savings- to know the break-even price at the end of various years, then make an informed decision on whether / when to sell- that's what I was keen on knowing. So, if RE calculator includes only the home loan part then my return calculation is over-stated - right? Or am I missing something.
Of course, it's another question whether one must base one's sale based on break-even price or 1% over etc, given that market price may not support my personal break-even but in RE returns, should not both own funds' opp cost and home loan funds interest cost (net of IT, rental savings) etc be a part?
Awaiting your response.
Regards,
Bashyam