Excel Payment Chart: Build a Loan Schedule and Graph

An Excel payment chart is usually a loan amortization schedule paired with a graph showing how payments are split between interest and principal, or how the balance falls over time. In Microsoft Excel for Microsoft 365 on Windows, enter the loan details, calculate the payment with PMT, build a period-by-period table, and chart its principal, interest, or remaining-balance columns.

What an Excel payment chart shows

A chart is the visual summary; the schedule underneath it contains the actual calculations. For a typical fixed-rate loan, each regular payment covers interest accrued since the previous payment and pays down some principal. Early payments generally contain a larger interest share because the outstanding balance is higher. As the balance declines, the interest portion usually shrinks and the principal portion grows.

A useful workbook keeps these pieces distinct:

Inputs: starting loan amount, annual interest rate, payment frequency, and term.
Schedule: each period’s opening balance, payment, interest, principal repaid, and ending balance.
Chart: a visual comparison of principal and interest, or a line showing the balance over time.

This setup is different from a chart of bills paid by date or household spending. If by payment chart you mean a record of transactions, enter payment dates and amounts in a list and chart those amounts instead. The loan example below addresses the common meaning: a payment schedule for a loan with regular payments.

Set up the loan inputs

Start with a blank worksheet and enter labels in column A and values in column B. Use this illustrative loan: a principal of $20,000, an annual interest rate of 6%, monthly payments, and a three-year term. Format the rate as a percentage and the loan amount as currency. Enter the rate as 6%, not 6, so Excel interprets it as 0.06.

A2: Loan amount; B2: 20000
A3: Annual interest rate; B3: 6%
A4: Payments per year; B4: 12
A5: Term in years; B5: 3
A6: Regular payment; B6: formula below

With the loan amount in B2, annual rate in B3, payments per year in B4, and term in years in B5, enter this in B6. It returns approximately $608.44 per month for the example, before any fees or lender-specific rounding.

=-PMT(B3/B4,B4*B5,B2)

PMT requires a rate per payment period and a total number of periods. Here, 6% divided by 12 is 0.5% per month, and 12 payments per year times three years is 36 payments. The minus sign makes the result positive because the loan amount is entered as a positive value. Excel’s financial functions use cash-flow signs: money received and money paid are opposite directions.

Build the payment schedule

Place these headings in row 10: A10 Period, B10 Opening balance, C10 Payment, D10 Interest, E10 Principal, and F10 Ending balance. Row 11 will be the first month. Formulas below use the input locations just described, with the schedule headings in A10:F10 and the first calculation row in row 11.

With the period number in A11 and the first payment period represented by 1, enter this in A11. It returns 1.

=1

With the loan amount in B2 and the first schedule row in row 11, enter this in B11. It returns an opening balance of $20,000.

=$B$2

With the regular payment in B6, the current opening balance in B11, and the monthly rate calculated from B3 and B4, enter this in C11. It returns about $608.44 for the first period; the MIN function also prevents the final payment from exceeding the amount still owed plus that period’s interest.

=MIN($B$6,B11+D11)

With the opening balance in B11 and the annual rate and payment frequency in B3 and B4, enter this in D11. It returns $100.00 of interest for the first month: $20,000 multiplied by 0.06 divided by 12.

=B11*$B$3/$B$4

With the payment in C11 and interest in D11, enter this in E11. It returns about $508.44 of principal for the first payment, calculated as the payment less interest.

=C11-D11

With the opening balance in B11 and principal repaid in E11, enter this in F11. It returns an ending balance of about $19,491.56 for the first month. MAX prevents a tiny negative balance caused by floating-point rounding.

=MAX(0,B11-E11)

For the next row, with period 1 in A11, enter this in A12; it returns 2. With the prior ending balance in F11, enter the next formula in B12; it returns the prior row’s ending balance, about $19,491.56.

=A11+1

=F11

Copy C11:F11 down alongside the period and opening-balance formulas until row 46, giving 36 payment periods in rows 11 through 46. To fill a range in Excel for Windows, select the formula cells and drag the fill handle down, or copy the first row and paste it into the remaining rows. With the example’s unrounded payment, the schedule totals about $21,903.79 in payments and $1,903.79 in interest over the full term. The exact display can vary with number formatting; the lender’s payment and rounding rules may also produce a slightly different final payment.

Create the chart in Excel

A stacked column chart makes the changing composition of payments easy to see. In the completed schedule, select the period values in A10:A46, then hold Ctrl and select the principal and interest columns E10:E46 and D10:D46. On the Windows ribbon, choose Insert > Insert Column or Bar Chart > Stacked Column. If selecting non-adjacent ranges is inconvenient, copy Period, Interest, and Principal into three adjacent columns and select that smaller table before inserting the chart.

Give the chart a descriptive title, such as Monthly Principal and Interest. Check that Period appears along the horizontal axis and that Interest and Principal appear as the two stacked series. If the chart has the wrong orientation, use Chart Design > Switch Row/Column. Use clear currency formatting in the schedule, and avoid adding more series than the reader needs to compare.

To chart the outstanding balance instead, select A10:A46 and F10:F46, then choose Insert > Insert Line or Area Chart > Line. This creates a line that should slope downward toward zero. A balance line often communicates payoff progress more clearly than a column chart, while stacked columns are better for comparing principal and interest in each payment.

Excel for Mac offers the same general chart types and formula approach, though ribbon labels and layout can differ slightly by release. Excel for the web also supports these basic formulas and charts, but the available controls and selection behavior may differ from the desktop ribbon. The worksheet structure remains the same.

Choose a chart that answers a question

Pick the visual according to what someone needs to understand, rather than adding every possible chart. A stacked column chart answers how payment composition changes over time. A line chart answers how quickly the balance is paid down. A simple table may be more useful when the main task is checking the due amount or looking up a particular month.

Interest versus principal: use stacked columns with Period, Interest, and Principal.
Balance over time: use a line chart with Period and Ending balance.
Total payment consistency: use a line chart of Period and Payment, especially if the payment varies.
Comparing several loans: make separate balance series only when the periods and units are comparable, and label each loan clearly.

For a fixed-rate loan with level payments, the payment amount is nearly constant, so a chart of total payment alone may tell the reader little. A principal-and-interest chart reveals the changing split, and a balance line reveals payoff progress. If payments vary because of an adjustable rate, extra payments, or irregular timing, record those changes in the schedule rather than assuming every period is identical.

Check assumptions and interpret the numbers

The example assumes a fixed annual nominal rate, monthly periods, equal payments made at the end of each period, no fees, and no extra repayments. Those assumptions matter. A quoted rate may not be the rate used for the lender’s payment calculation, and payment timing, compounding conventions, fees, and rate changes can affect the actual figures. Check the loan agreement or ask the lender for the applicable terms before treating a spreadsheet estimate as a statement of what is owed.

In this example, the first month’s interest is $100 because the calculation uses 0.5% of $20,000. The remaining roughly $508.44 of the $608.44 payment reduces principal. In later months, interest is calculated on a smaller opening balance, so more of a level payment goes toward principal. Small differences between the workbook and a lender statement can arise from cents-level rounding, payment dates, or the lender’s calculation method.

Currency formatting changes how values look, not the underlying stored numbers. Avoid rounding every intermediate calculation unless the loan’s actual terms require it. If you round each interest and principal value to cents, the sum of rounded rows may differ slightly from totals calculated with full precision. For a real schedule, compare the final balance and payment dates with the lender’s own figures.

Common problems and fixes

Payment is negative: this is often a cash-flow sign convention issue. With a positive loan amount in B2, the example uses a minus sign before PMT so the payment displays as positive.
Payment is much too large or small: check that B3 contains 6%, not 6, and that B4 contains 12 for monthly payments. PMT needs a rate per payment period, not the annual rate when payments are monthly.
The balance does not reach zero: verify that the schedule has the correct number of rows and that formulas were filled down without changing the absolute input references. A small residual may also reflect rounding or terms different from the assumptions.
The chart shows periods as data series: make sure the period column is the category axis and use Chart Design > Switch Row/Column if Excel plotted the data in the wrong direction.
The final balance turns slightly negative: use the MAX formula shown for the ending balance, and consider the MIN payment formula to cap the final payment at the amount due.

When a different Excel method is better

For a standard fixed-rate loan, the row-by-row schedule is transparent and easy to audit. Excel also has IPMT and PPMT functions for calculating the interest and principal portion of a payment in a chosen period. They can be useful when you only need selected periods, but a full schedule is often easier to chart because every period is visible in the table.

A transaction chart for rent, invoices, or household bills needs a different data model: list each actual date, payment amount, and perhaps category or status. A loan amortization schedule predicts allocations from assumptions; a transaction list records events that happened. Mixing forecast values and actual payments in the same chart without labels can mislead readers.

For a quick starting point, Microsoft Excel includes templates accessible from File > New; search for loan or amortization templates if available in your installation. Review a template’s formulas, assumptions, and dates before relying on it. A template is a convenience, not confirmation that its method matches a particular loan contract.

Frequently Asked Questions

How do I calculate a monthly loan payment in Excel?
Divide the annual interest rate by 12 and multiply the number of years by 12 for the number of monthly payments. With principal in B2, annual rate in B3, and term in years in B5, use =-PMT(B3/12,B5*12,B2). This assumes monthly payments and a fixed rate; confirm actual terms with the lender.

How do I make an Excel chart showing the loan balance?
Put each period in one column and its ending balance in another. Select those two columns, then choose Insert > Insert Line or Area Chart > Line. The balance series should trend downward toward zero if the schedule covers the full payoff period.

Can Excel show principal and interest separately?
Yes. Add separate Interest and Principal columns to the amortization schedule, then chart those columns as a stacked column chart. Interest for each period is opening balance multiplied by the periodic rate; principal is payment minus interest.

Why does my Excel payment schedule end with a small balance?
Possible causes include rounding each row, an incorrect payment count or periodic rate, or assumptions that differ from the lender’s. Keep full precision in calculations, check that all periods are included, and compare the result with the lender’s schedule. A small difference is not proof that the lender’s figure is wrong.

Can I use an Excel payment chart for weekly or biweekly payments?
Yes, but change the payment frequency and make the rate and number of periods consistent with the loan’s actual terms. Do not automatically assume that dividing an annual rate by 52 or 26 matches the lender’s method; confirm the rate conversion, payment dates, and compounding convention first.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

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

Spotted an error in this guide? Let us know.