Amortization Schedule Excel: Build One Step by Step

How-to basics · Updated November 21, 2026

To build an amortization schedule in Excel, enter the loan amount, annual interest rate, term, and payments per year, calculate the periodic payment with PMT, then track interest, principal, and balance for each payment. With the loan amount in B1, annual rate in B2, term in years in B3, and payments per year in B4, the payment formula is =-PMT(B2/B4,B3*B4,B1).

What an amortization schedule shows

An amortization schedule breaks a loan into its payment periods and shows how each payment is divided between interest and principal. It also tracks the unpaid balance after each payment. In a typical fixed-rate, fully amortizing loan, the scheduled payment stays level while the interest portion generally declines and the principal portion generally grows.

This is useful for understanding the timing of repayment, estimating total interest under the stated assumptions, and seeing how an additional principal payment might change the payoff path. A schedule is a calculation model, not a lender statement: actual balances can differ because of payment dates, fees, rate changes, rounding policies, or contract terms.

Set up the loan inputs

In a new workbook in Excel for Microsoft 365 on Windows, label column A and enter values in column B. Use a consistent annual-rate convention and enter the rate as a percentage, such as 6%, rather than 6. For a simple monthly fixed-rate example, use these inputs:

  • B1: Loan amount, such as 200000.
  • B2: Annual interest rate, such as 6%.
  • B3: Term in years, such as 30.
  • B4: Payments per year, such as 12 for monthly payments.
  • B5: Periodic payment, calculated with PMT.

Format B1 and B5 as currency, B2 as a percentage, and B3 and B4 as numbers. Keep full precision in the cells and use number formatting to show cents; rounding every calculation to two decimals can create a small residual balance near the end of a long schedule.

With the inputs in B1:B4 as described above, enter this formula in B5: =-PMT(B2/B4,B3*B4,B1). For a $200,000 loan at 6% annual interest over 30 years with monthly payments, it returns approximately $1,199.10 per month. The minus sign makes the displayed payment positive because Excel treats money paid out and money received as opposite cash-flow signs.

Build the payment-by-payment table

Starting in row 10, add headings: Period, Beginning Balance, Payment, Interest, Principal, and Ending Balance. Put the first period number, 1, in A11. In the first schedule row, the beginning balance is the original loan amount. Interest for the period is the beginning balance multiplied by the periodic rate. The principal portion is the payment less interest, and the ending balance is the beginning balance less principal.

With B1 holding the original loan amount and B11 as the first schedule row, enter =$B$1 in B11 for the beginning balance. The dollar signs keep the input reference fixed when the formula is copied.

With B11 as the period’s beginning balance, B2 as the annual rate, and B4 as payments per year, enter =B11*$B$2/$B$4 in D11 to calculate interest. In the example, the first month’s interest is $1,000: $200,000 multiplied by 6% and divided by 12.

With B5 as the regular payment, B11 as beginning balance, and D11 as interest, enter =MIN($B$5,B11+D11) in C11. This uses the normal payment in ordinary periods but reduces the final payment if only a smaller amount is needed to clear the remaining balance and interest.

With C11 as the payment, D11 as interest, and B11 as the beginning balance, enter =MIN(C11-D11,B11) in E11 to calculate principal. For the example’s first payment, principal is approximately $199.10, so the ending balance is approximately $199,800.90.

With B11 as beginning balance and E11 as principal, enter =MAX(0,B11-E11) in F11. MAX prevents a tiny negative balance caused by calculation precision. In row 12, enter =A11+1 in A12 and =F11 in B12. Copy the formulas in C11:F11 down as well. Select the completed formula row and use Home > Fill > Down, or drag the fill handle, to extend the schedule through period 360, row 370.

The first payment therefore contains $1,000 of interest and about $199.10 of principal, with the small difference in the displayed balance resulting from rounding on screen. The next period’s interest is calculated on the lower balance, not on the original $200,000.

Check totals and interpret the example

For this example, the schedule has 360 monthly periods. The exact payment returned by PMT is slightly more than $1,199.10, although the cell formatted as currency displays $1,199.10. Multiplying the unrounded payment by 360 gives total scheduled payments of about $431,676, or roughly $231,676 above the original principal. That difference represents modeled interest, not necessarily every cost of borrowing; fees and other charges are not included unless you add them separately.

With the schedule’s interest values in D11:D370, calculate modeled total interest in a summary cell using =SUM(D11:D370). With scheduled payments in C11:C370, calculate the total scheduled payments using =SUM(C11:C370). The second result should be close to the payment amount multiplied by 360, with any difference explained by the final-payment adjustment and displayed rounding. Check that the ending balance in F370 is zero and that beginning balances decline rather than rise in this fixed-rate example.

Adapt the schedule to payment frequency and dates

The periodic rate and number of periods must match the payment frequency. For monthly payments, divide the annual rate by 12 and multiply the term in years by 12. For quarterly payments, use 4; for annual payments, use 1. Enter the correct value in B4 and the PMT formula above uses it for both calculations. This assumes the annual rate is nominal and is divided evenly across periods, as is common in basic loan examples. A lender may use a different rate convention or day-count method, so the workbook may not reproduce its statement exactly.

To show calendar due dates, add a payment-date column and generate dates from the first due date using Excel’s EDATE function, which advances a date by a specified number of months. Monthly dates can behave differently around month ends, and real lenders may set due dates or adjust for weekends and holidays under their own rules. A calendar column does not alter the interest calculation unless you build a separate day-based model.

PMT’s optional type argument distinguishes payment timing: 0 means payments at the end of each period, the usual assumption for a standard loan schedule, while 1 means payments at the beginning. Do not change that setting simply to make a result match; confirm the timing in the actual loan terms.

Model optional extra principal payments

An extra principal payment can reduce the balance sooner and may reduce later interest, but the effect depends on when the lender applies it and how the loan handles early repayment. To model extras, add an input column for extra principal and another for applied extra principal. Keep scheduled principal separate from the extra so the table remains easy to audit.

For example, put the intended extra payment in H11, and use the existing B11 beginning balance and E11 scheduled principal. In F11, calculate extra principal actually applied with =MIN(H11,MAX(0,B11-E11)). Then, with B11, E11, and F11 as the first-row balance amounts, update the ending-balance formula in G11 to =MAX(0,B11-E11-F11). Set H11 to zero in periods without an extra payment, and use the new ending-balance column as the following period’s beginning balance. The applied extra is capped so the model does not pay more principal than remains after scheduled principal.

In this version, total cash paid in a period is the scheduled payment plus the extra principal actually applied. If you compare total payments, include both columns; the scheduled-payment total alone will omit the extra amounts. Confirm whether the lender accepts additional principal payments and whether there are limits, fees, or instructions needed to apply them as principal.

Common errors and ways to check the workbook

  • Entering 6 instead of 6%: A value of 6 means 600% when used as a rate. Enter 6% or 0.06.
  • Using the annual rate as the monthly rate: For the simple monthly model, use the annual rate divided by 12 in each period.
  • Reversing cash-flow signs: PMT may return a negative number if the principal and payment signs use the same direction. The leading minus sign in the example displays the payment as positive.
  • Rounding formulas prematurely: Avoid wrapping every calculation in ROUND unless the loan terms require that method. Displaying two decimals is not the same as rounding the stored value.
  • Copying the wrong balance reference: Each row’s interest must refer to that row’s beginning balance, and the next row must begin with the previous row’s ending balance.
  • Assuming the model includes every borrowing cost: The basic schedule covers principal and interest only. It does not automatically include fees, insurance, escrow, taxes, or variable-rate changes.

A useful audit is to verify that each row’s beginning balance equals the prior row’s ending balance, and that payment equals interest plus principal when there is no extra principal. The last balance should be zero or very close to zero. If it is not, check the period count, rate input, copied formulas, and whether the final row corresponds to the last payment.

Excel, lender calculators, and other options

Excel is useful when you want to inspect every period, change assumptions, add charts, or compare scenarios in one workbook. A lender’s calculator or statement is more authoritative for its own payment conventions, rate adjustments, fees, and payment application. A basic spreadsheet is especially suitable for a fixed-rate loan with regular payments; adjustable rates, irregular payment dates, deferred interest, or complex fees require more detailed assumptions.

Excel for Mac uses the same PMT function and worksheet formulas. Ribbon labels and some interactions can vary slightly by platform, but the calculation structure is the same. Excel for the web can also calculate these formulas, though its ribbon layout and fill interactions may differ from the Windows desktop version. If sharing a workbook, make input cells distinct from formula cells and label the assumptions so another reader can identify what the estimate includes.

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. For monthly payments, those are commonly the annual rate divided by 12, the number of years multiplied by 12, and the principal. The sign of the result depends on how cash inflows and outflows are represented.

Does an amortization schedule include taxes and insurance?
Not in the basic principal-and-interest schedule described here. Taxes, insurance, association charges, and lender fees are separate amounts unless you add them as additional columns or inputs. They do not automatically change the loan’s amortization math.

Why does my final balance show a few cents?
Small residuals can result from rounding displayed values, rounding formulas, or copying a schedule for the wrong number of periods. Keep full precision in calculations, verify the period count and rate, and consider adjusting the final payment to the remaining balance plus that period’s interest.

Can I use this schedule for a variable-rate loan?
Only as a simplified model if you update the rate and recalculate payments according to the loan’s actual reset rules. A single fixed-rate PMT calculation does not represent future rate changes, payment caps, or lender-specific recalculation methods.

How do I calculate total interest in Excel?
Sum the schedule’s interest column for all payment rows. For a fixed-rate loan, the total is also related to total payments minus principal, but summing the interest rows makes the assumptions and period-by-period result easier to inspect.

Use the schedule as an estimate

A well-built Excel schedule makes the relationship between payment, interest, principal, and remaining balance visible, and it is straightforward to adapt for a different term or payment frequency. Treat the result as an estimate based on the inputs, not as financial, tax, investment, or legal advice. Check the actual loan documents and payment details with the lender, or consult a qualified professional when you need an authoritative calculation.

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.