How to Create an Amortization Schedule in Excel (PMT + Period Rows)

If you searched how to create amortization schedule in excel, you want formulas — not a sales page. This walkthrough builds a fully amortizing table that ends at zero. It extends the pillar loan amortization schedule Excel without repeating the full free-vs-paid debate.

Context and template options: Loan amortization schedule Excel.

Inputs to place first

Input block

Excel Easy, Indeed, and Wall Street Prep all start from the same idea: convert annual rate to a periodic rate (/12 for monthly) and express nper in periods (years × 12).

Column headers for the schedule

In a header row (e.g. row 8):

1. Period
2. Date (optional — EDATE helps)
3. Beginning balance
4. Payment
5. Interest
6. Principal
7. Ending balance

First payment row formulas

Assume period 1 starts in row 9:

• Period: 1
• Beginning balance: =$B$1
• Payment: =$B$5
• Interest: =C9*($B$2/$B$4) or =IPMT($B$2/$B$4,A9,$B$3*$B$4,-$B$1)
• Principal: =D9-E9 or =PPMT(...)
• Ending balance: =C9-F9

Second row: beginning balance = previous ending balance; period = previous + 1; date = =EDATE(previous,1).

Fill the table

1. Complete row 9 formulas.
2. Build row 10 with carry-forward balance.
3. Select row 10 formulas and fill down for nper rows (e.g. 24 for a 2-year monthly loan).
4. Confirm last ending balance ≈ 0.
5. Sum principal column — equals original loan.
6. Optionally add cumulative interest/principal columns like Indeed's layout.

Worked numbers (illustrative)

On a $20,000 loan at 5% for 2 years monthly, PMT produces a constant installment. Early rows show larger interest shares; later rows show larger principal shares — the pattern Wall Street Prep documents for fixed-rate amortization. Your exact cents depend on rounding; many builders wrap PMT in ROUND(...,2) to match bank statements.

When to stop DIY and use a template

DIY is perfect for learning and short loans. Long mortgages (360 rows), payment options, and yearly summaries get messy. That is when an amortization schedule Excel template or the PlanoNest workbook (one-time purchase; live price on the product page) saves rebuild time. For extras, jump to extra payments.

FAQ

Can I use only PMT without IPMT/PPMT?

Yes — interest = beginning balance × periodic rate; principal = payment − interest. IPMT/PPMT are convenience functions.

Why is my last balance not zero?

Usually rate/nper mismatch, rounded payment without adjusting the final row, or missing absolute references ($B$2).

Does this cover balloon loans?

No — balloons leave a residual. Keep this method for fully amortizing loans.

Next step

Build the sample sheet once, save it as your personal template, or start from a structured file on the product page. Return to the pillar for cluster navigation.

Practical appendix: keep the schedule honest

Before you share a file with a partner or lender, run three checks that catch most spreadsheet mistakes. First, pick any middle row and confirm interest plus principal equals the payment within a cent. Second, sum the principal column and confirm it matches the original loan amount after rounding. Third, confirm the final ending balance is zero — or that the last payment is explicitly adjusted for leftover cents. These checks take under two minutes and prevent the most common looks-right-audits-wrong failures on a loan amortization schedule.

When you change assumptions, change only one input at a time and write the scenario name in a Notes cell (for example, Quote B at 6.25 percent for 60 months). That habit matters more than fancy charts. If you maintain both a baseline and an extra-payment version, keep them on separate sheets rather than overwriting the only copy you have. Offline Excel files still win for quote archives: you can reopen Tuesday numbers next to Thursday numbers without hoping a web form still remembers your session.

Rounding policy belongs in the Help area of any serious workbook. Some teams round each payment to two decimals and adjust the final row; others keep full precision until export. Either approach works if it is documented. What does not work is mixing both styles inside the same column. The same discipline applies to day-count: if your lender uses a 365-day basis, do not silently divide by 12 and assume the statement will match. Document the basis next to the rate input so future-you does not fix a discrepancy that is actually a convention mismatch.

Finally, remember the scope of this cluster: standard fully amortizing loans that reach a zero balance. If your term sheet includes a balloon residual, interest-only period, or revolving credit line, switch models instead of bending this schedule. The right spreadsheet is the one whose last row matches the contract — not the one with the prettiest chart.

Scenario discipline for recurring use

Treat every saved workbook as a decision log. Name the file with the borrower or project label, the rate lock date, and the term length so you do not open the wrong quote during a call. Keep a one-line changelog at the top of the Notes sheet: who changed the rate, when, and why. If two people edit the same schedule, protect the input cells and leave the period table unlocked for review only.

When you compare lenders, duplicate the entire input block rather than editing in place. Side-by-side sheets make total interest differences obvious without mental math. Export a PDF snapshot of the schedule summary when you accept a quote so you can prove what you modeled if the closing disclosure drifts. These habits are boring — and they are how spreadsheet users avoid expensive surprises on fully amortizing loans.

Closing checklist before you rely on the numbers

Print or PDF the first page of inputs plus the final dozen period rows. Confirm the product type is a standard amortizing loan, not a balloon or revolving line. Confirm payment frequency matches the contract. Confirm there is no hidden fee row pretending to be principal. If extras exist, confirm they sit in their own column. If yearly summaries exist, confirm they sum from the period table rather than hard-coded totals. When those checks pass, the schedule is ready to guide a conversation — still not a substitute for the signed note.

Reusable tip: store the lender quote ID beside the rate cell, and freeze the input pane so period rows stay aligned while you scroll a long schedule. Small hygiene habits compound when you revisit the same loan after a refinance conversation or a rate lock extension.

Reusable tip: store the lender quote ID beside the rate cell, and freeze the input pane so period rows stay aligned while you scroll a long schedule. Small hygiene habits compound when you revisit the same loan after a refinance conversation or a rate lock extension.

Reusable tip: store the lender quote ID beside the rate cell, and freeze the input pane so period rows stay aligned while you scroll a long schedule. Small hygiene habits compound when you revisit the same loan after a refinance conversation or a rate lock extension.

Reusable tip: store the lender quote ID beside the rate cell, and freeze the input pane so period rows stay aligned while you scroll a long schedule. Small hygiene habits compound when you revisit the same loan after a refinance conversation or a rate lock extension.