Loan Amortization Schedule in Excel: Step-by-Step (2026)
A $400,000 mortgage at 6.5% for 30 years costs the borrower $510,178 in interest — more than the home itself. Yet most finance professionals still build amortization schedules by hand-typing principal and interest cells, then patching prepayments with brittle IF statements. There is a better way. A well-built loan amortization schedule in Excel uses just three native functions — PMT, IPMT, and PPMT — to produce a schedule that handles fixed-rate loans, balloon payments, variable rates, and curtailment payments without breaking.
This guide walks through building one from a blank workbook, then layers on the real-world wrinkles credit analysts, mortgage underwriters, and corporate treasurers actually face.
What Is a Loan Amortization Schedule?
A loan amortization schedule is a period-by-period table that splits each loan payment into interest and principal, tracks the declining balance, and shows the total cost of borrowing over the life of the loan. The earliest payments are mostly interest; later payments are mostly principal — a curve every borrower should understand before signing.
Why Amortization Schedules Matter in Finance
For a financial analyst, the amortization schedule is the bridge between a debt assumption and the three financial statements:
- Interest expense flows to the income statement
- Principal balance appears on the balance sheet as long-term and current debt
- Principal repayments show up in cash flow from financing
- Coverage ratios (DSCR, ICR) depend on the period-by-period split
Get the schedule wrong, and every downstream number — EBITDA margin, free cash flow, debt covenants, valuation — drifts off.
💡 Pro Tip: Always build the amortization schedule as its own worksheet, not inline with the cash flow statement. This makes auditing the interest split far easier and lets you re-use the schedule across multiple model variants.
How Does Excel Calculate Loan Amortization?
Excel calculates loan amortization using three core financial functions. PMT returns the constant periodic payment, IPMT returns the interest portion of any single payment, and PPMT returns the principal portion. Together they let you build any fixed-rate amortization schedule without a single manual interest calculation.
Here is the syntax for each, with the arguments that matter for a standard loan:
=PMT(rate, nper, pv, [fv], [type])
=IPMT(rate, per, nper, pv, [fv], [type])
=PPMT(rate, per, nper, pv, [fv], [type])
Where:
- rate = periodic interest rate (annual rate divided by payments per year)
- nper = total number of payments
- per = the specific payment number you want to analyze (1 through nper)
- pv = the loan amount (present value, entered as a positive number for borrowing)
- fv = future value, defaults to 0 (use this for balloon loans)
- type = 0 for end-of-period payments (default), 1 for beginning-of-period
⚠️ Warning: Excel returns payments as negative numbers when you enter pv as positive, because the formulas use a cash-flow convention. Wrap PMT in a unary minus (
=-PMT(...)) if you want a positive payment value in your schedule.
How Do You Build a Loan Amortization Schedule in Excel Step by Step?
Building a complete loan amortization schedule in Excel takes six steps: lay out assumptions, calculate the periodic payment, build the period column, split each payment into interest and principal, track the running balance, and add summary metrics. The whole thing fits on one screen and works for any fixed-rate term loan.
Step 1: Lay Out the Loan Assumptions
Reserve the top of your worksheet for inputs. Color-code them blue (the modeling convention for hard-coded assumptions). A clean input block looks like this:
| Cell | Label | Value | Notes |
|---|---|---|---|
| B3 | Loan Amount | $400,000 | Principal borrowed |
| B4 | Annual Interest Rate | 6.50% | Nominal APR |
| B5 | Term (Years) | 30 | Loan tenor |
| B6 | Payments per Year | 12 | Monthly = 12, quarterly = 4 |
| B7 | First Payment Date | 7/1/2026 | Used for EDATE schedule |
| B8 | Periodic Rate | =B4/B6 | Derived |
| B9 | Total Payments | =B5*B6 | Derived |
The derived cells (B8 and B9) are formulas, not hard-codes. This lets you flex the term or rate without rebuilding the schedule.
Step 2: Calculate the Constant Periodic Payment
Drop the PMT formula into cell B10:
=-PMT($B$8, $B$9, $B$3)
For our $400,000 / 6.5% / 30-year example, this returns $2,528.27. That is the level monthly payment for the entire loan. The minus sign in front flips the sign so the payment displays positive.
ℹ️ Note: PMT assumes a fully amortizing loan with zero balance at maturity. For balloons, partial amortization, or interest-only periods, you'll override this with the formulas in later sections.
Step 3: Build the Period and Date Columns
Starting in row 14, set up the schedule columns: Period, Payment Date, Beginning Balance, Payment, Interest, Principal, Ending Balance.
Drop a 1 in A14 and use this formula in A15 to extend the period column:
=IF(A14<$B$9, A14+1, "")
For the payment date column, use EDATE so dates stay on the same day of the month regardless of month length:
=EDATE($B$7, A14-1)
Copy these down for 360 rows (the total payment count). The IF wrapper blanks rows past the loan term when you flex the inputs to a shorter loan.
Step 4: Split Each Payment Into Interest and Principal
This is where IPMT and PPMT do the heavy lifting. In the interest column (E14):
=-IPMT($B$8, A14, $B$9, $B$3)
In the principal column (F14):
=-PPMT($B$8, A14, $B$9, $B$3)
The A14 reference (the period number) is what makes each row different. Period 1 is heavy on interest; period 360 is heavy on principal. The total — column E plus column F — equals the constant PMT.
Example: For period 1 of our $400,000 loan, IPMT returns $2,166.67 (interest) and PPMT returns $361.60 (principal). For period 360, the same formulas return $13.61 (interest) and $2,514.66 (principal). Same total payment, mirrored split.
Step 5: Build the Running Balance
The beginning balance for period 1 equals the loan amount. From period 2 onward, it equals the prior period's ending balance:
C14: =$B$3
C15: =G14
Ending balance subtracts principal from beginning balance:
G14: =C14-F14
After 360 periods, G373 should equal exactly $0.00. If it doesn't, you have a rounding error or a formula reference issue — go back and audit.
Step 6: Add Summary Metrics
At the top of the schedule, add a few SUM-based metrics that make the schedule presentable to a lender or a CFO:
Total Interest: =SUM(E14:E373)
Total Principal: =SUM(F14:F373)
Total Cost of Loan: =SUM(D14:D373)
Average Monthly Pmt: =AVERAGE(D14:D373)
For our example, total interest comes to $510,178 — meaningfully more than the principal borrowed. This is the single most important number to show a borrower.
graph LR
A[Loan Inputs] --> B[PMT: Level Payment]
B --> C[Period Loop]
C --> D[IPMT: Interest Portion]
C --> E[PPMT: Principal Portion]
D --> F[Income Statement]
E --> G[Cash Flow Statement]
E --> H[Balance Sheet]
How Do You Handle Extra Payments and Prepayments?
To handle extra payments, add a curtailment column to the schedule and recompute the interest each period using the actual beginning balance rather than IPMT. The trick is switching from the closed-form IPMT/PPMT functions to a recursive balance-tracking approach.
Why IPMT Breaks With Prepayments
IPMT assumes the original amortization continues unchanged. The moment a borrower pays an extra $500 in month 24, the original schedule is invalid — the remaining periods now have a smaller principal balance, so the interest portion drops faster than IPMT predicts.
The Recursive Approach
Replace IPMT with a direct interest calculation:
Interest (E14) = C14 * $B$8
Principal (F14) = MIN(D14 - E14, C14)
Extra Payment (H14) = [user input or scheduled curtailment]
Ending Balance (G14) = MAX(C14 - F14 - H14, 0)
The MIN and MAX wrappers protect against the final payment going negative — when the remaining balance is smaller than the regular principal portion, the schedule terminates cleanly.
💡 Pro Tip: For a one-time lump-sum prepayment, just hard-code the value in column H for the relevant period. For recurring extra payments (say, $200 every month), reference an input cell so a borrower can test scenarios like "what if I pay $200 extra forever?" The answer for our example: payoff drops from 360 months to 283 months and saves $132,000 in interest.
How Do You Model a Balloon Payment Loan?
A balloon loan amortizes on a longer schedule than the actual term — say, 30-year amortization with a 7-year balloon maturity. The borrower makes monthly payments as if the loan were 30 years, then pays off the entire remaining balance in month 84.
Modify PMT to include a future-value argument equal to the remaining balance at maturity. But a cleaner approach is to amortize on the long schedule and add a single balloon line at the end:
Balloon Payment = -FV($B$8, BalloonPeriod, PMT_value, $B$3)
For a $5M loan at 7% with 30-year amortization and a 7-year balloon:
| Metric | Value |
|---|---|
| Monthly Payment (Years 1–7) | $33,265 |
| Total Paid Over 7 Years | $2,794,260 |
| Remaining Balance at Month 84 | $4,553,892 |
| Total Cost to Borrower | $7,348,152 |
The balloon structure looks attractive (low monthly payments) until the maturity comes due. Commercial real estate models almost always include a balloon, which is why the refi risk assumption matters as much as the in-place rate.
How Do You Build an Adjustable-Rate Mortgage (ARM) Schedule?
For an ARM, the periodic rate is no longer constant — it resets on a fixed schedule based on an index plus a margin. The amortization formulas still work, but you must recompute the payment after each reset.
ARM Schedule Mechanics
Add three columns: Rate Period, Period Rate, and Reset Payment. The rate period stays constant until the next reset (e.g., 5/1 ARM holds for 60 months, then resets annually).
Rate Period (J14): =IF(A14<=60, "Fixed", "Reset " & INT((A14-60-1)/12)+1)
Period Rate (K14): =IF(A14<=60, $B$4, IndexRate + Margin) / 12
Then recompute the payment whenever the rate changes:
=-PMT(K14, $B$9-A14+1, C14)
The $B$9-A14+1 argument is the remaining term, and C14 is the current outstanding balance. The PMT recalculates as a fresh amortization of the remaining balance over the remaining term at the new rate.
⚠️ Warning: Most ARMs have rate caps — periodic (max change per reset), lifetime (max change over the loan), and floor (minimum rate). Don't forget to wrap the reset rate in
MAX(MIN(NewRate, LifetimeCap), Floor)or your schedule will overstate stress scenarios.
What Are the Most Common Amortization Schedule Mistakes?
The same handful of errors show up in nearly every junior analyst's amortization schedule. Catching them early saves hours of downstream debugging.
| Mistake | Symptom | Fix |
|---|---|---|
| Annual rate used as periodic rate | Payment 12x too high | Divide annual rate by payments per year |
| Forgot to flip sign on PMT | Negative payments in schedule | Wrap with unary minus: =-PMT(...) |
| Hard-coded payment count | Schedule breaks when term changes | Use =Years * Periods Per Year |
| Beginning balance not linked | Closing balance drifts | Reference prior row's ending balance |
| IPMT used with prepayments | Schedule stops zeroing out | Switch to recursive interest calc |
| Missing date column | Can't tie to cash flow periods | Use EDATE from first payment date |
| No final balance check | Schedule ends at $-0.0001 | Add ROUND or audit cell at maturity |
graph TD
A[Build Schedule] --> B{Does final balance equal zero?}
B -->|Yes| C[Sum interest column]
B -->|No| D[Check rate periodicity]
D --> E[Check sign convention]
E --> F[Check balance linking]
F --> B
C --> G[Schedule ready for review]
How Do You Tie the Amortization Schedule to the Three Financial Statements?
The amortization schedule is a supporting worksheet. Three numbers flow out of it into the integrated model:
- Interest expense → Income Statement (above EBT)
- Principal repayment → Cash Flow Statement (financing activities, as negative cash flow)
- Ending balance → Balance Sheet (split between current portion and long-term debt)
The Current Portion Split
GAAP requires splitting debt between current liabilities (due within 12 months) and long-term liabilities (due beyond 12 months). For monthly amortization:
Current Portion of LT Debt = SUMIFS(Principal, Date, ">"&BalanceSheetDate, Date, "<="&EDATE(BalanceSheetDate,12))
Long-Term Debt = Ending Balance at BS Date - Current Portion
💡 Pro Tip: Build this split inside the amortization tab and reference it from the balance sheet. Auditors hate it when current/long-term splits are hard-coded — they want to see the derivation in one place.
Frequently Asked Questions
What is the difference between PMT, IPMT, and PPMT in Excel?
PMT returns the total constant periodic payment of a fully amortizing loan. IPMT returns just the interest portion of a specific payment, and PPMT returns just the principal portion. PMT equals IPMT plus PPMT for any given period. Use PMT to compute the level payment, and IPMT/PPMT to populate an amortization schedule row by row.
Can Excel build an amortization schedule for irregular payment dates?
Yes, but PMT, IPMT, and PPMT assume equal periods. For genuinely irregular payment dates, switch to a manual approach: calculate the day-count fraction between payments using actual/365 or 30/360 conventions, multiply the beginning balance by Rate * DayCountFraction for interest, and subtract from the payment to get principal. XNPV and XIRR handle this elegantly for valuation, but the schedule itself must be hand-built.
How do I handle interest-only periods at the start of a loan?
For an interest-only period, set the principal column to zero and let interest equal Balance * Periodic Rate. After the IO period ends, switch to a fully amortizing PMT calculated on the remaining term. Construction loans, commercial real estate bridges, and many subordinated debt facilities use this structure — model it as a phase-in rather than a continuous schedule.
What is the formula for the remaining loan balance at any period?
The remaining loan balance after period N equals =-CUMPRINC(rate, nper, pv, 1, N, 0) + pv, or more intuitively, sum the principal column from period 1 through N and subtract from the original loan amount. CUMPRINC is faster for large schedules because it avoids the row-by-row PPMT calculations. For prepayment-adjusted schedules, you must use the recursive balance — CUMPRINC won't know about your extra payments.
Why does my amortization schedule end at $0.01 instead of zero?
This is a floating-point rounding artifact, not a formula error. PMT calculates the payment to enough precision that 360 monthly payments theoretically zero out the balance, but Excel's binary math leaves a few cents on the table. Either accept it, add a final-period "true-up" that absorbs the rounding, or wrap your ending balance in ROUND(..., 2). Real-world lenders adjust the final payment by a few dollars to clear the balance exactly.
Putting It Into Practice
A well-built amortization schedule is one of those quietly compounding skills — once you've built one, you can drop it into a project finance model, a real estate pro forma, a personal financial plan, an M&A debt schedule, or an LBO model where each leveraged tranche needs its own amortization waterfall with minor adjustments. The PMT/IPMT/PPMT trio handles most cases; the recursive approach covers the rest. Add date columns, balloon logic, and ARM resets, and you can model nearly any fixed- or floating-rate debt instrument that exists in the market.
If you want to skip the manual build, VeloraAI can generate a complete amortization schedule from plain English — "build a 30-year monthly amortization schedule for a $400,000 loan at 6.5% with a $200 monthly extra payment" — and drop the formulas, the schedule, and the summary metrics straight into your active workbook. Either way, the muscle of understanding what each cell does is what separates analysts who own their models from analysts who pray nobody asks.
Next step: open a blank workbook, key in the assumption block from Step 1, and build the schedule from scratch. Even if you've done it before, doing it once a year keeps the formulas sharp and surfaces the small wrinkles — sign conventions, day-count fractions, current-portion splits — that show up in real deals.