How to Calculate an Amortization Schedule Without Relying on Software
The fastest way to calculate an amortization schedule by hand is to first compute your fixed monthly payment using the loan amortization formula, then build a row-by-row table where each period’s interest equals the starting balance multiplied by the periodic rate, and principal equals the payment minus that interest. You repeat this until the balance hits zero. In the next few minutes you’ll see a full manual walkthrough for a $5,000 loan, learn why the formula works, and get a printable worksheet framework so you can make your own amortization schedule on paper.
Most online guides stop at “use our calculator” or “open Excel.” But if you’ve ever wondered how to calculate amortization schedule math from first principles, the hand method is unbeatable for building intuition. When I first sat down with a refinance offer for a friend’s $12,000 auto loan, I realized the lender’s schedule looked like magic until I reproduced the first three lines with a pocket calculator and a columnar pad.
The thing nobody tells you about hand calculation: it exposes every rounding choice and timing assumption that software hides. That transparency is exactly why regulators and lenders still train new loan officers on paper schedules before letting them touch modeling systems.
The Core Formula, Demystified
What is the formula for calculating amortization? The standard closed-form equation for a fully amortizing fixed-rate loan is M = P [ r(1+r)n ] / [ (1+r)n − 1 ], where M is the level periodic payment, P is principal, r is the periodic interest rate, and n is the total number of payments. This isn’t an arbitrary bank trick; it’s the present value of an ordinary annuity solved for the payment.
Think of it this way: a lender fronts you P today. In return, you hand them n payments of M. Each payment is discounted back to today by the rate r. The sum of those discounted payments must equal P. The formula is just algebra rearranging that equivalence. The term (1+r)n is the future value factor of $1, and the denominator compresses the geometric series of discount factors.
Why the Periodic Rate Trips Up Beginners
The single most common error I see is plugging the annual percentage rate (APR) directly into r. If your loan quotes 6% yearly and compounds monthly, r is 0.06 ÷ 12 = 0.005, not 0.06. Miss this and your payment will be wildly too high. The Consumer Financial Protection Bureau notes that amortization assumes compounding frequency matches payment frequency unless stated otherwise.
Another non-obvious insight: the formula assumes payments occur at the end of each period (ordinary annuity). If payments are due at the beginning (like many leases), the math shifts to an annuity-due model, and you divide M by (1+r). Most people don’t realize that a seemingly tiny timing change alters the total interest paid by a measurable margin over 30 years.
Hand-Derivation Shortcut for the Skeptical
If you want to verify the formula without spreadsheets, write out the loan balance after each payment: B1 = P(1+r) − M; B2 = B1(1+r) − M = P(1+r)2 − M(1+r) − M. Continue to Bn = 0. Factoring M yields M[ (1+r)n-1 + … + 1 ] = P(1+r)n. The bracket is a geometric sum equal to [ (1+r)n − 1 ] / r. Solve for M and you’re back to the textbook equation. Doing this once on paper permanently removes the “magic” feeling.
Solving for Unknown Term or Rate
The same formula rearranges if you need n instead of M: n = −ln(1 − Pr/M) / ln(1+r). I used this when a borrower asked, “If I pay $450 a month on $5,000 at 6%, when am I done?” Hand-solving with log tables (or a basic calculator) gave 11.4 months, confirming the 12-month schedule with a small final payment. This flexibility is why understanding the derivation beats memorizing a calculator button.
A Real-Life Hand Calculation Example
Let’s use a small but realistic loan: $5,000 at 6% annual interest, amortized over 12 months. This is the kind of short-term personal loan many credit unions offer. We’ll compute the schedule manually.
Step 1: Periodic rate r = 0.06 / 12 = 0.005. n = 12. P = 5000. Plug into formula: M = 5000 × [0.005 × (1.005)12] / [(1.005)12 − 1]. Using (1.005)12 ≈ 1.0616778, M = 5000 × 0.005 × 1.0616778 / 0.0616778 = 26.5419 / 0.0616778 ≈ $430.33.
Building the First Three Rows by Hand
Month 1: Beginning balance = $5,000. Interest = 5000 × 0.005 = $25.00. Principal = 430.33 − 25.00 = $405.33. Ending balance = 5000 − 405.33 = $4,594.67.
Month 2: Beginning balance = $4,594.67. Interest = 4594.67 × 0.005 = $22.97 (rounded). Principal = 430.33 − 22.97 = $407.36. Ending balance = 4594.67 − 407.36 = $4,187.31.
Month 3: Interest = 4187.31 × 0.005 = $20.94. Principal = 430.33 − 20.94 = $409.39. Ending balance = $3,777.92. Notice principal creeps up while interest falls—the hallmark of amortization.
The Full 12-Month Schedule at a Glance
Below is the complete hand-built table. Rounding to cents each month causes a tiny residual; in practice you adjust the final payment by a few cents. This is exactly the type of printable DIY template we’ll discuss later.
| Mo | Begin Bal | Payment | Interest | Principal | End Bal |
|---|---|---|---|---|---|
| 1 | 5000.00 | 430.33 | 25.00 | 405.33 | 4594.67 |
| 2 | 4594.67 | 430.33 | 22.97 | 407.36 | 4187.31 |
| 3 | 4187.31 | 430.33 | 20.94 | 409.39 | 3777.92 |
| 4 | 3777.92 | 430.33 | 18.89 | 411.44 | 3366.48 |
| 5 | 3366.48 | 430.33 | 16.83 | 413.50 | 2952.98 |
| 6 | 2952.98 | 430.33 | 14.76 | 415.57 | 2537.41 |
| 7 | 2537.41 | 430.33 | 12.69 | 417.64 | 2119.77 |
| 8 | 2119.77 | 430.33 | 10.60 | 419.73 | 1700.04 |
| 9 | 1700.04 | 430.33 | 8.50 | 421.83 | 1278.21 |
| 10 | 1278.21 | 430.33 | 6.39 | 423.94 | 854.27 |
| 11 | 854.27 | 430.33 | 4.27 | 426.06 | 428.21 |
| 12 | 428.21 | 430.33 | 2.14 | 428.19 | 0.02* |
*The 2-cent leftover is rounding artifact; final payment would be $430.35 to zero out. When I first built this schedule, I forgot to adjust the last row and thought I’d made an algebra error—that’s a normal part of hand calculation.
Scaling to a Mortgage: First Two Rows of a $200k Loan
To show the method travels, take $200,000 at 4% annual, 30 years (n=360, r=0.003333). M = 200000 × 0.003333 × (1.003333)360 / ((1.003333)360−1) ≈ $954.83. Month 1 interest = 200000 × 0.003333 = $666.67; principal = 288.16; balance = 199,711.84. Month 2 interest = 665.71. The same loop, just more rows. Hand-calculating these two rows reveals why early mortgage payments feel like they barely touch principal.
How to Make Your Own Amortization Schedule
How do I make my own amortization schedule? You need four columns and a starting balance. Grab a sheet of grid paper or our free printable worksheet (linked conceptually below) and label: Period, Beginning Balance, Interest, Principal, Ending Balance. Compute the fixed payment once with the formula, then iterate row by row. If you want to sanity-check your handwritten table, our Amortization Schedule Calculator reproduces the same numbers instantly.
The thing nobody tells you about DIY schedules: you must decide a rounding convention before you start. Banks typically round interest to the nearest cent at each step, which creates a small balloon or deficit at the end. If you round only the final payment, your intermediate rows won’t match the lender’s statement. Pick one rule and stick to it.
A Simple 5-Step Hand Workflow
- 1. Calculate periodic rate (annual rate ÷ payments per year).
- 2. Compute fixed payment M using the formula or a calculator.
- 3. For each period: Interest = Beginning Balance × r.
- 4. Principal = M − Interest; Ending Balance = Beginning − Principal.
- 5. Repeat until balance < 1 cent; adjust last payment for rounding.
This workflow is the same whether you have a 1-year micro-loan or a 30-year mortgage; only the row count changes. For a 360-row mortgage, hand calculation is tedious, but doing the first 6 rows by hand then switching to software is a powerful learning exercise.
Designing Your Free Printable Worksheet
Draw a landscape-oriented table with seven columns: Period, Begin Bal, Fixed Pmt, Interest, Principal, Extra Pmt, End Bal. Pre-print the formula “Interest = Begin × r” in a footer. Leave 40 rows per page so a car loan fits on one sheet. I keep a stack of these in my office; they’ve settled more borrower disputes than any software report because the borrower can see the pencil math.
What Does a 20-Year Amortization Schedule Mean?
A 20-year amortization schedule means the loan is structured so that with level payments every month, the principal reaches zero after exactly 240 payments (20 × 12). The schedule shows each of those 240 months broken into interest and principal. It does not necessarily mean the loan term or maturity is 20 years—some loans have a shorter balloon maturity but are amortized over 20 years, leaving a balance due earlier. For those structures, our Partial Amortization Calculator shows the remaining balloon.
The practical takeaway: “amortization period” controls how fast you build equity; “loan term” controls when the bank can call the balance. A 20-year amortization on a 5-year term means you’ll owe a large lump sum at year 5 even though the schedule assumes 20 years of payments. I once reviewed a business loan where the owner confused these and was shocked by the balloon.
Comparing 15-, 20-, and 30-Year Schedules
Shorter amortization periods front-load principal reduction. On a $200,000 loan at 5%, a 20-year schedule carries about $1,319 monthly payment versus $1,074 for 30-year. The 20-year plan saves roughly $60,000 in total interest. The trade-off is cash flow: higher monthly outlay. Hand-calculating a few rows of each clarifies this far better than a calculator snapshot.
In accounting, a 20-year amortization schedule for an intangible asset follows the same math but uses straight-line or effective-interest methods under GAAP; the schedule still allocates cost over time, though non-loan contexts may not involve a lender.
Handling Extra Payments and Lump Sums by Hand
Voluntary extra payments break the fixed-payment assumption but are easy to fold into a manual schedule. After computing your regular M, simply add any extra cash to the principal column for that month. The ending balance drops more, so next month’s interest (balance × r) is lower, and more of your regular M goes to principal. This creates a compounding acceleration.
Example from our $5,000 loan: Suppose after Month 1 you pay an extra $100 toward principal. Month 1 ending balance becomes $4,494.67 instead of $4,594.67. Month 2 interest = 4,494.67 × 0.005 = $22.47 (vs $22.97). Over the remaining 11 months, that $100 extra saves about $3.50 in interest and shaves a payment off the end. Early extras save more because they reduce the high-interest early balances.
Most people don’t realize: an extra $1,000 applied in Year 1 of a 30-year mortgage can erase 3–4 future payments, while the same $1,000 in Year 29 saves almost no interest. Hand schedules make this visible immediately.
Lump Sum Mid-Schedule Example
Imagine you receive a $1,000 tax refund at Month 6 of the original loan. Your Month 6 ending balance was $2,537.41. Apply $1,000 extra principal: new balance $1,537.41. Month 7 interest drops to $7.69 (vs $12.69), principal portion of M rises accordingly. You’ll now pay off in about 4 more months instead of 6. The hand table simply continues with the lower balance; no new formula needed.
Building a Revised Schedule Manually
To redo the schedule, keep M the same but insert an “Extra Principal” column. Subtract extra from ending balance. When balance would go negative, stop—that’s your new payoff month. No spreadsheet required. This conceptual approach answers the “make my own” intent for non-standard payments, a gap most Excel tutorials ignore unless you know pivot tables.
Variable-Rate and Balloon Schedules: Edge Cases
Fixed-rate hand calculation is clean; variable-rate loans are messier but still doable. When the index adjusts (say after 5 years), you recompute M using the remaining n and the new r, with current balance as P. Write a divider row in your worksheet marking the reset. I’ve done this for an ARM when the Fed hiked rates; the payment jumped $140, and the hand table prevented a budgeting surprise.
Worked ARM Reset Example
Take a $200,000 loan, 5/1 ARM: first 5 years at 3% (r=0.0025, n=360, M≈$843). After 60 months, balance ≈ $179,500. New rate 5% (r=0.004167), remaining n=300. New M = 179500 × 0.004167 × (1.004167)300 / ((1.004167)300−1) ≈ $1,051. Your hand schedule just gains a new header row and continues. The interest column jumps, revealing the repricing instantly.
Balloon schedules (partial amortization) require a final row with a large principal catch-up. Your periodic M is calculated on a long amortization (e.g., 30 years) but the term is 5 years, so at period 60 you owe the remaining balance. In your hand table, simply sum the ending balance at the balloon date and label it “Balloon Due.” This is where the partial amortization concept diverges from full amortization.
Lease and Daily-Compounding Oddities
Some leases amortize differently, using annuity-due or actuarial methods. While outside our core example, the same hand principles apply: identify the rate per period, compute payment, iterate. If you deal with leases often, software is smarter, but understanding the loop protects you from hidden interest bumps.
Common Calculation Mistakes (And What Goes Wrong)
Beyond the rate-period mismatch, here are errors I’ve personally made or audited:
- Using 365-day simple interest on a monthly loan: Banks often compound monthly; mixing day-count conventions creates a 0.2% payment error that grows.
- Ignoring payment timing: If your loan payment is due on the 1st but disbursed on the 15th, the first period may be a “long” or “short” month. Hand schedules must reflect actual days or the lender’s stub period.
- Rounding too early: Rounding M to the dollar before building rows distorts later balances.
- Assuming extra payments auto-recalculate: Some servicers apply extra to future interest, not principal, unless you specify. Your hand schedule assumes principal application; verify with the bank.
- Forgetting the final cent adjustment: Leftover fractions accumulate; always reconcile last row to zero.
When I first tried to help a family member with a farm equipment loan, I used a 30-day month assumption on a loan that counted actual days; the ending balance was off by $40 after a year. The lesson: match the contract’s day-count and compounding exactly.
Pre-Calculation Audit Checklist
- Confirm rate is per payment period, not annual.
- Confirm n matches total planned payments, not years.
- Confirm payment timing (beginning vs end).
- Choose rounding rule (round each step vs final only).
- Verify first row interest = P × r exactly.
A Printable DIY Worksheet Framework
To fill the gap left by calculator-only articles, here is a reusable mental model you can transcribe to paper. Draw a table with these headers: Period | Begin Bal | Fixed Pmt | Interest | Principal | Extra | End Bal. Pre-fill the Interest formula “= Begin × r” as a note. Below the table, write your loan parameters: P, annual rate, payments/year, n, computed M.
This framework doubles as a decision matrix for method choice:
| Scenario | Best Method | Why |
|---|---|---|
| Learning / <24 payments | Hand + printable worksheet | Builds intuition, no software needed |
| Tracking extra payments | Hand with Extra column or calculator | Visible principal impact |
| 30-yr mortgage with rate changes | Excel or online tool | Scale and accuracy |
| Quick payoff quote | Calculator | Speed |
Can Excel calculate amortization schedule? Absolutely—Excel’s PMT, IPMT, and PPMT functions generate rows in seconds, and it’s ideal for large loans. But the hand worksheet remains superior for understanding and for situations where you lack software. Use Excel to extend, not replace, the foundational skill.
The DIY Amortization Decision Matrix in Practice
I recommend a hybrid: hand-build rows 1–6 of any loan, then if n > 60, export to software. This catches input errors early. For a recent client with a 7-year equipment loan, the hand rows showed the quoted payment was $12 too high because the lender used a 365-day year; we corrected it before signing.
When to Use Hand Calculation vs. Spreadsheets vs. Calculators
Each approach has trade-offs. Hand calculation is transparent and teaches the formula derivation, but it’s slow and error-prone past 50 rows. Excel scales and handles variable rates with formulas, yet hides the math if you just drag cells. Calculators like our amortization tool are fastest but offer zero insight. My recommendation: hand-build the first 3–6 rows of any new loan, then switch to software for the remaining rows.
One honest limitation: hand schedules can’t easily model daily compounding or payment holidays without expanding the table to days. In those cases, accept that a calculator is the right tool. The goal of this guide was to ensure you could calculate by hand, not that you always should.
Final Takeaway for the DIY Borrower
If you remember nothing else, remember the loop: Interest = Balance × r; Principal = M − Interest; New Balance = Old − Principal. Master that with a pencil, and you’ll never be intimidated by a lender’s statement again. Grab the printable worksheet concept above, pick a small loan, and try it this weekend.