Nov 22, 2009

4-in-1 Loan Calculator in Excel

Photo by spackletoe
After Tax Calculator, HRA Calculator and PPF Calculator, The Money Quest now brings you 4-in-1 Loan Calculator in excel sheet which includes EMI Calculator, Interest Rate Calculator, Loan Affordability Calculator and Loan Tenure Calculator.

Basically, for doing any loan calculations, four variables are involved: Loan Amount, rate of interest, Loan tenure (period) and EMI (Equated Monthly Installment). Given any 3 parameters, we can arrive at the fourth one. Thus, we can have 4 different Loan Calculators:

Loan Calculators:
1. Loan EMI Calculator
2. Interest Rate Calculator
3. Loan Amount / Affordability Calculator
4. Loan Tenure Calculator

The Calculators are multi-purpose and can be used for any kind of loan. For example, Loan EMI Calculator can be used whether you want to calculate EMI of home loan, EMI of personal loan or EMI of Car loan. Thus, the EMI Calculator serves the purpose of

1. Home Loan EMI Calculator
2. Personal Loan EMI Calculator
3. Car Loan EMI Calculator

To compute Equated Monthly Installment (EMI), you’ve to enter following loan details in the excel sheet:

a. Loan Amount
b. Tenure (in months)
c. Rate of Interest (in %)

Similarly, to calculate rate of interest, you’ll have to enter EMI, Loan amount and period of loan.

Furthermore, Calculators are universally applicable to loans from all banks (e.g., ICICI Bank, State Bank of India [SBI] and IDBI Bank) and housing finance companies (e.g., HDFC, Can Fin Homes [CFHL] and LIC Housing Finance).

Purpose: Different Ways in which Loan Calculators can be put to use

EMI Calculator: Calculate EMI based on loan amount & repayment period
1. The EMI (Equal Monthly Installment) Calculator helps you calculate how much you need to pay every month towards your loan repayment based on the loan amount, interest rate and tenure / period of loan. Put another way EMI is the fixed amount you pay every month towards principal repayment and interest payment.

2. You can use the EMI Calculator to check the EMIs for different periods to decide which would be most suitable for you.

3. You can also use EMI Calculator to find how the change in interest rate affects the EMI.

4. EMI Calculator can also help you find out the effect of change in loan amount on the EMI.

5. EMI Calculator also shows you the total interest to be paid during the entire loan tenure.

6. This excel Calculator can be used to calculate EMI of all types of loans such as EMI of home loan, EMI of personal loan and EMI of car loan.


Interest Rate Calculator: Know the interest rate for a given EMI
1. Interest rate Calculator can be used to find out rate of interest charged for a given EMI (Equated Monthly Installment). It can help you find out the interest rate being charged on the loan if the lender gives you an EMI quote without mentioning the interest rate.

2. It can also help you make sure that the loan EMI you’ve been asked to pay is in fact based on the interest rate quoted to you. In other words, you can get the assurance that interest rate quoted to you is in fact the monthly reducing balance rate and not the flat rate of interest.

A lot many lenders (particularly in case of personal loans and car loans) try to fool borrowers by quoting flat rate of interest (ostensible rate) which is considerably lower than the actual rate of interest being charged (i.e., monthly reducing balance rate).

In case the interest rate as per this excel Calculator doesn’t tally with what you’ve been told, you can ask the lender for clarification.


Loan Affordability Calculator: Find out Loan Affordability based on EMI & Tenure
1. Loan Affordability Calculator will let you calculate the maximum amount you can borrow based on the EMI you would like to pay and the period for which you would like to avail the loan (given the rate of interest).

2. You can see the effect of increasing /decreasing EMI on the loan amount. Similarly, by increasing or decreasing the repayment period (loan tenure) with or without changing the EMI, you can see the effect on the maximum amount you can borrow.

3. It can also help you find your loan affordability (& not eligibility) based upon your regular monthly income / salary income. Suppose your monthly income is Rs 1,00,000 p.m. and you think that you can manage to pay, say, 40% (i.e., Rs 40,000) of it as monthly loan installment without affecting your finances, then just enter Rs 40,000 as your EMI and you will know how much maximum amount of loan you can afford. If it comes out to be less than the loan amount you require, you can increase the loan tenure and arrive at an optimum combination of loan amount and EMI.


Loan Tenure Calculator: Find out Repayment period based on Loan Amounts & EMI
1. Loan Tenure Calculator will help you find out the tenure (period of the loan) based on an amount you would like to borrow, and EMI you can afford (for a given interest rate).

2. It can help you arrive at the repayment period for different loan amounts, for different EMIs and available at different rates of interest. By changing the loan amount and / or the EMI and / or the rate of interest, you can arrive at the optimum loan tenure you would like to go for.
To sum up, this loan calculator can help you plan house loan, personal loan and car loan requirement and arrive at the optimum / best combination of loan amount (borrowing required), EMI (repayment installment you can afford) and tenure (repayment period) for a given rate of interest.



Assumptions:
Loan Calculators are based on the following assumptions:

1. EMI is calculated based on monthly reducing balance. In other words, loan calculators are not applicable if interest is charged on daily reducing balance or annual reducing balance.

2. Processing & other charges which may applicable as per the rules of banks and other lending institutions are not taken into account.

3. EMI Calculator calculates EMI in arrears (when you pay EMI at the end of the month) and not EMI in advance (when you pay EMI at the start of the loan followed by beginning of each month).


Shortly, I’ll publish Interest Rate Converter (Converting flat rate of interest into reducing balance interest rate & vice-versa), Loan Eligibility Calculator (the maximum loan you can avail based on your income and existing loans) and Effective Interest rate calculator (IRR of your loan based on various associated loan costs).

If you encounter any difficulty or error while using this excel based loan calculator, just write a comment.

11 comments:

  1. i am stunned Fisher ! As per your calculator .. my Rate of Interest is coming to 13.45% whereas i was told by ICICI that i am paying 8.75 % (floating)... i have takena home loan of 12 lacs for 10 year at 8.75 %..... am i being fooled by icici u mean ?

    ReplyDelete
  2. Samant: I can’t say anything until I know all the facts.

    ReplyDelete
  3. hi Fisher,
    i have taken a home loan of 12 lacs for 10 year at 8.75 % and my EMI as per ICICI is 15,200 per month, what other details would you need. I got this loan in Sept 2009.

    THanks
    Samant

    ReplyDelete
  4. Samant: Please recalculate because as per calculator interest rate works out to be 9% (Loan – 12 lakh; Period – 10 yrs; EMI – 15,200).

    ReplyDelete
  5. Samant: I’ve rechecked; it seems that there is some bug in the Zoho Sheet. While my offline excel is showing the correct results, the online Zoho sheet is showing different result with the same formula.
    Let me first see where exactly the problem lies, then I’ll get back to you. Any way, thanks for pointing it out.

    ReplyDelete
  6. Samant: It seems to be due to a zoho software bug.

    The original zoho sheet is giving the right answer, but the embedded version is behaving strangely. Let me keep a tab on it for next few days and if the error doesn’t self-correct, take up the matter with Zoho. Sorry for the inconvenience and thanks again for bringing it to my notice.

    ReplyDelete
  7. Hi Fisher, I am Ramesh from the Zoho Sheet team. Thanks for the nice calculator you have developed using Zoho Sheet. I tried out the calculator embedded in this spreadsheet and it does seem to work fine. Do you still face this issue now? I believe one of our servers would have been busy at that point of time due to which one of the entries would have timed out. We are looking into this and will make sure such issues don't arise.

    ReplyDelete
  8. Hi Ramesh,

    You’re right, now the calculator is working fine.

    Thanks for the feedback.

    ReplyDelete
  9. Hey Fisher.. firstly congrats on another state-of-art work from you buddy!! Well.. am just getting more demanding now :-)
    Here is what I'm looking for.. I want to know what happens to the total repayment figure, tenure and overall interest paid if I make some lumpsum prepayments apart from my EMI (for calculation purpose, this might need to be considered as periodic, say annual, activity after n years of commencement of loan). This will just tell how best can one offload the liability of loan before scehduled tenure completion without overloading himself too much.
    Looking forward to see this capability soon. Thanks !

    ReplyDelete
  10. Of course the best thing about them also, is the fact that they only take a couple of minutes to find out just how much you can expect to pay for the loan you need. What could possibly be more convenient than that?

    ReplyDelete
  11. hey fisher, i wanted to know how can i calculate the interest that the bank is offering me on reducing balance, when i take a loan of Rs. 1000000 and the emi for the first 9 months is 84000 and the next nine months is 43400 that is the loan is for 18 months...please send me the details on kaushikejriwal@gmail.com
    thanks.

    ReplyDelete

You’re welcome to post a comment if you’d like to air your views, or if you’ve any further question to ask, but please stick to the topic and don’t forget to write your name.