How to Calculate Home Loan EMI in Excel with PMT (and Make a Month-by-Month Repayment Schedule)

By Ravindra Bagale · Last updated: 17 October 2026

Download the template: home-loan-emi-calculator-amortization-template.xlsx: example data with real formulas. Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and everything recalculates.

Made-up data: every name, amount, shop and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The example

New functions in one line each:

• PMT(rate, nper, pv) gives the fixed payment for each period of a loan.
• IPMT(rate, per, nper, pv) gives the interest part of payment number per.
• PPMT(rate, per, nper, pv) gives the principal part of payment number per. IPMT + PPMT = PMT.

Use the monthly rate (yearly rate / 12) and the number of months.

Amir's loan (made-up): ₹25,00,000, 8.5% a year, 20 years.

• Monthly rate = 8.5% / 12 = 0.7083%; months = 20 × 12 = 240.
• EMI = ₹21,695.58.
• Total paid = 21,695.58 × 240 = ₹52,06,939, so the total interest is ₹27,06,939, more than the loan itself.
• EMI 1: interest = 25,00,000 × 0.7083% = ₹17,708.33; principal = 21,695.58 − 17,708.33 = ₹3,987.25.
• From EMI number 143 (year 12) the principal part becomes bigger than the interest part.
• At 9.0% instead of 8.5% the EMI becomes ₹22,493.15: ₹797.57 more every month.

2. Build it step by step

Step 1. Type the loan details

1. In A4:A6 type Loan amount (₹), Interest rate (yearly), Tenure (years).
2. In B4 type 2500000, in B5 8.5% and in B6 20 (yellow input cells).

Step 2. EMI with PMT

1. In B8 type =PMT(B5/12,B6*12,-B4): ₹21,695.58.
2. The minus before B4 makes the answer positive. Without it Excel shows −21,695.58, because money you pay out is negative.

Step 3. Totals

1. In B9 type =B6*12: 240 EMIs.
2. In B10 type =B8*B9: total paid ₹52,06,939.
3. In B11 type =B10-B4: total interest ₹27,06,939.
4. In B12 type =PMT((B5+0.5%)/12,B6*12,-B4): ₹22,493.15 if the rate rises by 0.5%.

Step 4. Interest and principal of one EMI

1. In B13 type =IPMT(B5/12,1,B6*12,-B4): ₹17,708.33 interest in EMI 1.
2. In B14 type =PPMT(B5/12,1,B6*12,-B4): ₹3,987.25 principal in EMI 1.

Step 5. The schedule (columns D to I)

1. In D4:I4 type EMI no., Opening balance, EMI, Interest, Principal, Closing balance.
2. In D5 type 1 and in D6 =D5+1.
3. In E5 type =$B$4; in E6 =I5 (last month's closing balance).
4. In F5 type =$B$8.
5. In G5 type =IPMT($B$5/12,D5,$B$9,-$B$4) and in H5 =PPMT($B$5/12,D5,$B$9,-$B$4).
6. In I5 type =E5-H5.
7. Copy row 6 down to row 244. The closing balance in I244 is ₹0 (absolute references keep $B$5 fixed).

Step 6. Year-wise split and chart

1. In A18:A37 type years 1 to 20.
2. In B18 type =SUMIFS($G$5:$G$244,$D$5:$D$244,">"&(A18-1)*12,$D$5:$D$244,"<="&A18*12) for interest, and the same on column H in C18 for principal. Copy down.
3. Year 1: interest ₹2,10,591, principal ₹49,756. Year 20: interest ₹11,601, principal ₹2,48,746.
4. Select A17:C37 > Insert > Stacked Column chart (column charts).

Step 7. Afford-check with Goal Seek

1. To find the loan for an EMI of ₹20,000: Data > What-If Analysis > Goal Seek, Set cell B8, To value 20000, By changing B4 (Goal Seek).
2. Undo (Ctrl+Z) to go back to ₹25 lakh.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in the file.

4. Common mistakes

1. Using the yearly rate. =PMT(8.5%,240,-2500000) treats 8.5% as a monthly rate and gives a huge EMI. Always divide the rate by 12.
2. Using years instead of months. nper must be 240 for 20 years of monthly EMIs, not 20.
3. Getting a negative EMI. PMT returns money going out as negative. Put a minus before the loan amount (-B4), or before PMT.
4. Forgetting $ in the schedule. Without $B$5 the rate cell moves as you copy down and later rows break.
5. Comparing with the bank to the rupee. Banks may round, charge from the disbursement date or add fees. The sheet gives the standard reducing-balance EMI; use it to compare offers.

5. More real examples

Example 1: Car loan. ₹8,00,000 at 9.5% for 5 years: change B4:B6 to 800000, 9.5%, 5. The schedule after row 64 can be cleared.

Example 2: Personal loan comparison. Copy B4:B8 for two offers side by side (two rates, two tenures) and compare total interest in B11.

Example 3: Prepayment idea. After a bonus, lower the loan amount in a copy of the sheet to see how the interest drops. Banks may reduce the tenure instead of the EMI, so ask them.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

What is the Excel formula for EMI?

=PMT(rate/12, years*12, -loan). For ₹25 lakh at 8.5% for 20 years: =PMT(8.5%/12,240,-2500000) = ₹21,695.58.

Why does PMT show a negative number?

Excel shows money you pay as negative. Use -loan inside PMT, or write =-PMT(...).

How do I see interest and principal for each month?

IPMT gives the interest part and PPMT the principal part of a chosen EMI number; together they equal the EMI.

Does this work for a car or personal loan?

Yes. Any loan with a fixed rate and equal monthly EMIs works the same way.

How much EMI is safe?

Many lenders look at what share of take-home pay goes to EMIs. Keep space in your home budget for savings too.

7. Quick quiz

Ten questions on this page. Sign in to save your score.