calculate-loan

01 Loan payments

EMI Repayment Schedule Excel: Formula and Example

Calculator No. 01-15983 · Updated · Formula and assumptions shown below

Monthly EMI 405.53
Total interest
4,331.80
= Monthly emi × loan term × 12 − loan amount
Loan amount
20000
Input
Interest rate (APR)
8 %
Input
You enter
Loan amount, Interest rate (APR) (%), Loan term (years)
You get
Monthly EMI, Total interest
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

An EMI repayment schedule in Excel shows each monthly payment split between interest and principal, along with the loan balance after each payment. To build one, enter the loan amount, annual interest rate, and term; the calculator below estimates the monthly payment and total interest, while the steps explain how to create the full schedule in a spreadsheet.

Formula

The fixed monthly EMI is calculated using the standard amortizing-loan formula:

EMI = P × r × (1 + r)n ÷ ((1 + r)n − 1)

  • P is the starting principal, in your currency.
  • r is the monthly interest rate as a decimal: annual APR ÷ 12 ÷ 100.
  • n is the number of monthly payments: loan term in years × 12.
  • EMI is the scheduled monthly payment, in your currency.

The calculator rounds the payment to two decimal places. Its total-interest estimate is rounded EMI × number of payments − principal. That is a simplified estimate: a real lender may calculate interest using daily balances, different payment dates, or other contract terms.

Worked Example

Using the calculator’s default inputs—principal 20,000 in your currency, APR 8%, and a five-year term:

  1. Monthly rate: 8 ÷ 12 ÷ 100 = 0.0066667.
  2. Number of payments: 5 × 12 = 60.
  3. Apply the formula: 20,000 × 0.0066667 × (1.0066667)60 ÷ ((1.0066667)60 − 1).
  4. The monthly EMI rounds to 405.53 in your currency.
  5. Estimated total interest: 405.53 × 60 − 20,000 = 4,331.80 in your currency.

These figures use the rounded monthly payment for the total, as the calculator does. A final payment can differ slightly if the lender adjusts it to clear a remaining balance.

Assumptions

  • The interest rate is fixed for the full term.
  • Payments are made monthly at the end of each payment period.
  • Interest compounds monthly at APR divided by 12.
  • Fees, taxes, insurance, PMI, escrow, and payment-date variations are not included.

Build the Repayment Schedule in Excel

Excel can create the detailed month-by-month table that the headline calculation does not display. Put the loan inputs in separate cells—for example, principal in B1, APR as a percentage in B2, and term in years in B3. Label cells clearly and format the rate as a percentage. The Excel PMT function can calculate the payment; with those cell references, use =-PMT(B2/12,B3*12,B1). The minus sign makes the payment display as a positive amount under Excel’s cash-flow sign convention.

Create columns for payment number, opening balance, payment, interest, principal repaid, and closing balance. In the first schedule row, use these formulas, replacing row numbers as needed:

  • Payment number: 1; increase by 1 on each following row.
  • Opening balance: the original principal for the first row; thereafter, the previous row’s closing balance.
  • Payment: the fixed EMI calculated with PMT.
  • Interest: opening balance × APR ÷ 12.
  • Principal repaid: payment − interest.
  • Closing balance: opening balance − principal repaid.

Fill the formulas down for the full number of payments. The schedule should approach a zero balance at the end, subject to rounding. If you round interest and principal to cents in every row, the final row may leave a small remainder; you can adjust the last payment to the amount actually owed, rather than treating a negative balance as an extra credit.

How to Read and Check the Schedule

At the start of a standard fixed-rate loan, the balance is largest, so the interest portion of each payment is generally larger. As principal is repaid, the balance falls; consequently, the interest portion tends to shrink and more of the same payment goes toward principal. The payment itself stays level only if the rate, payment frequency, and contractual payment remain unchanged.

Useful checks include confirming that the first row’s opening balance equals the principal, that interest is calculated on the opening balance, and that principal repaid plus interest equals the payment. The closing balance should equal opening balance minus principal repaid. The sum of scheduled payments less the original principal should reconcile approximately to total interest, allowing for rounding and any adjusted final payment.

Common Excel Errors and Limits

  • Entering 8 instead of 8%: Excel may interpret 8 as 800%. Enter 8% or 0.08 when a formula expects a decimal rate.
  • Using an annual rate as a monthly rate: For monthly payments, divide an annual percentage rate by 12. If the lender specifies a different rate convention, use the contract’s method.
  • Mixing months and years: A five-year term means 60 monthly payments, not five.
  • Rounding too early: Displaying two decimals is not the same as rounding every intermediate calculation. Rounding each row can create a small end-balance difference.
  • Assuming every loan has a fixed EMI: Variable rates, skipped or irregular payments, fees added to principal, and extra repayments can change the schedule.

An Excel table is a model, not a substitute for the loan agreement. Check whether the quoted rate is nominal APR or an effective annual rate, and whether the payment date or interest method differs from monthly compounding. Enter the actual lender-provided figures and conventions where known.

Frequently Asked Questions

How do I calculate EMI in Excel?
Use =-PMT(annual_rate/12,years*12,loan_amount) when the rate is entered as a decimal, such as 0.08 for 8%. If entered as a whole percentage such as 8, divide it by 1200 instead.

How do I make an EMI amortization schedule in Excel?
Calculate a fixed payment, then create columns for opening balance, interest, principal repayment, and closing balance. Each month, calculate interest on the opening balance and subtract the principal portion from it.

Why does the last balance in my Excel schedule not equal zero?
Small differences commonly result from rounding payments or interest each month. Check that formulas reference the prior closing balance and that the monthly rate is correct; adjust the final payment only to match the remaining amount due under the applicable terms.

Does EMI include fees and insurance?
Not necessarily. The basic EMI formula covers principal and interest under its stated assumptions. Fees, insurance, taxes, and other charges may be separate or included differently in a lender’s disclosed payment.

Can an EMI schedule handle extra payments or a changing rate?
Yes, but the simple fixed-payment table must be modified. Record extra principal payments separately and recalculate the balance or remaining term; for a changing rate, update the rate and payment according to the loan’s terms.

This result is an estimate for educational use; the lender’s disclosure gives the exact figures.

Related

Formula used
in p | Loan amount | - | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Monthly EMI | - | round(p * (apr / 1200) / (1 - (1 + apr / 1200) ^ -(yrs * 12)), 2) | 2
out int | Total interest | - | pmt * yrs * 12 - p | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.

More in this section

The whole Loan payments section →