Excel Payment Plan: Build an Installment Schedule

To make an Excel payment plan, enter the amount borrowed, annual interest rate, and number of monthly payments, then use Excel’s PMT function to calculate the regular installment and a schedule to track interest and the remaining balance. For example, a $1,200 balance at 12% annual interest paid over 12 months has a monthly payment of about $106.62, before fees or other charges.

What an Excel payment plan shows

A payment plan is a schedule of what is due, when it is due, and how each payment affects the amount still owed. A useful spreadsheet distinguishes the payment amount from the principal reduction: when interest applies, part of each installment pays interest and only the remainder reduces the balance.

This is different from simply dividing a purchase into equal, interest-free installments. For a $1,200 purchase with no interest and 12 equal payments, the installment is $100. A loan with the same starting balance and term can cost more because interest is charged as the balance is repaid. The spreadsheet below models a fixed-rate loan with monthly payments and interest calculated monthly. It does not automatically include fees, taxes, late charges, or lender-specific day-count rules.

Set up the inputs

In a blank worksheet, enter these labels and values. The example uses dollars, but the currency can be changed to suit the transaction.

A1: Loan amount; B1: 1200
A2: Annual interest rate; B2: 12%
A3: Number of monthly payments; B3: 12
A4: Regular monthly payment; B4: enter the formula below

With the loan amount in B1, the annual nominal rate in B2, and the number of monthly payments in B3, enter this formula in B4. The leading minus sign makes the calculated payment display as a positive amount:

=-PMT(B2/12,B3,B1)

For the example inputs, the result is approximately $106.62 per month. PMT uses a rate per payment period, so dividing the annual rate by 12 is appropriate for this monthly schedule. The formula assumes payments are made at the end of each month, which is PMT’s default when its optional timing argument is omitted. If a contract uses a different payment frequency or timing, the assumptions need to change.

Build the month-by-month schedule

Starting in row 7, create these headings: A7 Period, B7 Opening balance, C7 Payment, D7 Interest, E7 Principal paid, and F7 Closing balance. The first schedule line is row 8. In the example, the 12 payment rows run from row 8 through row 19.

With the schedule headings in A7:F7 and the first payment on row 8, enter 1 in A8 to mark the first period. With the period number in A8, enter this formula in A9 to number the next period, then fill it down through A19:

=A8+1

With the initial loan amount in B1, enter this formula in B8 for the first opening balance:

=$B$1

With the previous row’s closing balance in F8, enter this formula in B9 for the next opening balance, then fill it down the remaining schedule rows:

=F8

With the opening balance in B8 and the annual rate in B2, enter this formula in D8 for the first month’s interest. Fill it down through D19:

=B8*$B$2/12

With the regular payment in B4, opening balance in B8, and interest in D8, enter this formula in C8. It prevents the scheduled final payment from exceeding the remaining balance plus that period’s interest. Fill it down through C19:

=MIN($B$4,B8+D8)

With the payment in C8 and interest in D8, enter this formula in E8 for the principal portion, then fill it down through E19:

=C8-D8

With the opening balance in B8 and principal paid in E8, enter this formula in F8 for the closing balance, then fill it down through F19:

=MAX(0,B8-E8)

The dollar signs in references such as $B$2 keep the input reference fixed when a formula is copied. On Windows, select a formula cell and use Ctrl+D to fill down into selected cells; on Mac, Control+D is the corresponding Fill Down shortcut in Excel. You can also drag the fill handle at the lower-right corner of a selected cell.

Check what the example means

In the first month, interest is $12: $1,200 multiplied by 12% and divided by 12. The $106.62 payment therefore reduces principal by about $94.62, leaving an opening balance near $1,105.38 for month two. The next month’s interest is about $11.05, because it is calculated on the lower balance, not the original $1,200.

Across 12 payments, the total is about $1,279.42, so total interest is approximately $79.42. These figures are approximate because the displayed payment is rounded to cents; Excel retains more precision internally unless you deliberately round the formulas. Apply a currency or accounting number format from Home > Number to show two decimal places without rounding the underlying calculations. Small differences from a lender’s statement can arise from payment dates, rounding rules, or fees.

Adapt the plan to the agreement

Before relying on the schedule, match its assumptions to the actual arrangement. The example works for a fixed annual rate and equal monthly periods. It is not a complete model of every loan or payment contract.

Interest-free installments: Set the rate to 0% and PMT returns an equal division of the balance over the stated number of periods. Confirm that the agreement really charges no interest or fees.
Different payment frequency: For quarterly payments, use a quarterly rate and count quarterly periods. Dividing an annual rate by four is only suitable when the quoted rate and contract support that convention.
Down payment: Subtract the down payment from the purchase price to determine the financed amount before using PMT.
Extra payments: Add a separate extra-principal column and make sure the extra amount reduces the balance. Check whether the lender permits extra payments without a penalty.
Variable rates or irregular dates: A single PMT result will not represent changing rates or uneven periods. The schedule must reflect each period’s actual terms.

If you add optional payments, be careful not to treat them as ordinary scheduled payments that the lender might apply differently. The order in which a lender allocates money among fees, interest, and principal is governed by the agreement and applicable rules, not by the spreadsheet.

Common spreadsheet problems

A payment that seems too small may result from entering 12 instead of 12% in the rate cell. Excel interprets 12 as 1,200% when used as a rate. Enter 12% or 0.12, and check that the number of periods matches the payment frequency: 12 monthly payments are one year, while 12 annual payments are 12 years.

Another frequent issue is mixing the annual rate with a monthly period. PMT’s first argument must be the rate for one payment period, not necessarily the rate printed as an annual percentage. The example uses B2/12 because it assumes a nominal annual rate divided into monthly periods. Some lenders calculate interest using daily balances or another method, so the spreadsheet’s result may be an estimate rather than the official payoff amount.

Also inspect the balance at the end of the schedule. If it remains above zero, check that the term, rate, and payment formula are consistent and that you filled formulas through the final period. If a payment is less than that period’s interest, the principal portion becomes negative and the balance can grow. That is negative amortization, not a calculation error. The MIN formula only caps an oversized final payment; it does not make an inadequate payment sufficient to repay the debt.

Excel schedule versus a template or lender calculator

A custom worksheet is useful when you want to see how a payment is split or compare different terms using the same assumptions. A ready-made Excel template can save setup time, but review its formulas, period timing, and treatment of extra payments before trusting its outputs. Templates vary, and a polished layout does not guarantee that the assumptions match your contract.

A lender’s calculator or official statement is usually more relevant for a real account because it may use the lender’s specific dates, rate conventions, fees, and rounding. Use your spreadsheet for planning and checking scenarios; do not substitute it for a payoff quote or payment instructions from the lender. Keep a copy of the original inputs and note the rate, payment date, and any assumptions so another person can understand the estimate.

Costs, accuracy, and responsible use

Excel itself does not determine whether a payment plan is affordable or fair. Compare the total amount repaid, not just the installment. A longer term generally lowers the required payment but can increase total interest when interest accrues over more periods. A lower advertised payment may also exclude an upfront fee or depend on a promotional rate that later changes.

For a real commitment, compare the spreadsheet with written terms and confirm the due date, late-payment consequences, rate changes, and how additional payments are applied. Avoid entering sensitive account credentials or payment-card details into a workbook that will be shared. A plan records calculations; it does not make payments, verify a creditor’s balance, or change the terms of an agreement.

Frequently Asked Questions

What Excel formula calculates a monthly loan payment?
Use PMT with the monthly interest rate, number of monthly payments, and amount borrowed. With the amount in B1, annual rate in B2, and term in months in B3, the example uses =-PMT(B2/12,B3,B1). The minus sign displays the payment as a positive number.

Can Excel make a payment schedule with no interest?
Yes. Enter 0% as the interest rate and use the same monthly schedule. The payment is divided evenly across the periods, provided there are no fees or other charges. A contract described as interest-free may still have costs, so check its full terms.

Why does my final payment not bring the balance to exactly zero?
Check that the schedule has the right number of periods, the rate is entered as a percentage, and the payment formula uses the correct periodic rate. Display rounding can also hide a small residual. Do not round each intermediate calculation unless the agreement requires it; use the lender’s payoff figure for an exact settlement.

Can I use the schedule for a credit card or variable-rate loan?
Only as a simplified estimate if you hold the rate and payment assumptions constant. Credit cards and variable-rate loans can use changing rates, daily balance calculations, minimum-payment rules, and fees that this fixed monthly example does not model. Adapt the schedule to the account’s actual method or use its official calculator.

Does an Excel payment plan send payments automatically?
No. It calculates and organizes a schedule only. It does not connect to a lender or transfer money. Set up payments through the lender or an authorized payment service, and verify the amount and due date against the account statement.

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.