An Excel amortization table uses PMT to calculate a regular loan payment, then formulas to split each payment into interest and principal and update the remaining balance. For example, a $20,000 loan at 6% annual interest over three years, paid monthly, has a payment of about $608.44 before any lender-specific fees or adjustments.
What an Amortization Table Shows
An amortization table, also called an amortization schedule, lays out a loan one payment period at a time. Each row typically shows the payment number, opening balance, payment, interest charged, principal repaid, and closing balance. As payments reduce the balance, the interest portion usually declines and the principal portion grows, assuming a fixed rate and equal payments.
This is useful because the payment alone does not show how quickly a borrower is paying down the amount borrowed. The schedule also helps check a lender’s figures, estimate the effect of an extra payment, or compare different terms. A spreadsheet is an estimate based on the assumptions entered; the lender’s payment schedule and loan agreement control the actual amounts.
Set Up the Loan Inputs
In a blank worksheet in Microsoft Excel for Microsoft 365 on Windows, enter labels in column A and values in column B. Use this layout:
- A2: Loan amount; B2: 20000
- A3: Annual interest rate; B3: 6%
- A4: Term in years; B4: 3
- A5: Payments per year; B5: 12
- A6: Number of payments; calculate this from the term and payment frequency
- A7: Regular payment; calculate this with PMT
With the number of years in B4 and payments per year in B5, enter this in B6: =B4*B5. It returns 36 payments for the example. The payment frequency must match the rate period used in the payment calculation. Monthly payments use 12 periods per year; quarterly payments use 4.
With annual interest in B3, payments per year in B5, total payments in B6, and the loan amount in B2, enter this in B7: =-PMT(B3/B5,B6,B2). It returns approximately $608.44 for the example. The minus sign makes the result positive because Excel treats money borrowed and money paid out as opposite cash-flow directions. The calculation assumes a fixed rate, level payments, and payments at the end of each period.
Build the Payment Schedule
Start the schedule in row 10. Put Period, Beginning Balance, Payment, Interest, Principal, and Ending Balance in A10:F10. The first payment goes in row 11. Enter 1 in A11 and the original loan amount in B11. The formulas below assume the input cells above and this schedule layout.
With B11 as the first beginning balance, B2 as the original loan amount, and B3 and B5 as the annual rate and payments per year, enter in B11: =$B$2. This returns $20,000. The dollar signs keep the input reference fixed when formulas are copied.
With the payment amount in B7, the beginning balance in B11, and the interest formula in D11, enter in D11: =ROUND(B11*$B$3/$B$5,2). This returns $100.00 of interest for the first month. Rounding interest to cents makes the displayed schedule behave more like a statement that posts currency to two decimal places.
With the payment in B7 and the period’s beginning balance and interest in B11 and D11, enter in C11: =MIN($B$7,B11+D11). This returns about $608.44 in the first period and caps the final payment at the amount still owed, including that period’s interest.
With the payment in C11 and interest in D11, enter in E11: =C11-D11. The first payment therefore repays about $508.44 of principal. With the beginning balance in B11 and principal in E11, enter in F11: =MAX(0,B11-E11). This returns about $19,491.56. MAX prevents a small negative balance from appearing due to rounding.
For the next row, enter 2 in A12. With the previous ending balance in F11, enter in B12: =F11. With the new balance in B12, enter in D12: =ROUND(B12*$B$3/$B$5,2). With B7, B12, and D12 holding the payment, opening balance, and interest, enter in C12: =MIN($B$7,B12+D12). With the payment in C12 and interest in D12, enter in E12: =C12-D12. With the opening balance in B12 and principal in E12, enter in F12: =MAX(0,B12-E12). The second month’s interest is $97.46, principal is about $510.98, and ending balance is about $18,980.58.
Select A12:F12 and fill the formulas down for the remaining periods, stopping at period 36 in row 46. On Windows, Ctrl+D fills formulas down when the range includes the starting row and rows below it. On Mac, use Command+D. You can also drag the fill handle at the lower-right corner of the selection. Excel for Mac uses the same formulas; the main difference here is the keyboard shortcut.
How the Payment and Interest Calculations Work
PMT calculates the level payment that amortizes a balance over a set number of periods at a periodic interest rate. Dividing the annual rate by the payment frequency converts the example’s 6% annual rate to 0.5% per month. Multiplying the term by that frequency gives 36 monthly payments. If the loan has a different compounding convention or the lender defines its rate differently, a simple annual-rate division may not reproduce the lender’s figures exactly.
Each period’s interest is calculated on the opening balance, not the original loan amount. The principal portion is what remains after subtracting interest from the payment. That principal reduces the balance carried into the next period. Consequently, a fixed payment does not mean a fixed amount of principal is repaid each month.
Check the schedule’s shape as well as its individual values: the opening balance should decline, interest should generally decline for a fixed-rate loan, and the final balance should reach zero or a very small rounding residue. In the example, the first month’s interest is 0.5% of $20,000, or $100. The second month’s interest is 0.5% of the reduced $19,491.56 balance, rounded to $97.46.
Dates, Totals, and Formatting
To show payment dates, add a first payment date in B8 and a Payment Date heading in G10. This optional formula assumes B8 contains a valid Excel date, A11 is period 1, and the schedule has monthly payments. Enter in G11: =EDATE($B$8,A11-1). It returns the date in B8 for the first payment; subsequent periods advance by one month when the period number increases. EDATE preserves monthly intervals, including when a month has fewer days than the starting date. For nonmonthly schedules, adjust the date logic to the actual payment frequency.
Format B2 and the schedule’s currency columns as currency or accounting, and format B3 as a percentage. To apply a number format, select the cells and use Home > Number in the ribbon. Formatting changes how values appear, not the underlying precision. The ROUND in the interest formula is what rounds the calculated interest itself.
You can check the total amount paid and total interest by summing the relevant schedule columns. With payments in C11:C46, enter in an unused cell: =SUM(C11:C46). This returns the sum of the payments actually shown in the schedule, including any smaller capped final payment. With interest in D11:D46, enter in another unused cell: =SUM(D11:D46). This returns total interest charged by the schedule. These totals can differ slightly from a calculation that uses unrounded values throughout, so compare them with the lender’s disclosure using the lender’s rounding rules.
Common Problems and Rounding
- Payment appears negative: PMT follows cash-flow sign conventions. Use the leading minus sign shown in the example if you want the payment displayed as a positive amount.
- The balance grows instead of shrinking: Check that the periodic rate and payment frequency are consistent, and confirm the payment is greater than the interest due.
- The schedule ends above or below zero: Check that you filled exactly the planned number of periods and that references to the input cells did not shift. Rounding can also leave a few cents of difference.
- The last payment is too large or negative: Check the opening balance and interest in the final row. The MIN payment formula and MAX ending-balance formula help contain ordinary rounding differences, but they cannot correct incorrect inputs.
- The lender’s payment differs: Fees, daily interest, irregular first periods, payment timing, rate resets, or a different rounding policy can change real loan figures.
A common misconception is that the stated annual rate can always be used directly as the rate in PMT. For monthly payments, the formula needs a rate per payment period. Dividing the annual rate by 12 is appropriate for the straightforward nominal-rate example here, but the loan documents may specify a different method. Also distinguish a nominal interest rate from an annual percentage rate: an APR may include certain costs and is not necessarily the rate to use as the periodic interest input.
Extra Payments and Variable-Rate Loans
An extra principal payment reduces the balance faster and normally reduces future interest, but the schedule must reflect how the lender applies it. Some lenders keep the regular payment and shorten the payoff period; others recalculate a payment or apply restrictions or fees. For a what-if model, add a column for extra principal and subtract the amount actually applied to principal from the period’s balance. Do not treat an extra amount as if it automatically changes the contractual monthly payment.
A fixed PMT schedule is not a complete model for a variable-rate loan. When the rate changes, future interest changes too, and the required payment may be recalculated under the loan’s rules. Update the rate for each affected period and use the lender’s method for recalculating payments, caps, and payment dates. Check the actual loan terms or ask the lender rather than assuming that the original PMT remains valid.
When Excel Is Enough—and When to Verify
Excel is well suited to a transparent estimate, a classroom example, or a comparison using clearly stated assumptions. It is especially useful for seeing how term, rate, and payment frequency affect the balance over time. It does not replace a lender’s official amortization schedule, and a spreadsheet cannot establish whether a particular loan is suitable for you.
For a real borrowing decision, verify the rate, payment dates, fees, payment timing, and prepayment rules against the lender’s documents. If a discrepancy matters, ask the lender for an explanation or consult a qualified financial professional. This example is educational spreadsheet guidance, not financial, tax, investment, or legal advice.
Frequently Asked Questions
What is the Excel formula for a loan payment?
Use PMT with the periodic interest rate, total number of payments, and loan amount. In the example, the formula in B7 is =-PMT(B3/B5,B6,B2), where B3 is the annual rate, B5 is payments per year, B6 is total payments, and B2 is principal.
How do I calculate the interest for each month?
Multiply the opening balance by the monthly rate and round to cents. In the example schedule, the formula in D11 is =ROUND(B11*$B$3/$B$5,2). The first month’s interest is $100.00.
Why does the principal portion increase over time?
With a fixed rate and regular payment, interest is charged on a declining balance. As interest takes a smaller share of each payment, more of the same payment can go toward principal.
Can I use this schedule for a car loan or mortgage?
The basic layout works for loans with fixed rates, regular payments, and consistent periods. Real mortgages and other loans may include fees, escrow, irregular dates, rate changes, or lender-specific rules, so compare the spreadsheet with the official schedule.
How do I make the final balance exactly zero?
Use a capped final payment and a nonnegative ending balance, as in the MIN and MAX formulas above. Rounding and lender conventions may still produce a small difference, so do not force the result by changing earlier payments without checking the underlying terms.
Related
- Finding a Z Value in Excel: Scores, Tests, and Cutoffs
- Free AI Excel Analysis: What You Can Do at No Cost
- Excel Yin-Yang Analysis: Build a Balanced Comparison
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Count Unique Values Across Excel Sheets with One FormulaTo count distinct nonblank values across worksheets in Excel for Microsoft 365, stack the ranges and count the…
- Excel IFERROR for Multiple Cells: Formulas and TipsTo handle errors across multiple cells in Excel, use IFERROR in the first output cell and fill the…
- Excel: Sum VLOOKUP Values Across Multiple Matching RowsTo total every row that matches a lookup value, use SUMIF, not a regular VLOOKUP: with the matching…
- Excel Formula If Cell Contains Value: Practical ExamplesTo return one result when a cell is not empty and another when it is empty, use =IF(A2<>"","Yes","No").…
- Remove Duplicates in Excel Filter: A Step-by-Step GuideTo remove duplicate rows in Excel for Microsoft 365 on Windows, select a cell in your data, choose…
- Excel Sheet Number Sequence: How to Create OneTo create a number sequence in an Excel worksheet, put =SEQUENCE(10) in a blank cell with room below…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.