To build an amortization schedule, you need four numbers from your loan — the principal, the annual interest rate, the term, and the payment frequency — and one formula that turns them into a fixed periodic payment. Each row of the schedule then splits that payment between interest and principal, with the interest share shrinking and the principal share growing as the balance falls. You can do the whole thing by hand or let Excel’s PMT, IPMT, and PPMT functions build it in seconds.
The Four Numbers You Need
Every schedule starts with the same inputs, all of which appear on your promissory note or Truth in Lending disclosure:
- Principal balance: the total amount borrowed before any interest. On a $250,000 mortgage or a $20,000 car loan, that starting figure is what the schedule works down to zero.1Consumer Financial Protection Bureau. On a Mortgage, What’s the Difference Between My Principal and Interest Payment and My Total Monthly Payment?
- Annual interest rate: the yearly cost of borrowing, expressed as a percentage. Lenders must disclose this as the annual percentage rate under the federal Truth in Lending Act.2eCFR. 12 CFR Part 226 – Truth in Lending (Regulation Z)
- Loan term: how long you have to repay. Sixty months for a typical car loan, 360 months for a 30-year mortgage.
- Payment frequency: how often payments come due. Monthly is standard; some loans use biweekly or quarterly schedules. This sets how many rows your table will have.
The Fixed Payment Formula
One formula does the heavy lifting. It finds the equal payment that, repeated for the life of the loan, pays off the balance plus interest exactly on schedule:
M = P × [r × (1 + r)^n] / [(1 + r)^n – 1]
Where M is the fixed periodic payment, P is the principal, r is the periodic interest rate (annual rate divided by the number of payments per year), and n is the total number of payments.
Take a $250,000 loan at 6% annual interest for 30 years with monthly payments. Convert the annual rate to a monthly rate: 6% ÷ 12 = 0.005. Total payments: 30 × 12 = 360. Plug those in:
M = 250,000 × [0.005 × (1.005)^360] / [(1.005)^360 – 1]
(1.005)^360 is roughly 6.0226, which gives:
M = 250,000 × [0.005 × 6.0226] / [6.0226 – 1] = 250,000 × 0.030113 / 5.0226 ≈ $1,498.88
That’s the payment every month for 30 years. Total cash paid over the life of the loan comes to roughly $539,595, of which about $289,595 is interest.
How Each Payment Splits Between Interest and Principal
The payment stays flat, but its composition shifts each period. For any row, calculate interest on the remaining balance by multiplying it by the periodic rate. In month one of the example, that’s $250,000 × 0.005 = $1,250 in interest. Subtract that from the $1,498.88 payment, and the remaining $248.88 pays down principal.
Month two starts with a balance of $249,751.12. Interest is $249,751.12 × 0.005 = $1,248.76, so $250.12 goes to principal — a little more than the previous month. The pattern repeats: as the balance falls, interest accrues more slowly, and more of the fixed payment attacks the principal.
By the last years of a 30-year mortgage, the ratio has nearly flipped. Early payments run roughly 80% interest and 20% principal. Late payments are the reverse.
Building the Table Row by Row
Set up five columns: payment number, payment amount, interest portion, principal portion, and remaining balance.
Row zero has just the starting balance. For row one, enter the fixed payment, calculate interest as balance × periodic rate, subtract that interest from the payment to get the principal portion, and subtract the principal from the previous balance to get the new remaining balance. Every row after that uses the same logic and takes its starting balance from the row above. A 30-year monthly loan runs to 360 rows, and the math in each is identical. The last row should show a balance of zero, or very close to it.
Why the Final Payment Usually Differs
The calculated payment almost always requires rounding to the nearest cent, and that fraction of a penny compounds across hundreds of payments. By the last month, the remaining balance won’t line up exactly with the standard payment. The final payment gets nudged up or down by a few cents to bring the balance to zero. In a spreadsheet, you can handle this by writing the last row to pay the remaining balance plus that month’s interest instead of the standard amount.
Doing It in Excel
Excel’s financial functions replace the manual formula entirely. The core one is PMT, which returns the fixed periodic payment.3Microsoft Support. PMT Function
Put your loan details in a few cells — say B1 for the $250,000 principal, B2 for the 6% annual rate, and B3 for the 30-year term. Then the monthly payment is:
=PMT(B2/12, B3*12, B1)
Excel returns a negative number because it treats payments as outflows. Wrap it in ABS() if the sign bothers you. The three arguments are the periodic rate, total number of payments, and present value.3Microsoft Support. PMT Function
Splitting Each Payment With IPMT and PPMT
To see how much of any single payment is interest and how much is principal, use IPMT and PPMT. Both take the same arguments as PMT plus the specific period number.
IPMT returns the interest portion:4Microsoft Support. IPmt Function
=IPMT(B2/12, A8, B3*12, B1)
PPMT returns the principal portion:5Microsoft Support. PPMT Function
=PPMT(B2/12, A8, B3*12, B1)
A8 holds the current period number. Put payment numbers 1 through 360 in column A, enter these formulas in the first row, and drag them down. For the remaining balance column, subtract the PPMT value from the previous row’s balance. The sum of IPMT and PPMT in any row should equal PMT; if it doesn’t, a reference is off.
Totaling Interest and Principal Across a Range
Two more functions let you skip the row-by-row detail. CUMIPMT returns the total interest paid between any two periods:6Microsoft Support. CUMIPMT Function
=CUMIPMT(B2/12, B3*12, B1, 1, 60, 0)
That returns total interest paid in the first five years. CUMPRINC does the same for principal:7Microsoft Support. CUMPRINC Function
=CUMPRINC(B2/12, B3*12, B1, 1, 60, 0)
Both take six arguments: periodic rate, total periods, loan amount, start period, end period, and payment timing (0 for end-of-period, which is standard). These are useful for estimating mortgage interest paid in a given tax year or comparing how much principal you’d build under different loan terms. Keep the rate and period count in the same time unit; mixing a monthly rate with an annual count returns a #NUM! error.
Modeling Extra Payments
One of the most practical uses of a schedule is seeing what happens when you pay more than required. Extra money applied to principal reduces the balance that interest is calculated on for every future period, and the effect compounds — a lower balance means less interest next month, so more of your regular payment attacks principal, which lowers the balance further.
On a $200,000 mortgage at 4% over 30 years, adding $100 a month to principal cuts roughly four and a half years off the loan and saves over $26,000 in interest. Doubling that to $200 a month eliminates more than eight years and saves over $44,000.
To model this in Excel, add a column for the extra payment. In the remaining balance formula, subtract both the regular principal portion and the extra payment from the previous balance. The row where the balance hits zero is your new payoff date. When you actually send extra money to a lender, tell the servicer to apply it to principal. Otherwise some servicers treat it as a prepayment of next month’s installment, which saves no interest.
What the Schedule Doesn’t Show
An amortization schedule tracks principal and interest only. If you have a mortgage, your actual monthly payment is almost certainly higher because it includes escrow contributions for property taxes and homeowners insurance.8Consumer Financial Protection Bureau. What Is an Escrow or Impound Account?
Most lenders require an escrow account that collects those costs monthly and pays them when they come due. A servicer can also require a cushion of up to two months’ worth of escrow payments.9eCFR. 12 CFR Part 1024 Subpart B – Mortgage Settlement and Escrow Accounts If your property taxes rise or your insurance premium jumps, your total monthly payment changes even though the principal-and-interest portion stays fixed. The schedule remains accurate; it just doesn’t tell the whole payment story.
When the Balance Grows Instead of Shrinks
A standard schedule assumes every payment covers at least the full interest owed. Negative amortization is the opposite: the payment is less than the interest due, and the unpaid interest is added to the balance. The debt grows instead of shrinking.10Consumer Financial Protection Bureau. What Is Negative Amortization?
This shows up most often with adjustable-rate loans that offer a low minimum payment option during an introductory period. The minimum covers only part of the interest; the rest piles onto the principal, so you end up paying interest on interest.10Consumer Financial Protection Bureau. What Is Negative Amortization? If you’re building a schedule for a loan with a payment option that doesn’t fully cover interest, the remaining-balance column will trend upward in the early rows. That’s the clearest sign the loan’s structure is working against you.