How to Build an Amortization Schedule in Excel — Free CSV Export

Direct Answer

Last verified on: June 28, 2026

Editorial note: Excel function names match Microsoft Excel and Google Sheets. Apple Numbers uses slightly different syntax. Escrow, private mortgage insurance (PMI), and fees are not part of the core PMT formula.

Research method: Daily Calcs verified PMT/IPMT/PPMT output against the online Amortization Calculator for a $250,000 loan at 6.5% over 360 months. Microsoft function documentation reviewed June 28, 2026.

Method 1: Export a CSV (Fastest)

1. Open the Amortization Calculator.
2. Enter your loan amount, interest rate, and term.
3. Click Download CSV (or Print PDF for a clean copy).
4. Open the CSV in Excel or Google Sheets.

You now have a complete schedule. To turn it into a reusable template, save it as .xlsx and add a chart of principal versus interest over time.

Why CSV first: A 30-year loan has 360 rows. One wrong cell reference in row 47 breaks every row below it. Exporting validates the math before you customize.

Method 2: Build It with Formulas

Set up these columns and fill the formulas down for every month:

For a $250,000 loan at 6.5% over 30 years, =PMT(0.065/12, 360, -250000) returns about $1,580 per month.

Worked example: first three payments

Total interest over 360 payments: about $318,861 on this loan.

Adding Extra Payments

Add an Extra column, then subtract both the scheduled principal and the extra principal from the balance each row:

New balance = Previous balance - PPMT portion - Extra payment

Because interest is charged on the prior balance, extra principal compounds into a shorter payoff. Add a summary cell totaling interest paid so you can compare scenarios — the same logic the online calculator runs automatically.

Worked Example: Building a 30-Year Schedule Step by Step

Follow this walkthrough for a $250,000 loan at 6.5% over 360 months — the same baseline used throughout Daily Calcs amortization guides.

Step 1 — Set up input cells. In A1 enter 250000 (principal), B1 enter 0.065 (annual rate), C1 enter 360 (months). Label each cell so you can change scenarios later.

Step 2 — Calculate the level payment. In D1: =PMT(B1/12, C1, -A1). Result: $1,580.17. This is your fixed monthly payment for every row.

Step 3 — Build row 1 (period 1). Column A: 1. Column B (Interest): =IPMT($B$1/12, A2, $C$1, -$A$1). Column C (Principal): =PPMT($B$1/12, A2, $C$1, -$A$1). Column D (Balance): =$A$1 - C2. Row 1 should show $1,354.17 interest, $226.00 principal, balance $249,774.

Step 4 — Fill down 360 rows. Increment period numbers in column A. Copy B, C, and D formulas through row 361. Lock $B$1, $C$1, and $A$1 with absolute references so each row references the correct period number.

Step 5 — Validate against the calculator. Open the Amortization Calculator, enter the same inputs, and compare month-1 payment and month-360 balance (should be $0). If they differ by more than a few cents, check that principal is negative in PMT and that you divided the annual rate by 12.

Step 6 — Add extras (optional). Insert an Extra column. Change the balance formula to: =previous_balance - principal - extra. A $100 extra in month 1 drops the month-2 interest charge because the balance starts lower — the same compounding effect shown in the table above.

Export a CSV from the calculator first if you want a verified baseline before customizing charts, escrow columns, or PMI rows for a full mortgage model.

When to Use Which Method

Excel Build Checklist

• Enter annual rate in a single cell; divide by 12 in formulas
• Use negative principal in PMT so the payment displays positive
• Lock rate, term, and principal cells before filling down 360 rows
• Cross-check month-1 payment against the Amortization Calculator
• Keep escrow and PMI in separate columns for mortgages
• Add a SUM() row for total interest paid

Calculator Methodology

The exported CSV and PMT function both use:

Payment = P × r(1 + r)^n / ((1 + r)^n - 1)

Assumptions: Fixed rate, monthly payments, no skipped payments.

Limitations: Excel models do not replace lender amortization if your servicer uses different rounding or daily accrual. Variable-rate loans need a different structure.

Related Reading

• Amortization Formula Explained — the math behind PMT
• What Is Amortization? — the underlying concept
• Amortization Schedule Example — a worked walkthrough
• Extra Mortgage Payment Savings Report — extra payment data

Official and Supporting Sources

• Microsoft — PMT function
• Consumer Financial Protection Bureau — Understanding amortization

Next Step

Run your loan in the Amortization Calculator, download the CSV, or use the payment amount to validate your Excel PMT formula.