EMI Calculator Excel Model can be used to calculate EMI for Home Loan or any other loan. For better understanding, we have provided visual charts for Loan Repayment Break-up and Loan Repayment Schedule. Some more cool stuff is provided below. Checkout EMI calculation formula, examples and some smart tricks that you can use to save your hard earned money rather than giving it to the Bank.
Download Calculator
You can download this EMI Calculator for offline use. Like, Tweet or +1 to show Excel Worksheet link.
[sociallocker]
Excel EMI Calculator with Pre-Payment Option
[/sociallocker]
What is EMI?
EMI or Equated Monthly Installment is the monthly amount payable to the Bank or any other financing institution till the Home Loan or any other loan is paid back in full.
EMI consists of the Interest on the outstanding loan amount and part of the Principal to be repaid. During initial loan period, a large portion of each payment is devoted to interest. With passage of time, larger portions pay down the principal.
Since, EMI is a fixed amount and does not change every month, it becomes easier to plan for monthly expenses.
How to use EMI Calculator Excel Model?
Let us take an example of Rahul Vamsi, 38, a Project Manager in an IT company. Rahul wants to purchase a house worth Rs. 70 lakhs in Noida. He has saved Rs. 20 lakhs for downpayment and wants to take a home loan for the balance Rs. 50 lakhs. He wants to know the EMI amount for the loan and wants to check the monthly and yearly loan repayment schedule.
Let us use our EMI Calculator Excel Model for Rahul’s case.
- Enter Loan Amount in rupees in the first yellow cell i.e. 50,00,000.
- Enter the Loan Interest rate per annum in percentage, say 11%.
- Enter Loan Tenure or Loan period in years, say 20.
That is all you have to do. The EMI Calculator will not only compute the EMI but also give you visual charts displaying Break-up of Total Payment and Loan Amortization. You can also check your Loan Repayment Schedule Year wise and Month wise. This EMI Calculator Excel Model can be used for loan tenure of upto 30 years. You can download and save this EMI Calculator Excel Model for offline use.
You can check out the following in Yearly and Monthly Loan Repayment Schedule:
Principal (P) – The Principal amount to be repaid during the year/ month.
Interest (I) – The Interest amount to be repaid during the year/ month.
Total Payment (P+I) – Total amount (Principal + Interest) to be repaid during the month/ year.
Principal Outstanding – Outstanding Loan Amount Balance at the end of the year/ month.
Cumulative Interest – Total Interest paid/ payable upto that year/ month.
Cumulative Principal – Total Principal paid/ payable upto that year/ month
In above example, Rahul will have to pay an EMI of Rs. 51,609 every month for 20 years unless he prepays the loan in between. Please note that over 20 years, Rahul will be paying an interest amount of Rs. 73,86,261 which is almost 50% more than the principal amount that he borrowed.
Want to know how you can save some of this interest amount? Keep reading.
5 Smart Tricks to save Money
If you pay attention to details, you can save some quick bucks with minimum effort. Here is how:
- Lower Interest Rate – The EMI and total interest outgo is highly sensitive to interest rate. In
above example, if Rahul gets a better deal at just half a percentage lower interest rate i.e. @10.5%, the total interest payable becomes Rs. 69,80,559 and he can save a cool over Rs. 4 lakhs over 20 years. So, you must go with the lowest interest rate possible. There is hardly any extra value that a particular Bank gives over others to justify a higher interest rate. They are all in the business of money. - Lower Tenure & Higher EMI – If you can afford a slightly higher EMI, you can always reduce your tenure. For eg. if Rahul reduces his tenure by 5 years, his EMI increases to Rs. 56,830, an increase of about 10% while savings in interest amount are really significant. He will be paying Rs. 52,29,372 in 15 years vs Rs.73,86,261 in 20 years, a huge saving of over Rs. 21 lakhs.
- Higher Down Payment – If you can afford a higher Down Payment, there is no point in keeping that extra money parked in a Bank Deposit or any other investment vehicle where returns are less than your home loan interest rate. Also, remember that interest on most of the savings including Bank Fixed Deposits are taxable so if you are in highest tax bracket of 30%, you effectively earn a post-tax return of only 6% on a FD @ 9%. By paying off higher Down Payment, you are eventually gaining 11% on your money compared to post-tax return of 6% in a Bank FD. In our example, if Rahul has Rs. 10 lakhs in Bank deposits @9%, by paying a higher Down Payment he saves over Rs. 16 lakhs over 20 years. Only equity market returns can beat the loan interest rates over long term but that comes at a much higher risk and requires personal involvement. Unless you have enough knowledge and time to invest directly in shares, it is better to pay higher Down Payment.
- Loan Prepayment – Let us consider two cases here.
- Alternative Investment – Now that we talked about Shares and Alternative investments, if you feel that you can beat your loan interest rate with higher returns, you can consider reducing your EMI to a minimum and investing the surplus in Alternative Investment like Share Market. After sometime, you can pre-pay your loan with your extra returns. For example, if Rahul can make a 20% post-tax return from his alternative investment, his Rs. 10 lakhs will become Rs. 61,91,736 in 10 years vs Rs. 17,90,848 in Bank FD over 10 years, a gain of Rs. 44,00,889 which can be easily used to pre-pay the outstanding loan of Rs. 37,46,600 at the end of 10 years. Please note that by the end of 10 years, Rahul would have anyway paid cumulative interest of Rs. 49,39,730 which is about 67% (2/3) of total interest payable over 20 years. A word of caution – Savings of Rs. 24 lakhs in interest in this example may still look attractive to some. However in real world, more than 90% of the investors cannot make 20% post-tax returns year on year over long-term. Hence, this strategy is recommended only for people who have knowledge and dedication to invest in Share Markets or other alternative markets. What if your 20% positive returns become negative 20% returns over next 10 years? All your investments will go for a toss. Unless you have a very high risk appetite, you are better off paying higher Down Payment than using this strategy.
- Salary Increase – Salary increases can vary by a large amount, hence, it is difficult to compare numbers in this case. In the same example above if Rahul’s Salary increases and he could save Rs. 40 lakhs over next 10 years, he should definitely pre-pay his loan. And why even wait for that long? You can pre-pay your loan every year because you are gaining an extra 5% every year over Bank Fixed Deposit rates.
- Loan Refinance – Not many people know that Banks have different interest rates for existing and new customers. Once you have a loan account with a Bank and unless you have opted for fixed interest rates, you do not have control over your interest rates. On the contrary, Banks offer attractive interest rates to new customers. You can probably use it to your advantage. Though you cannot negotiate with your own Bank, another Bank may offer you a much attractive interest rate on refinancing your loan. In Rahul’s case, we saw that an interest rate change of 0.5% saved more than Rs. 4 lakhs over 20 years for him. The savings will be even higher if you can reduce your interest rate by 1% or more. Please note that you have to factor in the one time loan refinancing processing fee which could range from 0.5% to 2% of the loan amount.
These were some of the examples on how you could save your interest amount. Now, you can play with the EMI Calculator Excel Model to evaluate various scenarios for your situation. Don’t fall prey to the aggressive marketing by a Bank or to some marketing gimmicks like lower processing fee, cheque collection service etc. You can save lakhs of Rupees by doing very little work yourself.
If you want to understand the EMI Calculation Formula, continue reading.
How is EMI calculated?
Mathematical EMI Calculation Formula
Where,
E is EMI
P is Principal Loan Amount (eg. Rs. 50,00,000 in Rahul’s Case)
r is rate of interest calculated on monthly basis. (i.e., r = Rate of Annual interest/12/100. If rate of interest is 11% per annum, then r = 11/12/100=0.009167)
n is loan term / tenure / duration in number of months (eg. 240 months for 20 years loan tenure)
Now, EMI for Rahul can be derived as follows.
EMI = Rs. 50,00,000 X 0.009167 X (1+0.009167)^240/ [(1+0.009167)^240 – 1]
= Rs. 51,609
Computing EMI using the above EMI calculation formula is cumbersome and error prone. Our EMI Calculator Excel Model automates this calculation for you. You can download and save this Excel model for offline use.
To calculate EMI on the fly, you can use the below EMI Calculation Formula in Excel.
Excel EMI Calculation Formula
In case you want to quickly calculate EMI, you can do that by using PMT function in Microsoft Excel and in Google Spreadsheets.
Here is the Excel EMI Calculation Formula:
[box type=”note” ]=PMT(rate, nper, pv, fv, type)[/box]
Where,
Rate is the monthly interest rate for the loan
Nper is the total number of payments for the loan i.e. loan period in months
PV is the present value. Enter Loan amount with a negative sign as PV, else EMI will be given as a negative.
FV (Optional) is the future value. Since, our loan amount will eventually become 0, you can either enter 0 as FV or leave it blank, in which case the function automatically assumes it to be 0.
Type (Optional) is the number 0 (zero) or 1 and indicates when payments are due. 0 indicates that payments (EMI) are due at the end of the period (month) and 1 indicates that payments (EMI) are due at the beginning of the period (month). If you leave it blank, the function assumes it to be 0.
EMI for Rahul can be calculated as follows:
=PMT(0.11/12, 240, -5000000, 0, 0) or
=PMT(0.11/12, 240, -5000000)
Both will deliver the same result, EMI = Rs. 51,609
Over to You
We need your love! Like, Share and Rate the EMI Calculator if you found it useful.
Have something to say or ask? Please comment below.