To create an amortization schedule, you need three numbers from your loan agreement — the principal, the contract interest rate, and the total number of payments — one payment formula, and a table with six columns that tracks each month’s interest, principal, and remaining balance until the loan reaches zero. The math repeats the same three steps every row, so once the first month is right, the rest is mechanical. A spreadsheet makes it fast, but the logic is the same whether you use PMT functions or a pencil.
The Three Numbers You Need
Pull these from your promissory note or the closing disclosure required under Regulation Z.
- Loan principal. The amount you actually borrowed. If closing costs were rolled in, they’re part of this figure. Don’t substitute the total cost of credit shown on your disclosure, which bundles in finance charges over the life of the loan.
- Contract interest rate. The annual rate stated in your loan agreement. Not the APR. APR folds in fees for comparison shopping and will give you the wrong payment if you drop it into the formula.
- Loan term. The total number of payments. A 30-year mortgage paid monthly is 360 payments; a 15-year is 180. Use what the contract says.
Convert the annual rate to a monthly rate by dividing by 12. A 6.5% annual rate becomes 0.065 ÷ 12 = 0.005417 per month. That monthly rate drives everything that follows.
Calculating the Monthly Payment
The standard formula for a fixed-rate loan is:
M = P × [ i(1 + i)^n ] / [ (1 + i)^n − 1 ]
M is the monthly payment, P is the principal, i is the monthly interest rate, and n is the total number of payments. The formula finds the one constant dollar amount that, paid every month for n months, covers all interest owed and brings the balance to zero on the last payment.
A Worked Example
Take a $200,000 mortgage at 6.5% for 30 years. The monthly rate is 0.005417, and n is 360.
Calculate (1 + i)^n: (1.005417)^360 ≈ 6.9913. Multiply by the monthly rate: 0.005417 × 6.9913 = 0.03787. Multiply by the principal: 200,000 × 0.03787 = $7,574. That’s the numerator. For the denominator, subtract 1 from the rate factor: 6.9913 − 1 = 5.9913. Divide: $7,574 ÷ 5.9913 = $1,264.14 per month.
That figure covers only principal and interest. More on what sits outside the schedule in a moment.
Setting Up the Table
An amortization table has six columns. Each row is one payment period, usually a month.
- Payment number. Sequential count from 1 to n.
- Beginning balance. What you owe at the start of the month. For payment 1, this equals the original principal.
- Payment amount. The fixed figure from the formula above.
- Interest portion. How much of this payment goes to the lender as the cost of borrowing.
- Principal portion. How much actually reduces your debt.
- Ending balance. What you owe after the payment. This becomes the next row’s beginning balance.
Filling In Each Row
Three calculations per row, always in the same order. Continuing the $200,000 example at 6.5%:
Payment 1. Multiply the beginning balance by the monthly rate for the interest portion: $200,000 × 0.005417 = $1,083.33. Subtract that from the total payment for the principal portion: $1,264.14 − $1,083.33 = $180.81. Subtract the principal from the beginning balance for the ending balance: $200,000 − $180.81 = $199,819.19.
Payment 2. The prior ending balance is the new beginning balance. Interest: $199,819.19 × 0.005417 = $1,082.35. Principal: $1,264.14 − $1,082.35 = $181.79. Ending balance: $199,637.40.
Payment 3. Interest: $199,637.40 × 0.005417 = $1,081.37. Principal: $182.77. Ending balance: $199,454.63.
Notice the pattern. Each month the interest drops a little and the principal grows a little, even though the total stays the same. Early in a 30-year mortgage, roughly 85% of each payment is interest. By the final years, that ratio flips almost entirely to principal. The first decade feels like you’re barely making a dent because you largely aren’t.
Repeat the three-step calculation for every remaining payment. Row 360 should produce an ending balance of zero, give or take rounding.
Building It in a Spreadsheet
Nobody fills in 360 rows by hand. Excel and Google Sheets both have built-in functions that do the arithmetic.
Set Up Your Inputs
Put the loan details in dedicated cells so every formula can point at them. For instance: annual interest rate in C2, loan term in years in C3, payments per year in C4, and loan amount in C5. Label each one.
Calculate the Payment
The PMT function returns the fixed monthly payment directly. The syntax is PMT(rate, nper, pv), where rate is the periodic rate, nper is the total number of payments, and pv is the loan amount. Using the input cells above: =PMT(C2/C4, C3*C4, C5). The result comes back negative because Excel treats outgoing cash as negative. That’s normal.
Split Interest and Principal
IPMT and PPMT give the interest and principal portions for any specific payment. Both take the same arguments as PMT plus a “per” argument for the payment number. For payment 1: =IPMT(C2/C4, 1, C3*C4, C5) returns the interest; =PPMT(C2/C4, 1, C3*C4, C5) returns the principal.
You can also build the schedule manually in the sheet, exactly like the hand calculations above. In the interest column, multiply the beginning balance by the monthly rate. In the principal column, subtract the interest from the fixed payment. In the ending balance column, subtract the principal from the beginning balance. Drag down. Either method produces the same schedule; the built-in functions are just quicker to set up.
Why the Last Payment Won’t Match
Build a full schedule and the final payment will almost certainly be a few cents off from the others. The calculated monthly payment gets rounded to the nearest cent, and that rounding error compounds across hundreds of payments. If the payment was rounded up, you overpay slightly each month and the final payment comes in smaller. Rounded down, the final payment is a bit larger. On a 30-year mortgage, the cumulative difference is usually a few dollars. Lenders adjust the last payment so the balance lands on zero.
Modeling Extra Payments
The real reason to build a schedule yourself is to see what extra payments do. Any extra money applied to principal skips the interest-first split and reduces your balance dollar for dollar. Because next month’s interest is calculated on the new, lower balance, the savings compound.
The numbers add up fast. On a $200,000 mortgage at 4%, adding $100 per month to principal can shorten the loan by over four years and eliminate more than $26,000 in interest. Doubling that to $200 extra per month can cut the term by roughly eight years.
Biweekly payments work on the same principle. Pay half the monthly amount every two weeks and you make 26 half-payments across 52 weeks, which equals 13 full payments instead of 12. That extra payment each year goes straight to principal and shaves years off a 30-year loan.
To model this in your spreadsheet, add a column for additional principal. Subtract it from the ending balance along with the regular principal portion, then carry the lower figure forward. The payoff date moves earlier with each row.
Before you commit to extra payments, check your loan documents for a prepayment penalty. Federal law prohibits prepayment penalties on residential mortgages that don’t qualify as “qualified mortgages,” caps penalties on qualifying loans for the first three years only, and bars them entirely on adjustable-rate and higher-cost loans.1Office of the Law Revision Counsel. 15 USC 1639c – Minimum Standards for Residential Mortgage Loans
Adjustable-Rate Loans
The formula above assumes a fixed rate for the whole term. Adjustable-rate mortgages use the same math, but the schedule resets at each rate adjustment. When the rate changes, the lender plugs three updated values into the same formula: the new interest rate, the remaining principal balance, and the number of payments left. The result is a new fixed payment that fully pays off the loan over the remaining term. Each adjustment effectively starts a fresh amortization schedule from that point.
If you’re building a schedule for an ARM, you’ll need to project future adjustments using the index and margin in your loan documents and any caps on how much the rate can move per period or over the life of the loan. The schedule becomes a forecast, not a guarantee.
Watch for Negative Amortization
Some loans allow a minimum payment that doesn’t cover the interest due. The unpaid interest gets added to principal, and the balance grows instead of shrinking. That’s negative amortization, and it means you’re paying interest on interest.2Consumer Financial Protection Bureau. What Is Negative Amortization
It shows up most often in payment-option ARMs where the borrower can choose to pay less than full interest. The lower payment comes with a growing balance that eventually triggers a mandatory recast to fully amortizing payments. Regulation Z requires lenders to disclose the consequences before closing, including how much the balance could grow and the earliest date full payments kick in.3eCFR. 12 CFR Part 1026 – Truth in Lending Regulation Z
If any row of your schedule shows an ending balance higher than the beginning balance, you’re in negative amortization territory. Revisit the payment amount or the loan terms.
Escrow Sits Outside the Schedule
An amortization schedule tracks only principal and interest. Most mortgage servicers also collect property taxes and homeowners insurance each month in an escrow account, and the full bill is often called PITI: principal, interest, taxes, and insurance.
Escrow doesn’t appear on the schedule because it doesn’t affect the loan balance. Taxes and insurance are pass-through costs the servicer holds and pays on your behalf, and federal rules require an annual escrow analysis with adjustments when premiums or tax bills change.4eCFR. 12 CFR 1024.17 – Escrow Accounts
Your schedule tells you where the loan stands. Your actual monthly bill will be higher because of escrow, so keep both figures in view when you budget.