Amortization Schedule in Excel: Build One Step by Step

How-to basics · Updated November 20, 2026

To find a ready-made schedule, open Excel for Microsoft 365 on Windows and choose File > New, then search for loan amortization schedule; template results can vary. To build one yourself, enter the loan details and use PMT plus a few formulas to calculate each payment’s interest, principal, and remaining balance. The steps below use a fixed-rate loan and a clear example, so you can check the results as you go.

What You’ll Need

  • Excel for Microsoft 365 on Windows, or a recent Excel version with PMT and EDATE.
  • The loan principal, annual interest rate, loan term, payment frequency, and first payment date.
  • Your lender’s payment and interest rules. The example assumes a fixed rate, regular monthly payments, and interest calculated monthly; actual contracts may use different conventions.

Step-by-Step

  1. Look for a template, if you prefer not to start from a blank sheet. In Excel for Microsoft 365 on Windows, select File > New and type loan amortization schedule in the template search box. Preview a result and confirm it includes the loan inputs and a period-by-period breakdown of principal, interest, and balance. Templates may be unavailable, change over time, or come from third parties, so inspect the formulas and assumptions before relying on one. Excel does not have a dedicated command that automatically creates a schedule in every workbook. If you cannot find a suitable template, build one with the steps below.
  2. Enter the loan inputs in a consistent layout. Start a blank worksheet. Put these labels in A2:A6 and the corresponding values in B2:B6: Loan amount, Annual interest rate, Term in years, Payments per year, and First payment date. For the worked example, enter 20000 in B2, 6% in B3, 3 in B4, 12 in B5, and a real date such as 1/15/2027 in B6. Format B2 as Currency, B3 as Percentage, and B6 as a Date using Home > Number. The example’s principal is $20,000, the annual rate is 6%, and the loan has 36 monthly payments.
  3. Calculate the regular payment. Label A7 Scheduled payment. With the principal in B2, annual rate in B3, term in years in B4, and payments per year in B5, enter this formula in B7: =ROUND(-PMT(B3/B5,B4*B5,B2),2). PMT calculates a level payment for each period. Dividing the annual rate by 12 converts it to a monthly rate, and multiplying three years by 12 gives 36 periods. The minus sign makes the payment display as a positive amount, while ROUND sets it to cents. For these inputs, the regular payment is $608.44. This is an illustrative calculation, not a quote; compare it with the lender’s stated payment.
  4. Create the schedule headings. In row 11, enter Period in A11, Beginning balance in B11, Payment in C11, Interest in D11, Principal in E11, Ending balance in F11, and Due date in G11. Select the headings and choose Home > Bold if you want them to stand out. Keeping interest and principal in separate columns makes it easier to see how each payment is allocated and to check the remaining balance.
  5. Set up the first payment row. In row 12, enter the following formulas. With the first schedule row in row 12 and the inputs in B2:B7, enter =1 in A12 for the first period. With the original principal in B2, enter =$B$2 in B12. The dollar signs keep the input reference fixed when formulas are copied. With the scheduled payment in B7 and the first row’s beginning balance and interest in B12 and D12, enter =MIN($B$7,B12+D12) in C12. This uses the regular payment except when the amount needed to clear the loan is smaller, such as for the final payment. With the beginning balance in B12 and the annual rate and payment frequency in B3 and B5, enter =ROUND(B12*$B$3/$B$5,2) in D12. This rounds the period’s interest to cents. With the payment in C12 and interest in D12, enter =C12-D12 in E12. With the beginning balance in B12 and principal paid in E12, enter =MAX(0,B12-E12) in F12. MAX prevents a tiny negative balance caused by rounding. For the example, the first month’s interest is $100.00, principal paid is $508.44, and the ending balance is $19,491.56; those amounts add up correctly to the $608.44 payment.
  6. Calculate the due date, if you want dates in the schedule. With the first payment date in B6 and the period number in A12, enter =EDATE($B$6,A12-1) in G12. EDATE advances by whole months, so the first row uses the date in B6. Format G12 as a date with Home > Number. If your lender’s payment dates follow a special calendar, such as a weekend adjustment, check its rules rather than assuming this date formula reproduces them.
  7. Build the second row and copy the schedule down. With the previous period in A12, enter =A12+1 in A13. With the previous ending balance in F12, enter =F12 in B13. In C13, D13, E13, and F13, copy the corresponding formulas from C12:F12 down one row; relative references will adjust to row 13. With the date formula in G12, copy it to G13 as well. Select A13:G13 and drag the fill handle (the small square at the lower-right corner of the selection) down for the remaining periods. For a 36-payment example, fill through row 47. Double-check that the last period number is 36.
  8. Format and review the completed schedule. Select the money columns B:F and apply Currency or Accounting from Home > Number. Review the first and last rows, and confirm that every beginning balance after the first equals the previous row’s ending balance. In this example, the last payment should bring the ending balance to zero; the final payment can be slightly different from $608.44 because the interest amounts and regular payment are rounded to cents. If the schedule does not reach zero, verify the inputs and formulas before changing any values.
  9. Save a separate copy and compare it with the loan terms. Save the workbook with a useful name, and keep an untouched copy if you are adapting a template. Compare the payment amount, number of payments, and total schedule with the lender’s documents. A spreadsheet is a calculation aid: fees, changing rates, extra payments, daily accrual, and lender-specific rounding can produce different results. Check the actual terms with the lender or a qualified professional; this example is not financial, tax, investment, or legal advice.

Common Mistakes

  • Using the annual rate as the monthly rate. For monthly payments, divide the annual rate by 12 in PMT and in the interest calculation. Enter 6% as 6%, not 6, and do not divide by 100 again if the cell is already a percentage.
  • Mixing years and payment periods. PMT needs a rate and number of periods that use the same frequency. For a three-year monthly loan, use 6% divided by 12 and 3 multiplied by 12, not three periods.
  • Reversing the payment sign. Excel’s cash-flow convention can show a negative payment if the principal is positive. In the example, the minus sign before PMT makes the scheduled payment positive and easier to read.
  • Using the wrong balance in the interest calculation. Interest for a period is based on that period’s beginning balance in this example, not its ending balance. Each later row must use the prior row’s ending balance as its beginning balance.
  • Expecting every payment to be exactly the same after rounding. Rounding interest to cents and rounding the scheduled payment can leave a few cents to adjust in the final payment. Do not force the ending balance to zero by editing an earlier payment unless the loan terms call for that adjustment.
  • Assuming every loan follows the example’s rules. Adjustable rates, fees, interest-only periods, skipped payments, and daily interest calculations require different logic. A basic fixed-rate schedule may not match the lender’s official balance in those cases.

Tips

  • Keep input cells separate from calculation cells and label every input, including whether the rate is annual and how often payments occur. Clear labels make the workbook easier to check later.
  • Use absolute references for fixed inputs, such as $B$7 for the regular payment, but leave row references relative where each period should update. When you fill formulas down, this combination keeps the inputs fixed and advances the schedule references.
  • Freeze the headings for a long schedule by selecting a cell below and to the right of the rows and columns you want to keep visible, then choose View > Freeze Panes > Freeze Panes. For this layout, selecting A12 and choosing that command keeps the heading row visible while scrolling.
  • Turn the schedule into an Excel table if you want filter and banded-row controls: select the completed schedule, including its headings, and choose Insert > Table. Confirm that My table has headers is selected. Keep the input cells outside the table so they remain easy to distinguish.
  • For a quick check, add the principal amounts in the schedule and compare their sum with the original loan amount. In a fully paid fixed-rate example with no extra payments, the total principal should equal $20,000, subject to the final-cent rounding used in the workbook.
  • On Excel for Mac, the formulas and worksheet layout are the same; ribbon labels and the template experience can vary slightly by release. Excel for the web supports ordinary worksheet formulas, but some template search results and ribbon controls may differ from the Windows desktop app. If you use a semicolon as your local formula separator, Excel may adapt formulas to that regional setting.

Troubleshooting

The formula appears as text instead of calculating: Select the cell, change its format from Text to General on Home > Number, then press F2 and Enter on Windows. On Mac, use Control+U to edit the cell and press Return, or double-click the cell and confirm the formula. Check that the first character is an equals sign and that Show Formulas is not turned on under Formulas > Show Formulas.

You see #NUM! or an unexpected payment: Confirm that the term, payment frequency, and rate are positive and that B3 is entered as a percentage. Check the PMT inputs: annual rate divided by payment frequency, and years multiplied by payment frequency. A zero-interest loan is a useful diagnostic; its payment should equal the principal divided by the total number of payments.

The balance increases or the loan never reaches zero: Check that the payment is greater than the period’s interest and that the rate is not being interpreted as 6 instead of 6%. Confirm that B13 points to F12, not B12 or another cell, and that the row formulas were filled through all payment periods. If the payment is below the interest due, the balance can grow because the payment does not cover the interest.

The ending balance is a few cents below or above zero: Confirm that the interest and payment rounding rules match the lender’s method. The example rounds interest each month and caps the last payment at the amount due, but an official statement may use a different convention. Do not treat a small discrepancy as proof that the lender’s figure is wrong; ask the lender how it calculates interest and final payments.

EDATE is missing or dates look like numbers: Make sure the date in B6 is a real Excel date, not text. Apply a date format to column G. If your Excel version does not recognize EDATE, enter payment dates manually or use a version that supports the function; the date column is optional and does not affect the payment calculations.

Frequently Asked Questions

Does Excel have a built-in amortization schedule button?
Not as a universal one-click worksheet command. You can search templates through File > New in Excel for Microsoft 365 on Windows, or make a schedule with formulas. Template availability and layout can change, so inspect any downloaded workbook before using it.

Which Excel formula calculates the loan payment?
PMT calculates a regular payment when the rate and number of periods match the payment frequency. In the example, the formula in B7 uses the monthly rate and 36 monthly periods. The schedule formulas then split each payment into interest and principal.

Can I make a schedule for weekly or biweekly payments?
Yes, if the loan terms use a fixed periodic rate and regular payments. Change B5 to the correct number of payments per year and make sure the interest-rate conversion matches the lender’s convention. Do not assume that dividing an annual rate by 52 or 26 matches every contract; confirm the method with the lender.

Why is the final payment different from the others?
It may be smaller because the remaining principal plus final-period interest is less than the regular payment. Rounding interest and payments to cents can also create a small adjustment. The example uses MIN to cap the last payment at the amount due and MAX to keep the ending balance from displaying below zero.

Can I include extra payments?
Yes, but the basic schedule does not include them. Add a clearly labeled extra-payment input and subtract it from the remaining principal only after checking the lender’s rules for how extra money is applied and whether fees or restrictions apply. Recalculate later interest from the updated balance, and compare the result with the lender’s official schedule.

Will this match my lender’s statement exactly?
Not necessarily. The example assumes a fixed annual rate divided evenly into monthly periods, a regular payment, and monthly rounding. A lender may use daily interest, a different payment date convention, fees, or another rounding method. Use the lender’s stated terms and ask for an explanation if the figures differ.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.