A flat rate charges interest on the full original amount for the whole tenure, even though you repay part of it every month. A reducing balance rate charges interest only on what is still outstanding. The same quoted percentage therefore costs far more under a flat rate, and Excel's RATE function shows by how much.
How is a flat-rate EMI worked out?
The method is simple. Interest for the whole loan is amount x annual rate x years. Add it to the amount and divide by the number of months. In a sheet, put B1 Loan amount, B2 Flat rate per year, B3 Months. Then:
- Total interest: =B1*B2*B3/12
- EMI: =(B1+B1*B2*B3/12)/B3
Take a loan of ₹1,00,000 at a flat 12% for 24 months. These are test numbers. Interest is 1,00,000 x 12% x 2 = ₹24,000, the total payable is ₹1,24,000, and the EMI is 1,24,000 / 24 = ₹5,166.67.
What would 12% on a reducing balance look like?
Use PMT: =PMT(0.12/12,24,-100000). The EMI is about ₹4,707.35 and the total interest is 4,707.35 x 24 - 1,00,000 = about ₹12,976. So the flat quote charges roughly ₹24,000 where a reducing balance 12% charges about ₹13,000, nearly twice as much, for the same headline number.
The reason is that under the flat method you keep paying interest on the full ₹1,00,000 even in month 23, when you owe only about ₹10,000.
How do I find the effective rate with RATE?
RATE reverses the PMT calculation. Give it the number of months, the EMI as a negative number and the loan as a positive number:
=RATE(24,-5166.67,100000)*12
The function returns the monthly rate, about 1.80%. Multiplying by 12 gives roughly 21.57% a year, which is the reducing balance rate that produces the same EMI. So a flat 12% is equal to roughly 21.6% on the more common basis.
| Quote | Basis | EMI | Total interest | Annual rate on a reducing balance |
|---|---|---|---|---|
| 12% flat | Original amount | ₹5,166.67 | ₹24,000 | about 21.6% |
| 12% reducing | Outstanding balance | ₹4,707.35 | about ₹12,976 | 12% |
Can I make the sheet general?
Yes. Put the flat quote in B2, then compute the EMI in B4, the monthly rate in B5 with =RATE(B3,-B4,B1) and the annual figure in B6 with =B5*12. Create a second column for a competing offer quoted on a reducing balance, where B4 uses PMT and B6 simply reads the quoted rate. Seeing both annual figures side by side makes the comparison honest.
Which questions should I ask the lender?
- Is the quoted rate flat or reducing balance?
- What are the processing fee and any other charges, and are they deducted from the amount I receive?
- What is the EMI, the number of instalments and the total I will pay?
- Can I get the repayment schedule in writing?
- Are there charges for part payment or early closure?
If a quote leaves out the EMI or the total payable, work them out yourself with the formulas above. A quick check: total payable divided by months should match the EMI the lender states.
How do I check a quote in practice?
Ask for three figures: the amount you will receive, the EMI and the number of instalments. Work out the flat-style rate with =(EMI*months-Loan)/Loan/(months/12), which for the example is (1,24,000 - 1,00,000) / 1,00,000 / 2 = 12%. If that matches the number the lender quoted, the offer is flat. Then run RATE to see the reducing balance equivalent. The two steps take a minute and make the real cost visible before you sign anything.
What next?
Once you take a loan, the EMIs repeat on your statement every month. If you track them in SpendixAI, you can see the total paid across the loan and compare it with the sheet's total payable.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

