To make an EMI schedule in Microsoft Excel, calculate the regular payment with the PMT function, then list each period’s opening balance, payment, interest, principal repaid, and closing balance. For a monthly loan, the key calculation is =-PMT(annual_rate/12, years*12, principal); the schedule shows how each payment reduces the debt over time.
What an EMI schedule shows
EMI means equated monthly installment: a scheduled payment that is usually the same amount each month when the loan has a fixed interest rate and payments are made on time. An EMI schedule, also called an amortization schedule, breaks that payment into interest and principal for every installment. The split changes even though the payment stays level. Early payments generally contain more interest because the outstanding balance is larger; later payments generally repay more principal.
A schedule is useful for understanding a loan offer, checking the lender’s payment figures, estimating the remaining balance after a particular month, and seeing how extra principal payments might affect the timeline. It is an estimate based on the assumptions entered, not a replacement for the lender’s official repayment statement. The lender’s contract determines the actual rate method, payment dates, fees, rounding, and treatment of additional payments.
Set up the loan inputs
In a blank worksheet, put the loan assumptions in one place so they are easy to check or change. For the formulas below, enter these labels and values:
- A2: Loan principal; B2: 100000
- A3: Annual interest rate; B3: 12%
- A4: Term in years; B4: 3
- A5: Payments per year; B5: 12
- A6: Regular payment; leave B6 for the formula
Enter the principal as a positive number and the rate as a percentage, such as 12%, rather than the whole number 12. The payments-per-year input is 12 for monthly payments, 4 for quarterly payments, or 1 for annual payments, if the loan is actually structured that way. Confirm the payment frequency and the rate period in the loan documents; not every lender calculates interest by simply dividing an annual rate by 12.
With principal in B2, annual nominal rate in B3, years in B4, and payments per year in B5, enter this in B6 to show a positive installment:
=-PMT(B3/B5,B4*B5,B2)
PMT needs a rate for each payment period, the total number of payments, and the present value of the loan. The minus sign changes Excel’s usual cash-flow sign convention into a positive displayed payment. Here, the illustrative monthly payment is about 3321.43, before any fees or separate charges. The calculation assumes a fixed 12% nominal annual rate, monthly payments, and a 36-month term.
Build the amortization table
On row 9, create these column headings: A9 Period, B9 Opening balance, C9 Payment, D9 Interest, E9 Principal, and F9 Closing balance. Put the first payment on row 10. The following formulas assume the input cells are B2:B6 and the schedule columns are arranged exactly as described. Set the first period number in A10:
=1
With the opening balance in B10 and the original loan principal in B2, enter this in B10:
=$B$2
With the opening balance in B10, interest in D10, and regular payment in B6, enter this in C10. The MIN function adjusts the final installment so it does not charge more than the remaining balance plus that period’s interest:
=MIN($B$6,B10+D10)
With the opening balance in B10, annual rate in B3, and payments per year in B5, calculate one period’s interest in D10:
=B10*($B$3/$B$5)
With the payment in C10 and interest in D10, calculate the amount of principal repaid in E10:
=C10-D10
With the opening balance in B10 and principal repaid in E10, calculate the new balance in F10:
=MAX(0,B10-E10)
The MAX function prevents a tiny negative balance caused by decimal rounding from appearing in the final row. It does not fix an incorrect interest rate or payment assumption. For the next row, enter the next period number in A11 and carry forward the prior closing balance into B11:
=A10+1
With the previous period number in A10, use that formula in A11. With the prior closing balance in F10, use this formula in B11:
=F10
For row 11 and later rows, the payment, interest, principal, and closing-balance formulas are the same pattern as row 10, adjusted to the current row. Copy the formulas down for 36 payment rows, ending on row 45 for a three-year monthly loan. In Microsoft 365 for Windows, you can drag the fill handle at the bottom-right of the selected cells, or copy and paste the formulas into the rows below. Excel for Mac has the same formulas and fill-handle behavior; some keyboard shortcuts differ, but no different formula is needed.
Check the worked monthly example
For the example inputs—100000 principal, 12% annual interest, three years, and monthly payments—the first period begins with a balance of 100000. The monthly rate under this simple assumption is 1%. First-month interest is therefore 1000, the payment is about 3321.43, and principal repaid is about 2321.43. The closing balance is about 97678.57. These amounts are rounded for display; Excel retains additional decimal precision unless you change the underlying values.
Across 36 payments, the total paid is approximately 119571.48 and total interest is approximately 19571.48 when using the rounded payment figure. The exact totals can differ slightly because Excel calculates with the unrounded PMT result and because lenders may round each installment or interest amount differently. To display currency, select the relevant cells and use Home > Number to choose a currency or accounting format. Formatting changes what is shown, not the stored calculation precision.
With payment amounts in C10:C45 and interest in D10:D45, these optional summary formulas calculate the total paid and total interest:
=SUM(C10:C45)
=SUM(D10:D45)
Place those formulas in separate summary cells, not inside the ranges they sum. The difference between total payments and the original principal should be close to total interest in this no-fee example. A mismatch can signal an incomplete schedule, an incorrect input, or a payment frequency that does not match the term.
Understand the interest and payment assumptions
The simple schedule above treats each period’s interest as the opening balance multiplied by the annual rate divided by the number of payments per year. This is a useful model for many fixed-rate examples, but it is not universal. A lender may accrue interest daily, use a different day-count convention, set a different first payment date, or apply payments to fees before principal. Those details can produce a real statement that differs from the worksheet even when the headline rate looks the same.
PMT assumes regular, equal intervals and a constant rate. It also assumes the payments occur at the end of each period unless you specify otherwise. A loan with a variable rate, irregular payment dates, a payment holiday, or fees rolled into the balance needs a more tailored model. Do not treat an advertised APR as automatically interchangeable with the contractual periodic interest rate: APR may account for certain costs or be defined differently under applicable rules. Check the lender’s terms before using a rate in a schedule.
For a different payment frequency, update B5 and make sure the term and payment dates really correspond to that frequency. The formula’s period count is years multiplied by payments per year. For example, a three-year quarterly schedule has 12 periods, not 36. A fixed monthly payment with daily interest accrual cannot be matched exactly by merely changing the formula’s monthly rate.
Make the schedule easier to read and audit
Use clear labels and keep assumptions separate from calculated rows. Freeze the heading row if the schedule is long, and format balances and payments consistently as currency. A quick visual review should show the opening balance falling toward zero, interest declining over time for a fixed-rate amortizing loan, and principal repayment rising. The final closing balance should be zero or extremely close to it.
- Use a distinct fill color for input cells so assumptions are not confused with formulas.
- Show enough decimal places while checking calculations, even if the finished sheet displays two currency decimals.
- Keep an untouched copy of the original assumptions before exploring alternative rates or terms.
- Compare the first payment and payoff date with the lender’s official schedule, then investigate differences rather than forcing the spreadsheet to match blindly.
- For a longer schedule, add a payment date column using actual contract dates; monthly dates can vary in length and are not always equivalent to a fixed number of days.
A useful audit is to check one row by hand: interest should equal opening balance times the applicable period rate, principal should equal payment minus interest, and closing balance should equal opening balance minus principal. If the payment is less than interest, principal becomes negative and the balance grows. That situation may indicate negative amortization or a wrong input, so do not assume the loan will pay off on the displayed schedule.
Common errors and useful adjustments
A frequent mistake is entering 12 instead of 12% in B3. Excel interprets 12 as 1200%, which produces a dramatically different payment. Another is using the annual rate directly in PMT while the schedule is monthly; the payment-rate argument must represent the period rate. Also check that the number of payments matches the term: 3 years of monthly payments means 36 periods.
If the balance does not reach zero, first verify the inputs, formulas, and number of filled rows. A small residue can result from rounding displayed values, while a large residue usually indicates a rate, payment, or schedule-length error. If the last row shows a negative balance, use a final-payment adjustment such as the MIN formula above, and check whether the contractual final payment is expected to differ from earlier installments.
Extra payments require more than changing the standard EMI formula. If a borrower makes an additional principal payment, the schedule must apply it to the balance on the correct date, after accounting for that period’s interest and any lender rules. Some loans reduce the term while keeping the installment steady; others recalculate the installment. Fees or limits may also apply. Model extra payments in a separate column only after confirming how the lender processes them, and do not assume the worksheet changes the loan contract.
When Excel is enough and when to use another method
Excel is suitable for a transparent, editable estimate when the rate and payment pattern are stable. PMT gives a quick payment estimate, while the row-by-row table makes the changing interest and principal visible. A lender calculator may be more appropriate for a quick quote, but its result is only as useful as the assumptions entered. The official amortization schedule or account statement is the reference for actual charges and balances.
For a variable-rate loan or one with irregular dates, use a period-by-period model that records the actual rate and payment date for each period, or request the lender’s schedule. You can still use Excel, but a single PMT formula will not describe changing payments accurately. If the result is being used to make a borrowing decision or resolve a billing dispute, verify the loan terms and calculations with the lender or a qualified financial professional. This spreadsheet example is educational, not financial advice.
Frequently Asked Questions
What is the Excel formula for EMI?
For a fixed-rate loan with monthly payments, use =-PMT(annual_rate/12,years*12,principal). Replace the labels with cell references, and confirm that the rate and payment frequency match the loan terms.
How do I calculate monthly loan interest in Excel?
For a schedule using a nominal annual rate divided into monthly periods, multiply the opening balance by the annual rate divided by 12. For example, if the balance is in B10 and the annual rate is in B3, the formula is =B10*($B$3/12). Daily-accrual loans may need a different calculation.
Why does my EMI schedule show a small balance at the end?
Displayed values may be rounded to two decimal places while Excel calculates with more precision, or the schedule may have the wrong number of periods. Check the inputs and formulas, and compare with the lender’s rounding method. A final-payment adjustment can remove a tiny spreadsheet residue, but it should not conceal a larger error.
Can Excel calculate a loan with a changing interest rate?
Yes, but a single PMT formula assumes a constant rate. A variable-rate schedule needs the applicable rate for each period and may require recalculating payments according to the loan agreement. Use the lender’s rules for rate changes, payment resets, and dates.
Does an EMI include processing fees or insurance?
Not automatically. The basic PMT calculation covers principal and interest for the assumptions provided. Fees or insurance may be charged separately or included in a financed amount, depending on the contract. Check the lender’s documents and model any included costs explicitly.
Related
- Debt Payoff Calculator Excel Template: Build Your Plan
- Debt Payoff Calculator in Excel: Build One Step by Step
- Free Debt Payoff Calculator Excel: How to Build One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Compare Documents in Word: See Every ChangeTo compare two versions of a document in Microsoft Word, open Word and choose Review > Compare >…
- Google Sheets Print Area: Set and Print a Cell RangeGoogle Sheets does not have a permanent, Excel-style “Set Print Area” command. To print just part of a…
- Excel Data Analysis Experience: Skills, Tasks, and ProofExcel data analysis experience means being able to organize data, check its quality, use formulas or tools to…
- Excel Page Break Character: Meaning and Print BreaksExcel does not use a special character inside a cell to create a worksheet page break: page breaks…
- Switch Excel Cell Format: Change Numbers, Dates, and TextTo switch an Excel cell’s format, select the cell or range, then choose a format from Home >…
- Add a Picture to a Word Header: Steps, Layout, and TipsYes. In Word for Microsoft 365 on Windows, open the header by choosing Insert > Header > Edit…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.