Excel Payment Schedule: Build One With Formulas

Formulas & functions · Updated November 18, 2026

To build a basic loan payment schedule in Excel, enter the annual interest rate, number of monthly payments, and loan amount, then calculate the fixed payment with PMT. For example, with the annual rate in B2, payment count in B3, and loan amount in B4, put this in B5: =ROUND(-PMT(B2/12,B3,B4),2). A table can then show each due date, payment, interest, principal paid, and remaining balance.

What an Excel payment schedule shows

A payment schedule lays out expected payments over time. For a loan, it usually separates each installment into interest and principal, then tracks the balance left after the payment. This is useful because a fixed payment does not mean the same amount goes to interest every month. When the balance falls, the interest portion generally falls too, and more of each later payment goes toward principal.

The same spreadsheet structure can be adapted for other recurring cash flows, such as planned invoice payments or savings contributions, but the formulas and assumptions differ. The example here is a fixed-rate loan with monthly payments at the end of each period. It is an illustration, not a lender quote: actual loan terms may include fees, different compounding rules, irregular dates, or other conditions.

Set up the inputs and columns

In a new workbook, enter clear labels in column A and values in column B. Use this layout: A2 Annual interest rate, B2 6%; A3 Number of monthly payments, B3 24; A4 Loan amount, B4 12000; A5 Monthly payment, B5 calculated by the formula above. Format B2 as a percentage and the money values as currency. The example assumes 24 monthly installments on a $12,000 loan at a nominal annual rate of 6%.

Starting in row 10, add these headers: A10 Period, B10 Due date, C10 Opening balance, D10 Payment, E10 Interest, F10 Principal, and G10 Closing balance. Row 11 is the first payment period. Type 1 in A11 and enter the actual first due date in B11. Use the lender’s stated due date rather than assuming the loan starts on the first of a month.

Keep the inputs separate from the schedule, and use descriptive headers. That makes it easier to review assumptions, change the loan amount or term, and spot a formula that has been overwritten. For a real loan, confirm whether the quoted rate is annual, whether payments are monthly, and whether the payment is due at the beginning or end of each period.

Calculate the payment, interest, and balance

In this layout, with B2 holding the annual rate and B3 the number of monthly payments, the B5 payment formula is =ROUND(-PMT(B2/12,B3,B4),2). PMT returns a negative amount when the present value is entered as a positive loan balance, so the minus sign displays the payment as a positive number. ROUND limits the scheduled payment to cents. For these example inputs, the result is $531.85 per month.

For the first schedule row, with the loan amount in B4 and the first opening balance in C11, enter =$B$4 in C11. With the annual rate in B2 and a monthly period, enter =ROUND(C11*$B$2/12,2) in E11 to calculate first-month interest. It returns $60.00: $12,000 multiplied by 6% and divided by 12.

With the payment in B5 and the current opening balance and interest in C11 and E11, enter =MIN($B$5,C11+E11) in D11. This uses the regular payment unless the remaining balance plus that period’s interest is smaller, which helps prevent an oversized final installment. With the example inputs, the first payment is $531.85.

With the payment in D11 and interest in E11, enter =D11-E11 in F11 for the principal portion. The first month therefore pays $471.85 toward principal. With the opening balance in C11 and principal in F11, enter =MAX(0,C11-F11) in G11. The first closing balance is $11,528.15. The MAX function prevents a small negative balance caused by rounding from appearing in the schedule.

These calculations show the logic: payment equals interest plus principal, and the closing balance is the opening balance less principal repaid. Interest is calculated on the opening balance for this simple monthly model. A lender may calculate interest using daily balances or another convention, so the spreadsheet is not necessarily an exact reproduction of a statement.

Fill dates and copy the schedule

For the second row, with period 1 in A11, enter =A11+1 in A12. With the first due date in B11, enter =EDATE(B11,1) in B12 to advance the date by one calendar month. EDATE preserves the monthly cadence more reliably than adding a fixed number of days, though dates near the end of a month can shift to that month’s final valid day.

With the previous closing balance in G11, enter =G11 in C12 for the next opening balance. Copy the formulas in D11:G11 down through the remaining payment rows; relative references adjust to each row, while the dollar signs keep the input references fixed. Copy the period and date formulas down as well. In Excel for Microsoft 365 on Windows, dragging the fill handle or copying and pasting the cells works; the same worksheet formulas work in Excel for Mac and Excel for the web.

For this example, fill through period 24. Check that the closing balance reaches zero and that the last payment is no larger than the amount owed. Because the payment and interest are rounded to cents, the final installment may differ slightly from earlier ones. The MIN formula caps it at the remaining balance plus interest rather than forcing every row to show the same amount.

Make the schedule easier to review

Format due dates as dates and money columns as currency with two decimal places. Select the schedule and use Home > Format as Table if you want filter buttons and consistent row formatting; choosing a table style does not change the financial calculations. Freeze the header row if the schedule is long, and consider adding a clear note above it with the rate, term, and date assumptions.

Useful review checks include:

  • Confirm the first opening balance equals the original loan amount.
  • Confirm every payment is the sum of that row’s interest and principal.
  • Confirm each new opening balance matches the previous closing balance.
  • Confirm the closing balance does not become materially negative and reaches zero by the final scheduled period.
  • Compare the payment amount and due dates with the lender’s disclosure or payment information.

For an optional interest total, with interest amounts in E11:E34, put =SUM(E11:E34) in a summary cell. For this illustrative schedule, the exact total depends on the cent-rounded interest and final payment produced by the rows; use the workbook’s result rather than treating a hand-rounded estimate as an exact lender figure. You can also sum the payment column to see projected cash paid, but that total does not include fees unless you add them separately.

Fixed payments, extra payments, and changing terms

PMT is suited to a fixed rate and equal-length payment periods. If the interest rate changes, the original payment formula does not automatically model the lender’s reset rules. If a borrower makes extra principal payments, add a separate extra-payment column and subtract that amount from the balance, while checking whether the loan permits the payment and whether it changes the payment amount or shortens the term. For a schedule intended to match a contract, use its actual rules and dates.

A payment made at the beginning of each period is a different assumption from the end-of-period example. PMT includes an optional type argument: 0 means payment at period end, while 1 means payment at period beginning. Do not change this setting simply to make the result look closer; use the timing specified in the agreement. Similarly, dividing an annual rate by 12 is an assumption for a nominal annual rate with monthly periods, not a universal method for every product.

Common errors and how to fix them

A frequent mistake is entering 6 instead of 6% or 0.06 in the rate cell. That makes the calculation use 600% annually. Another is mixing an annual rate with a monthly payment count without converting the rate to a monthly rate. The example divides B2 by 12, but a daily accrual method or a different compounding basis needs a model that reflects those terms.

Sign conventions can also make a correct PMT result appear negative. The minus sign in the example is intentional: the loan amount is treated as money received and the installment as money paid. If you instead enter the loan amount as a negative value, do not also negate PMT without considering the resulting sign.

If the final balance is a few cents below zero, check that interest is rounded consistently and that the last payment is capped. If a balance remains after the last row, confirm the schedule has the correct number of periods, the input rate and term are right, and formulas were copied to every row. Do not hide a larger mismatch by forcing the last balance to zero; investigate the assumptions first.

When a template or another tool makes sense

A built-in or downloaded amortization template can save setup time, but inspect its formulas and assumptions before relying on it. Confirm the rate frequency, payment timing, date handling, treatment of extra payments, and final-row logic. A template with annual periods, for example, will not match a monthly repayment plan merely because the total term is the same.

A hand-built worksheet is useful when you need to expose assumptions or change columns, while a lender’s official schedule is the reference for contractual amounts. For complex loans, irregular payment dates, variable rates, balloon payments, or fees, ask the lender for its calculation method or have the model reviewed by a qualified professional. This spreadsheet is for understanding and organizing projected payments, not financial, tax, investment, or legal advice.

Frequently Asked Questions

Does Excel have a payment schedule template?
Excel may offer templates through its template gallery, and Microsoft’s available templates can change. A template is a starting point, not proof that its assumptions match a particular loan. Check the formulas and terms, or create a transparent schedule using the layout above.

What Excel formula calculates a monthly loan payment?
PMT calculates a fixed payment when the rate and number of periods are consistent. Convert an annual rate to a monthly rate for monthly periods, enter the total number of monthly payments, and check whether payment timing is at the beginning or end of each period.

Why does PMT show a negative number?
Excel uses cash-flow signs. If the amount borrowed is positive, the payment is commonly returned as negative because it represents money paid out. Negating the PMT result displays the installment as a positive number, as in the example.

How do I calculate interest for each payment?
For the simple monthly model, multiply the opening balance by the annual rate divided by 12, then round to cents if the schedule is designed to track cent-based payments. The lender’s method may differ, especially if it accrues interest daily.

Can I use this schedule for a mortgage or car loan?
The same amortization structure can illustrate a fixed-rate loan, but it may omit escrow, insurance, fees, daily interest, or other contractual details. Compare it with the lender’s official figures and confirm the real terms with the lender or a qualified professional.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.