How to Build an Amortization Schedule in Excel Step by Step

How-to basics · Updated November 20, 2026

To build a basic loan amortization schedule in Excel, enter the loan amount, annual interest rate, payment frequency, and term; calculate the regular payment with PMT; then calculate each period’s interest, principal, and remaining balance. The example below uses a $20,000 loan at 6% annual interest, paid monthly over three years. It is illustrative only: check the lender’s actual rate, fees, payment dates, and rounding rules before relying on a schedule.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows. The formulas below also work in current Excel for Mac and Excel for the web.
  • The loan’s starting principal, annual interest rate, payment frequency, and number of years.
  • The lender’s terms if you want the schedule to match an actual loan, including whether the rate is fixed, how interest is calculated, and whether fees or extra payments apply.

Step-by-Step

  1. Set up the loan inputs. In a blank worksheet, put the labels Loan amount, Annual interest rate, Payments per year, and Term in years in A1:A4. Put 20000 in B1, 6% in B2, 12 in B3, and 3 in B4. Enter the annual rate as a percentage, not as the number 6; enter 12 for monthly payments, 4 for quarterly payments, or 1 for annual payments. Format B1 as Currency and B2 as Percentage using Home > Number.
  2. Calculate the total number of payments. With the labels and sample inputs in A1:B4, put Total payments in A5. In B5, enter =B3*B4. For this example, the result is 36 payments. Confirm that the term and payment frequency produce the count you expect before continuing.
  3. Calculate the interest rate per payment period. With the annual rate in B2 and payments per year in B3, put Periodic rate in A6 and enter =B2/B3 in B6. The example returns 0.5%, or 0.005, per month. This assumes a nominal annual rate divided evenly across the payment periods; a lender’s actual interest method may differ.
  4. Calculate the scheduled payment. With principal in B1, periodic rate in B6, and payment count in B5, put Scheduled payment in A7 and enter =ROUND(-PMT(B6,B5,B1),2) in B7. The minus sign changes PMT’s normally negative cash-flow result into a positive payment, and ROUND sets it to cents. For the example, the payment is about $608.44 per month. PMT calculates a level payment that covers interest and repays the principal over the stated number of periods; it does not include separate fees or irregular charges.
  5. Make the schedule headings. Starting in row 11, enter Period in A11, Beginning balance in B11, Payment in C11, Interest in D11, Principal in E11, and Ending balance in F11. These columns make it possible to see how each payment is split and how much of the loan remains. Format the money columns B:F as Currency or Accounting, ideally with two decimal places.
  6. Fill in the first period number and opening balance. In A12, type 1. With the starting loan amount in B1 and the schedule beginning on row 12, enter =$B$1 in B12. The dollar signs keep the input reference fixed if you later copy the formula. The first beginning balance should be $20,000.00.
  7. Calculate first-period interest. With the first beginning balance in B12 and the periodic rate in B6, enter =ROUND(B12*$B$6,2) in D12. In this example, one month of interest is $100.00: $20,000 multiplied by 0.5%. Rounding interest to cents at each period is a practical way to make the displayed schedule follow common payment rounding, but a lender may use a different convention.
  8. Calculate the payment, principal, and ending balance. With the first beginning balance in B12 and interest in D12, enter =MIN($B$7,B12+D12) in C12. This uses the regular payment unless the remaining amount due is smaller, which helps prevent a final overpayment. With payment in C12 and interest in D12, enter =C12-D12 in E12. With beginning balance in B12 and principal in E12, enter =MAX(0,B12-E12) in F12. For period one, the expected interest is $100.00, principal is $508.44, and ending balance is $19,491.56.
  9. Set up the next period and copy the formulas down. In A13, enter =A12+1; in B13, enter =F12. These carry the period number and prior ending balance forward. Copy C12:F12 down to row 47, then copy A13:B13 down to row 47 as well. Row 47 is period 36 for this example. On Windows, select the cells and use the fill handle at the lower-right corner of the selection, or copy and paste with Ctrl+C and Ctrl+V. On Mac, use Command+C and Command+V; the fill handle works there too. Check that each beginning balance after the first equals the previous row’s ending balance.
  10. Add totals and check the payoff. With payments in C12:C47, interest in D12:D47, and principal in E12:E47, place Total in A48. Enter =SUM(C12:C47) in C48, =SUM(D12:D47) in D48, and =SUM(E12:E47) in E48. The sum of principal should equal the original $20,000, subject to rounding; total payments should equal total principal plus total interest. The final ending balance in F47 should be zero or a very small rounding difference. Because the payment is rounded to cents and the last payment is capped at the amount due, the last payment can be slightly different from the regular one.

To adapt the example, replace the sample inputs with the loan’s terms and keep the same layout. For instance, changing B3 to 4 and using a four-year term means 16 quarterly payments; the annual rate is then divided by four. Do not change the payment frequency without also checking that the lender quotes the rate and payment schedule on the same basis. If payments occur on irregular dates, or interest accrues daily, a simple equal-period model may not reproduce the lender’s figures.

Common Mistakes

  • Mixing annual and periodic rates. PMT needs a rate for one payment period. If payments are monthly, using 6% directly instead of 6% divided by 12 produces a much larger payment. Use the periodic-rate cell consistently in both PMT and the interest calculation.
  • Entering the payment count incorrectly. A 30-year monthly loan has 360 payments, not 30. Calculate the count as payments per year multiplied by years, and verify the result.
  • Using inconsistent cash-flow signs. PMT returns a negative value when the loan amount is entered as a positive present value. The example negates PMT to display a positive payment. If you change the sign convention, make sure the payment and principal formulas still agree.
  • Rounding too early or inconsistently. Rounding the displayed payment but not the interest, or rounding some rows differently, can leave a residual balance. Decide whether the model rounds each period to cents and use that rule consistently. Actual lender statements can still differ because of their own rounding and accrual practices.
  • Forgetting that extra charges are separate. Taxes, insurance, origination fees, late charges, and other amounts may change the amount paid but are not automatically included in this principal-and-interest schedule. Add separate columns only if you know how those items are charged.

Tips

  • Keep loan inputs in a small, clearly labeled block above the schedule. This makes it easier to change assumptions without overwriting formulas.
  • Use cell formatting for readability: percentage for the annual and periodic rates, currency for balances and payments, and whole numbers for payment counts. Formatting changes how values appear, not the underlying calculation.
  • Freeze the headings if the schedule is long. In Excel for Windows, select the row below the rows you want to keep visible, then choose View > Freeze Panes > Freeze Panes. On Mac, use the View tab’s Freeze Panes control; menu wording can vary slightly by release.
  • For an easier visual scan, select the schedule and choose Home > Format as Table. A table can add banded rows and filter controls; confirm the total row and formulas still refer to the intended periods.
  • Save a separate copy before changing formulas in a schedule you need to preserve. If you compare the worksheet with lender documents, compare payment dates and balances at the same point in the cycle.

Troubleshooting

The payment looks far too high or too low. Check that B2 contains a percentage such as 6%, not 6, and that B6 divides the annual rate by payments per year. Check B5 too: the example should show 36. If the loan has a balloon payment, variable rate, or nonstandard payment timing, PMT’s level-payment result is not a complete representation of its terms.

The balance increases instead of falling. Review the interest and principal columns. The payment must exceed that period’s interest for principal to be repaid. Check that the payment is positive, interest uses the periodic rate, and the principal formula subtracts interest from the payment. Also verify that each period’s beginning balance links to the preceding ending balance.

The last balance is not zero. Confirm that you filled exactly the required number of rows and that the last payment formula uses MIN to cap the amount due. Check for missing or overwritten formulas, and make sure interest is rounded consistently. A small fraction of a cent can result from different rounding choices; a material balance usually points to an input, reference, or payment-count error. Do not hide a discrepancy by manually typing zero until you understand its source.

You see ####### or a formula error. A row of hash marks usually means the column is too narrow; widen it or choose Home > Format > AutoFit Column Width. If Excel shows a formula error, check for missing parentheses, a misplaced comma, or a cell formatted as Text. Change the cell to General or Number, re-enter the formula, and confirm calculation is set to Automatic under Formulas > Calculation Options.

Frequently Asked Questions

What does an amortization schedule show?
An amortization schedule lists each payment and separates it into interest and principal. It also tracks the loan balance after each payment, making the declining balance and total interest visible.

Can I use this schedule for a mortgage?
You can use the same basic structure for a fixed-rate mortgage with regular payments, but the example does not include property taxes, insurance, fees, or every lender-specific convention. Compare the assumptions with the mortgage documents and lender’s payment schedule.

How do I add an extra payment?
Add an extra-principal input or column, then include that amount in the principal reduction and cap the total principal paid at the beginning balance. Recalculate later interest from the reduced balance. Confirm how the lender applies extra payments; an extra amount may not automatically be treated as principal.

Why does my schedule differ from the lender’s statement?
Possible causes include daily rather than monthly interest accrual, different payment dates, fees, a rate change, or different rounding. Check the lender’s actual terms with the lender or a qualified professional. This spreadsheet is a calculation aid, not financial, tax, investment, or legal advice.

Does the schedule work in Excel for Mac or the web?
The PMT, ROUND, MIN, MAX, and SUM formulas used here are available in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon locations and fill controls can differ slightly, but the worksheet formulas do not need to change.

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.