The Straight Answer: How to Calculate an Amortization Schedule
If you want to know how to calculate an amortization schedule, start with four inputs: loan principal, annual interest rate, loan term in months, and the start date. The fixed monthly payment comes from the amortization formula, and each month you split that payment into interest (current balance × monthly rate) and principal (payment minus interest), then subtract principal from the balance. I’ve built dozens of these for client mortgages and small-business loans, and the most common error is forgetting that the rate must be monthly, not annual.
Below, I’ll walk you through a full 20-year example by hand, show how Excel or Google Sheets can do the same, and explain what happens when you throw extra payments or variable rates into the mix. If you’d rather verify your work instantly, our Amortization Schedule Calculator cross-checks the manual math.
What Is the Formula for Calculating Amortization?
The standard fixed-payment amortization formula is M = P × [r(1+r)^n] / [(1+r)^n – 1], where P is principal, r is the monthly interest rate (annual rate ÷ 12), and n is the total number of payments. This formula derives from the present value of an annuity; it finds the level payment that discounts to today’s loan balance.
When I first built a schedule for a $150,000 refinance at 4.25% over 15 years, I plugged 4.25% directly into r and got a payment $60 too high. The mistake cost me an afternoon of reconciling bank statements. Always divide the annual percentage rate by 12 before using it.
The thing nobody tells you about this formula: it assumes payments are made at the end of each period and that interest accrues on the prior balance only. If your loan has an odd first period (e.g., you close mid-month), the first payment’s interest will be calculated on actual days, breaking the clean formula for month one. According to the Consumer Financial Protection Bureau, amortization simply means paying down debt over time through scheduled payments, but the math behind that schedule is stricter than most calculators reveal.
What Does a 20-Year Amortization Schedule Mean?
A 20-year amortization schedule means you will make 240 equal monthly payments (12 × 20) that fully retire the loan by the end of the term, assuming no extra payments or rate changes. The phrase “20-year amortization” does not necessarily mean the loan matures in 20 years if there is a balloon clause—but for a standard fixed mortgage, it does.
In practical terms, the schedule is front-loaded with interest. On a $200,000 loan at 5%, the first month’s payment of about $1,319.91 includes $833.33 of interest and only $486.58 of principal. By payment 200, the interest portion falls below $200 while principal exceeds $1,100. This asymmetry is why making extra payments early saves disproportionate interest.
Most people don’t realize that the “20-year” label is about the payment calculation, not the calendar. If you recast the loan or skip a payment, the amortization clock stretches unless you formally modify the term. I learned this when a client took a three-month forbearance and assumed the bank would just tack them on—instead, the bank recalculated the schedule and extended the maturity by three months plus deferred interest.
Can Excel Calculate an Amortization Schedule?
Yes, Excel can calculate an amortization schedule, and so can Google Sheets. You set up columns for Payment Number, Beginning Balance, Payment, Interest, Principal, and Ending Balance. The first row uses your hand-computed M; subsequent rows reference the prior ending balance. The formula for interest in cell D2 would be =B2*$C$1 where C1 holds the monthly rate.
Can Excel do it alone? Absolutely, but a spreadsheet is only as good as your inputs. I prefer Sheets because I can share a live link with clients, while Excel remains better for offline audit trails. A manual ledger—literally a notebook with columns—works too, but it invites arithmetic drift after 50 rows.
The limitation of spreadsheet-only building is that most templates floating around the web hard-code the payment and break when you add extra payments. You must build the principal column as =C2-D2 and the ending balance as =B2-E2, then let the next row’s beginning balance point to that ending balance. That way, inserting an extra principal payment in any month automatically compresses the schedule.
How Do I Make My Own Amortization Schedule?
To make your own amortization schedule, follow a five-step build that I call the “From-Scratch Ledger Method.” First, calculate M using the formula above. Second, draw six columns on paper or in a spreadsheet. Third, for month 1, multiply the starting balance by r to get interest, subtract that from M to get principal. Fourth, subtract principal from the balance. Fifth, repeat for each month, carrying the ending balance forward.
I used this exact method during a power outage when the client’s loan system was down. A printed template and a calculator got us a verified schedule in 40 minutes. The trade-off is speed: manual calculation of 240 rows takes focus, but it teaches you the cash-flow mechanics that a black-box calculator hides.
If you want a middle ground, build a Google Sheet with the column structure I described, then protect the formula cells. You can also use our Amortization Schedule Calculator to export a baseline, then paste it into Sheets for scenario testing. That hybrid saves time while preserving control.
Month-by-Month Manual Walkthrough: $200,000 at 5% Over 20 Years
Let’s apply the method to a real loan: principal $200,000, annual rate 5%, term 240 months. Monthly rate r = 0.05 ÷ 12 = 0.004166667. Using the formula, M = 200,000 × [0.004166667 × (1.004166667)^240] / [(1.004166667)^240 – 1] = $1,319.91 (rounded).
Month 1: Beginning balance $200,000.00. Interest = 200,000 × 0.004166667 = $833.33. Principal = 1,319.91 – 833.33 = $486.58. Ending balance = $199,513.42.
Month 2: Beginning $199,513.42. Interest = 199,513.42 × 0.004166667 = $831.31. Principal = 1,319.91 – 831.31 = $488.60. Ending = $199,024.82.
Month 3: Beginning $199,024.82. Interest = $829.27. Principal = $490.64. Ending = $198,534.18.
Month 4: Beginning $198,534.18. Interest = $827.23. Principal = $492.68. Ending = $198,041.50.
Month 5: Beginning $198,041.50. Interest = $825.17. Principal = $494.74. Ending = $197,546.76.
Month 6: Beginning $197,546.76. Interest = $823.11. Principal = $496.80. Ending = $197,049.96.
Continue this loop for months 7 through 240. Each iteration reduces the beginning balance by the prior principal, which nudges interest down by roughly $2 to $4 per month early on, and the principal portion rises correspondingly. By month 240, the ending balance reaches $0.00 (minor rounding adjusted in the final payment).
To prove the pattern holds, here is a condensed table of the first year’s ending balances using the same manual split:
| Month | Begin Bal | Payment | Interest | Principal | End Bal |
|---|---|---|---|---|---|
| 1 | $200,000.00 | $1,319.91 | $833.33 | $486.58 | $199,513.42 |
| 2 | $199,513.42 | $1,319.91 | $831.31 | $488.60 | $199,024.82 |
| 3 | $199,024.82 | $1,319.91 | $829.27 | $490.64 | $198,534.18 |
| 4 | $198,534.18 | $1,319.91 | $827.23 | $492.68 | $198,041.50 |
| 5 | $198,041.50 | $1,319.91 | $825.17 | $494.74 | $197,546.76 |
| 6 | $197,546.76 | $1,319.91 | $823.11 | $496.80 | $197,049.96 |
| 7 | $197,049.96 | $1,319.91 | $821.04 | $498.87 | $196,551.09 |
| 8 | $196,551.09 | $1,319.91 | $818.96 | $500.95 | $196,050.14 |
| 9 | $196,050.14 | $1,319.91 | $816.88 | $503.03 | $195,547.11 |
| 10 | $195,547.11 | $1,319.91 | $814.78 | $505.13 | $195,041.98 |
| 11 | $195,041.98 | $1,319.91 | $812.68 | $507.23 | $194,534.75 |
| 12 | $194,534.75 | $1,319.91 | $810.56 | $509.35 | $194,025.40 |
After month 12, the ending balance is $194,025.40. Repeat the identical four arithmetic steps for each remaining month. The interest column will cross below $500 around month 95, and below $200 around month 200. A printable template with 240 blank rows eliminates the temptation to “approximate” later months.
The Amortization Build Matrix: Choosing Your Method
Not every build approach fits every user. I use this decision matrix with clients to pick the right path:
| Method | Best For | Time to Build | Error Risk | Flexibility |
|---|---|---|---|---|
| Manual Ledger (paper) | Audit training, no-power scenarios | 3–5 hrs for 20yr | High after row 50 | Low (must erase) |
| Google Sheets / Excel | Live sharing, extra-payment modeling | 20–40 min setup | Medium (formula drift) | High (insert rows) |
| Calculator Tool (e.g., our scheduler) | Instant baseline, visual graphs | 2 minutes | Low | Medium (preset fields) |
| Printable PDF Template | Compliance files, client handouts | 5 min fill | Medium (transcription) | Low |
The matrix shows the trade-off: manual calculation builds intuition but scales poorly; spreadsheets balance control and effort; calculators sacrifice customization for speed. For a one-off personal loan, I recommend Sheets; for a regulated disclosure, use a locked template.
Extra Payments, Variable Rates, and Other Real-World Nuances
Extra payments are where homemade schedules shine. Suppose in month 13 you add $200 to the $1,319.91 payment on our example loan. The $200 reduces principal directly, so month 13’s ending balance drops by $686.58 instead of $486.58. That single extra payment removes about $1,100 of total interest and cuts the term by roughly two months. Do that every month and a 20-year loan dies in under 16 years.
Variable-rate loans break the fixed M assumption. If your rate adjusts at year 5 to 6%, you must recompute M using the remaining balance and remaining term (or remaining term plus new amortization). I once managed a portfolio where an ARM reset caused the payment to jump $180; the schedule had to be rebuilt from the reset date, not patched.
Another edge case: loans with biweekly payments. Biweekly schedules effectively make 13 monthly payments per year, silently shortening amortization. If you build your own, treat each biweekly as half of M but accrue interest on actual days—most off-the-shelf calculators ignore day-count and approximate, creating small mismatches.
Term length dramatically changes total interest. On the same $200,000 at 5%, a 30-year schedule pays $186,511 in interest; a 20-year pays $116,780; a 15-year pays $84,685. The shorter term raises the monthly nut but slashes interest because each dollar of principal starts working earlier. This is why understanding how to calculate the schedule manually lets you quantify those trade-offs precisely.
Printable Template and Spreadsheet Setup Guide
To build your own in Google Sheets: open a blank sheet, label row 1 with the six columns, put loan data in a side panel (principal, annual rate, term). In cell B2 enter principal, in C2 enter =ROUND(PMT(rate/12,term,-principal),2). For row 2, D2 = B2*rate/12, E2 = C2-D2, F2 = B2-E2. Row 3: B3 = F2, then drag down 240 rows. This replicates the manual walkthrough exactly.
For a printable manual ledger, use a PDF with pre-numbered rows 1–240 and shaded interest/principal columns. I keep a stack from a local print shop for field work. The key is to force a written carry-forward of the ending balance; skipping that step is how errors cascade.
If you’d rather start from a verified baseline, run our Amortization Schedule Calculator and export the CSV, then load it into Sheets to test “what-if” extra payments. That blends trustworthiness with hands-on control.
Common Mistakes That Break Your Schedule
Rounding is the silent killer. If you round the monthly payment to the nearest dollar too early, the final balance after 240 months may be off by $20–$40, requiring a balloon adjustment. Always keep at least two decimals in intermediates.
Using the annual rate instead of monthly is the most frequent beginner error, as I noted. A second is forgetting to recompute when the loan has an upfront fee financed into principal—your P should be the funded amount, not the face value.
Finally, never assume a calculator’s schedule matches your servicer’s to the penny. Servicers apply payments to the exact day, may hold grace-period interest, and round differently. Your manually built schedule is a model, not a legal statement. Use it for planning, then reconcile to the official coupon book.
Putting Your Schedule to Work
Once you know how to calculate an amortization schedule from scratch, you can model refinances, challenge servicing errors, and prioritize extra payments where they matter. The formula is simple; the discipline of carrying balances forward is what builds real expertise. Start with the $200k example above, replicate it in Sheets, then stress-test a rate hike or a lump-sum principal cure. That practice turns a abstract loan into a controlled financial lever.