Build a Mortgage Amortization Schedule in Excel
A mortgage amortization schedule breaks down each monthly payment, showing how much goes toward interest and how much reduces your loan balance. You can create one in Excel or Google Sheets using two formulas: one to calculate your fixed monthly payment and another to divide each payment between interest and principal.
This walkthrough takes you through both formulas cell by cell, using a $400,000 loan at 6.5% over 30 years as the running example. Swap in your own numbers, and the entire schedule will recalculate.
There's no downloadable file here because every loan has a different balance, rate, and term. By entering the formulas yourself, you can make sure the schedule matches your exact loan.
Prefer to skip the spreadsheet entirely? Our free mortgage amortization schedule calculator runs the same math instantly.
What the Amortization Formula Actually Does
A fixed-rate loan charges interest only on the balance you still owe, so the payment splits differently every month even though the dollar amount never changes. Interest dominates the early payments, because the balance is largest right after closing. The Consumer Financial Protection Bureau (CFPB) explains it plainly: lenders use a standard formula so a fixed payment retires the loan exactly at the end of the term, with the interest-to-principal ratio drifting the entire way.
Three numbers drive that formula: the loan amount, the interest rate, and the term in months. Change any one of them and the entire schedule shifts underneath you. That is the whole mechanism behind mortgage amortization, and it is exactly what the next three steps rebuild in a spreadsheet.
This same formula also reveals the total interest you will pay over the life of the loan. On the $400,000 loan at 6.5% over 30 years, 360 payments of about $2,528 add up to roughly $910,080 paid in total. Subtract the $400,000 you borrowed, and interest alone accounts for about $510,000 of that. Seeing that figure spelled out often pushes a reader to look seriously at a shorter term or an extra-payment plan.
Step 1: Calculate the Fixed Monthly Payment
Open a blank sheet and label five input cells: loan amount, annual rate, term in years, monthly rate, and total payments. Put your loan amount in B1 (400000 for the example), your annual rate in B2 as a decimal (0.065 for 6.5%), and your term in years in B3 (30).
In B4, find the monthly rate with =B2/12. In B5, find the total number of payments with =B3*12. In B6, calculate the fixed payment with the built-in PMT function: =PMT(B4,B5,-B1). The negative sign on the loan amount is required, since PMT returns a negative number without it.
For the $400,000 loan at 6.5% over 30 years, B6 returns roughly $2,528. That single number is the payment every row of your schedule references.
Step 2: Split Each Payment into Interest and Principal
Build a new table with six headers across row 1: Month, Starting Balance, Payment, Interest, Principal, Ending Balance. In row 2, enter 1 for Month and =$B$1 for Starting Balance, so it pulls your loan amount.
For Payment, reference the fixed payment you already calculated: =$B$6. For Interest, multiply that row's starting balance by the monthly rate: =C2*$B$4. For Principal, subtract interest from the payment: =D2-E2. For Ending Balance, subtract principal from the starting balance: =C2-F2.
Excel and Google Sheets also offer IPMT and PPMT functions that calculate this same split in one cell each, if you would rather skip the manual subtraction.
Step 3: Copy the Formula Down the Full Schedule
In row 3, set Month to 2 and Starting Balance to the prior row's Ending Balance: =G2. Select the formulas in row 3 and drag the fill handle down through row 361, since a 30-year loan runs 360 monthly payments.
Your final row's Ending Balance should land at or extremely close to zero. That confirms the schedule pays the loan off exactly on schedule. The payment formula is built to guarantee exactly that.
Add a running-total column with =SUM($E$2:E2) copied down every row if you want to see cumulative interest paid at any point in the loan. That column answers a question the payment figure alone never does: how much interest a specific payoff date actually costs you.
Common Mistakes That Break the Schedule
We see one mistake most often when readers build this formula themselves: using the annual rate instead of the monthly rate in the interest calculation. That single swap inflates every interest figure by roughly 12 times, and it is the most common error in a homemade schedule.
Two other errors show up almost as often. Forgetting to lock a reference with a dollar sign, such as $B$4, lets the formula drift as you copy it down hundreds of rows. Rounding the payment to the nearest dollar early creates a small mismatch that compounds into a real gap by the final row.
Reconcile your work against the built-in CUMIPMT function, which totals interest over a range of payments without a row-by-row table. If your running total and CUMIPMT disagree by more than a few dollars, check the rate and reference cells first.
When the Live Calculator Beats a Spreadsheet
A spreadsheet is the right tool for understanding exactly how the math works, and for a loan that never changes. It is the wrong tool once you want to test an extra $200 a month toward principal, compare a 15-year term against a 30-year term, or see how a refinance offer changes your total interest, since each scenario means rebuilding rows by hand.
Our free mortgage amortization schedule calculator runs this same formula instantly and lets you test extra payments and different terms without touching a single cell reference. If you are deciding whether an extra payment plan is worth it in the first place, the mortgage payoff calculator shows the years and interest it would save.
Build the spreadsheet once to understand the mechanism. Reach for the calculator every time after that.
Frequently asked questions
What is the mortgage amortization formula?
The fixed monthly payment formula is M = P times [r(1+r)^n] divided by [(1+r)^n minus 1], where P is the loan amount, r is the monthly interest rate, and n is the total number of payments. Excel and Google Sheets calculate the same result with the built-in PMT function: =PMT(rate, nper, -pv).
How do I calculate a mortgage payment in Excel?
Enter your monthly rate, total number of payments, and loan amount, then use =PMT(monthly_rate, total_payments, -loan_amount). The negative sign on the loan amount is required for the function to return a positive payment.
How much of my first mortgage payment goes to interest?
Most of it. On a $400,000 loan at 6.5% over 30 years, the first payment of about $2,528 includes roughly $2,167 in interest and only $361 in principal, because interest is calculated on the full starting balance.
Does the amortization formula change for a 15-year loan instead of a 30-year loan?
The formula stays the same. Only the term input changes, from 360 monthly payments to 180. A shorter term produces a higher monthly payment, but a much smaller share of each payment goes to interest, since the balance shrinks faster.
Can I model extra principal payments in this spreadsheet?
Yes, by adding an Extra Payment column and subtracting it from the Ending Balance formula each month, though the schedule then runs fewer than 360 rows and needs a formula that stops once the balance hits zero. Our mortgage payoff calculator models extra payments without any spreadsheet work.
Is there a downloadable amortization schedule file I can just open?
No. This guide teaches the formulas so you can build a schedule for your own exact loan amount, rate, and term, since a generic downloadable file would not match your numbers. Prefer not to build one? The free mortgage amortization schedule calculator generates your full schedule instantly online.
Does this formula work for an adjustable-rate mortgage?
Only between rate changes. An adjustable-rate mortgage (ARM) uses this exact formula for each fixed-rate period, but once the rate resets, you have to recalculate the payment using the loan's remaining balance, the new rate, and the remaining term as new inputs. A fixed-rate loan never needs that recalculation, which is one reason the formula is easiest to learn on a fixed-rate example first.
Does a biweekly payment schedule use the same formula?
Not directly. Paying half your monthly payment every two weeks results in 26 half-payments a year, the equivalent of 13 full monthly payments instead of 12, which this formula does not account for on its own. Our mortgage payoff calculator models a biweekly schedule and the extra payment it effectively adds each year.
Sources
We prioritize primary sources for rules, formulas, rates, limits, and definitions. See our calculator methodology and editorial policy.