An EMI sheet in Excel takes one formula. What most of them leave out is the processing fee, which comes off the money you receive and quietly raises the rate you are really paying. This free sheet works out the EMI and the full schedule, then the true yearly rate after the fee, and lets you put three loan offers side by side.
⬇ Download: EMI calculator in Excel (.xlsx, free)What's in the sheet
- EMI tab: loan amount, rate, tenure in months and processing fee, then the EMI, total interest, fee with 18% GST, total cost and the true yearly rate (APR).
- A month-by-month schedule for up to 30 years: opening balance, interest, principal and closing balance.
- Compare tab: three offers with different rates, tenures and fees, worked out the same way.
The formulas, so you can check them
EMI = PMT(rate/12, months, −loan). In a cell: =PMT(11%/12, 36, -500000) gives ₹16,369 for ₹5 lakh at 11% over 3 years. Over 36 months you pay ₹89,297 in interest. Each month's interest is the opening balance × rate ÷ 12, and the rest of the EMI repays principal, which is why the early EMIs are mostly interest.
The true rate uses RATE(): =RATE(months, −EMI, loan − fee)×12. You repay EMIs on the full loan but receive the loan minus the fee, so the real rate is higher than the quoted one.
Why the lowest rate isn't always the cheapest loan
Take ₹5 lakh for 36 months. Offer A: 10.5% with a 3% processing fee. Offer B: 11% with a 1% fee. A has the lower EMI, but after the fee and GST its true rate is about 13.0% a year, against about 11.8% for B, and its total cost is about ₹7,500 higher. The compare tab does this for any three offers.
Frequently asked questions
- What is the EMI formula in Excel?
- =PMT(annual rate/12, number of months, -loan amount). For ₹5,00,000 at 11% for 36 months, =PMT(11%/12,36,-500000) returns ₹16,369.
- How do I make an EMI schedule in Excel?
- List the months down a column. Interest = opening balance × rate/12, principal = EMI − interest, closing balance = opening − principal, and the next month opens at that closing balance. The free sheet on this page has it for up to 360 months.
- Does the processing fee change the interest rate?
- It doesn't change the rate on paper, but it raises what you really pay. Use =RATE(months, -EMI, loan - fee)*12 to see the true yearly rate. On a 3-year loan at 10.5%, a 3% fee plus GST takes the true rate to about 13%.
- Is GST charged on the loan processing fee?
- Yes, 18% GST applies to processing fees. It is not charged on the interest part of your EMI.