A loan EMI schedule in Excel lists each payment and shows how much goes to interest, how much reduces the principal, and what balance remains. Enter the loan amount, annual interest rate and term in the calculator below to estimate the monthly payment and total interest; then use the spreadsheet steps to build a month-by-month schedule.
Formula
The calculator uses the standard fixed-payment amortization formula. In symbols, the unrounded monthly payment is M = P × r ÷ [1 − (1 + r)−n]; the displayed payment is rounded to the nearest cent. The monthly rate is r = APR ÷ 1200, and the number of payments is n = years × 12. Total interest is estimated as I = M × n − P, using the rounded monthly payment.
- P: starting principal, in your currency.
- APR: annual percentage rate entered as a percentage, such as 8 for 8% per year.
- r: monthly interest rate as a decimal.
- n: number of monthly payments.
- M: monthly payment, in your currency.
- I: estimated total interest over the full schedule, in your currency.
This calculation assumes monthly payments and a fixed rate. The actual payment schedule can differ if the lender uses different timing, rounds interest differently, or includes charges in the payment.
Worked Example
Using the calculator’s defaults—principal of 20,000 in your currency, 8% APR and a 5-year term:
- Convert APR to a monthly decimal rate: 8 ÷ 1200 = 0.0066667.
- Find the number of monthly payments: 5 × 12 = 60.
- Apply the payment formula: 20,000 × 0.0066667 ÷ [1 − (1.0066667)−60] ≈ 405.53.
- Calculate total interest using the rounded payment: 405.53 × 60 − 20,000 = 4,331.80.
The estimated monthly payment is 405.53 in your currency, and estimated total interest is 4,331.80 in your currency. Small differences can arise if a spreadsheet keeps more precision internally or applies rounding at a different stage.
Assumptions
- The interest rate stays fixed throughout the term.
- Payments are equal, made monthly at the end of each period.
- Interest compounds monthly at APR divided by 12.
- Fees, taxes, insurance, PMI and escrow are not included.
Build an EMI Schedule in Excel
For a detailed schedule, place the loan inputs in separate cells. For example, use B1 for principal, B2 for annual APR as a percentage, and B3 for the term in years. In B4, calculate the monthly payment with =ROUND(PMT(B2/12,B3*12,-B1),2). Excel’s PMT function returns a payment per period; the negative principal argument makes the payment display as a positive amount. If your APR is stored as the number 8 rather than the percentage value 8%, use =ROUND(PMT(B2/1200,B3*12,-B1),2) instead.
Create columns for payment number, opening balance, payment, interest, principal repaid and closing balance. Put the first payment number in row 8, and use these example formulas, adjusting cell references to match your layout:
- Payment number: 1, then increase by 1 for each row.
- Opening balance: the original principal in the first row; thereafter, the previous row’s closing balance.
- Payment: the monthly payment cell, such as
=$B$4. - Interest:
=ROUND(opening_balance*$B$2/12,2)when B2 stores APR as a percentage, or divide by 1200 if it stores a whole number such as 8. - Principal repaid: payment minus interest.
- Closing balance: opening balance minus principal repaid.
Copy the formulas down for the number of payments. With the example above, that is 60 rows. If your spreadsheet calculates a tiny negative final balance because of rounding, inspect the final row and reconcile it to zero; do not conceal a meaningful difference by forcing the balance to zero early.
How to Read the Schedule
Each payment is divided between interest and principal. Interest is calculated on the opening balance, so the interest portion generally falls as the balance declines. The principal portion generally rises while the total payment remains level. Early payments therefore do not reduce the balance by the full payment amount.
For the example, the first month’s interest is approximately 20,000 × 8% ÷ 12 = 133.33. From a payment of 405.53, about 272.20 reduces principal, leaving an opening balance for the next month of about 19,727.80. The precise displayed values depend on the spreadsheet’s rounding conventions.
Common Excel Issues and Limits
- APR format: Excel stores 8% as 0.08. If a cell contains 8, divide by 1200 for a monthly decimal rate; if it contains 8%, divide by 12.
- Sign convention: PMT treats money paid and money received as opposite signs. Use a negative present value to show the payment as positive.
- Rounding differences: A lender may calculate daily interest or use a different rounding sequence. A spreadsheet estimate may not match its official schedule exactly.
- Variable rates or irregular payments: A single fixed-payment formula cannot represent rate changes, missed or extra payments, payment holidays, or fees added to the balance without additional inputs and logic.
- Last payment: Rounding each period can leave a small residual. The contract’s final amount may be adjusted to settle the balance.
An amortization schedule is useful for understanding the arithmetic and checking how principal and interest change over time. It does not include costs unless they are explicitly entered, and it cannot establish the exact amount due under a specific loan agreement.
Frequently Asked Questions
What does EMI mean?
EMI means equated monthly instalment: a recurring payment intended to remain level over a stated term for a fixed-rate, fully amortizing loan. The payment includes interest and principal.
What Excel formula calculates an EMI?
Use =PMT(annual_rate/12, years*12, -loan_amount) when the annual rate is stored as an Excel percentage. If the rate is entered as a whole number, such as 8 for 8%, use =PMT(rate/1200, years*12, -loan_amount).
How do I calculate the interest for each month?
Multiply that row’s opening balance by the monthly interest rate. For an APR stored as an Excel percentage, use opening balance × APR ÷ 12; round only if you are modelling a schedule that rounds each period.
Why does my Excel EMI differ from the lender’s figure?
Check whether the APR is stored as 8% or as 8, whether the term is in years or months, and whether the amount financed includes charges. Differences can also reflect payment dates, daily interest, or rounding rules.
Does the EMI include fees or insurance?
Not in this calculation. It covers principal and interest under the stated assumptions; add separate amounts only if they are part of the actual payment and you have reliable figures from the lender.
The result is an estimate, and 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 payment | - | 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
- Closing Costs on a House Loan: What to Expect$405.53
- Secured Loan for Home Refinancing: Payment Calculator$405.53
- Closing Cost House Calculator: Estimate Buyer Costs$8,500.00
- Closing Cost House Estimate Calculator
- Loan Repayment Extension Request Letter: What to Include$405.53
- Refinance Calculator With PMI and Loan Term