When a lender gives you only the loan amount, the EMI and the number of months, RATE works out the interest rate hidden in those numbers. The formula is =RATE(months, -EMI, loan amount). It returns the monthly rate, so multiply by 12 for a yearly figure. A second step, subtracting the processing fee from the amount you actually receive, shows the rate once that cost is included.
How do I set up the sheet?
Put the quote in four cells: B1 Loan amount, B2 EMI, B3 Tenure in months, B4 Processing fee. In B6 write:
=RATE(B3,-B2,B1)
Format B6 as a percentage with two decimals. This is the monthly rate. In B7 write =B6*12 for the nominal annual rate. The EMI goes in negative because it is an outflow, and the loan positive because it is an inflow. If you swap the signs, you will get #NUM!.
What does a worked example give?
Take a quote of ₹3,00,000 with an EMI of ₹9,800 for 36 months. The numbers are invented. The total payable is 9,800 x 36 = ₹3,52,800, so the interest is ₹52,800.
| Step | Formula | Result |
|---|---|---|
| Monthly rate | =RATE(36,-9800,300000) | about 0.904% |
| Annual rate (x 12) | =B6*12 | about 10.85% |
| Effective yearly rate | =(1+B6)^12-1 | about 11.40% |
So the rate behind that EMI is about 10.85% a year on a reducing balance. If the lender told you 10.5%, the gap is a sign to ask for the full schedule.
How do I include the processing fee?
If the lender deducts a fee of ₹4,500 from the amount paid out, you receive ₹2,95,500 but still repay the EMIs on ₹3,00,000. Use the money you actually receive as the present value:
=RATE(B3,-B2,B1-B4)*12
That gives a monthly rate of about 0.991%, or roughly 11.89% a year, with an effective yearly figure around 12.57%. The fee adds about one percentage point to the cost in this example. If other charges are also taken from the loan, subtract them too. If the fee is paid separately from your own pocket, treat it as an extra outflow at the start; subtracting it from the loan amount gives the same comparison.
Why does RATE sometimes return #NUM!?
- The signs are the same: the loan and the EMI must have opposite signs.
- The EMI is lower than the interest alone, so the loan could never be paid off.
- The tenure is in years while the EMI is monthly. Convert to months.
- Excel could not converge. Add a guess as the sixth argument, for example =RATE(B3,-B2,B1,0,0,0.01), which starts the search at 1% a month.
How do I compare two offers?
Make two columns with the same layout. Offer A: ₹3,00,000, EMI ₹9,800, 36 months, fee ₹4,500. Offer B: the same loan, EMI ₹9,950 and fee ₹0. The rate for B is =RATE(36,-9950,300000)*12, which comes to about 11.90% a year, almost the same as A's 11.89%, even though A has the lower EMI. A lower EMI is not always cheaper once fees are counted. Try your own figures, since changing the tenure or fee changes the order.
What if the quote has other extras?
Some loans add an insurance premium to the amount or charge for documentation. Ask for a written breakup of everything you pay and receive, then reflect each item: charges deducted at the start reduce the loan amount in the formula, and charges added each month increase the EMI. If something is unclear, ask the lender. This is a check on the numbers, not a recommendation of any lender.
Keep the records
Once you accept an offer, save the sanction letter and schedule. When EMIs begin to appear on your statements, SpendixAI can keep them under their own category so you can compare what you actually pay with the schedule.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

