How to Calculate Loan Amortization (Step by Step)

I was staring at my own loan spreadsheet and it still felt like magic

I was sitting at my kitchen table with a mug of coffee, a half-finished spreadsheet, and a loan statement that kept insisting I owed basically the same amount month after month. And I’m thinking, “Wait… I’m paying every month. Why does the balance barely move?” I’d built models for work, I’d managed my own money for years, and still, amortization felt like one of those words people nod at like they get it (I nodded too). It took me a while to realize the math wasn’t hard — it’s just annoyingly easy to do slightly wrong.

So if you’re trying to compare two loans, figure out how much interest you’ll pay, or see what happens if you throw an extra 200 at the principal each month, this is the step-by-step that actually matches how you’d use it in real life.

And yes, the balance drops… just not right away.

Amortization, in plain language (not the textbook version)

The thing is, a loan payment is usually two things smashed together: interest and principal. Interest is the “rent” you pay to borrow the money, and principal is the part that actually reduces what you owe. Early in the loan, the interest portion is big because your balance is big. Later, the interest portion shrinks because the balance shrinks. That’s the whole trick.

A lot of people think “fixed payment” means “fixed interest.” It doesn’t. The payment stays the same, but the mix changes every month.

And a quick definition that clears up a ton: an amortization schedule is just a month-by-month table that shows payment, interest, principal, and remaining balance. That’s it — one schedule, a bunch of rows.

If you want to jump straight to tools, I built these because I got tired of re-checking my own spreadsheets:

Here’s an embedded version if you just want to plug numbers in and see the schedule.

The step-by-step: how to calculate loan amortization by hand (without losing your mind)

I’m going to use a real-ish scenario: a 300,000 loan, 30 years, 6.5% annual interest, paid monthly. That’s a pretty normal “mortgage math” example, but the same steps work for car loans, personal loans, whatever — as long as it’s an amortizing loan with fixed payments.

So what do you actually calculate each month?

1. Figure out the monthly payment (fixed).
2. Compute that month’s interest (balance × monthly rate).
3. Principal paid = payment − interest.
4. New balance = old balance − principal paid.
5. Repeat for the next month (and watch the interest portion slowly shrink).

Now let’s actually run the numbers, because that’s where it clicks.

Inputs (rounded a bit):

• Principal P = 300,000
• Annual rate = 6.5% so monthly r = 0.065 / 12 = about 0.0054167
• Term = 30 years so n = 360 payments

Step 1: Monthly payment
Using the formula above, the payment comes out around 1,896 per month (give or take a couple bucks depending on rounding). If you’ve ever looked at a mortgage quote and thought “that seems low for a 300,000 loan,” remember: that’s principal + interest only, not taxes/insurance/HOA (those are separate buckets).

Step 2: Month 1 interest
Interest = balance × r = 300,000 × 0.0054167 ≈ 1,625

Step 3: Month 1 principal
Principal = payment − interest = 1,896 − 1,625 ≈ 271

Step 4: New balance
New balance ≈ 300,000 − 271 = 299,729

So yeah… you pay almost 1,900 and only about 270 actually knocks down the balance. That’s the part that makes people mad (and honestly, I get it). But it’s not a scam — it’s just how amortization works when the balance is big and the rate is non-trivial.

Now do month 2 the same way, but using the new balance. The interest drops a tiny bit, principal rises a tiny bit, and the schedule slowly tilts in your favor.

Here’s what the first few months look like with rounded numbers (don’t obsess over the pennies; your lender’s schedule will be exact to the cent, but the pattern is what you’re after).

And here’s the part most people miss: amortization is a loop. Once you have the payment, the rest is just repeating steps 2–4 for 360 rows. That’s why calculators are handy — not because it’s “advanced,” but because nobody wants to copy formulas down a spreadsheet 360 times and then wonder if they referenced the wrong cell.

But if you’re building your own model (or checking a lender), the monthly interest line is the tell. If the interest isn’t exactly prior balance × monthly rate, something’s off.

So why does everyone get this wrong? Because they try to compute principal first. Interest comes first. Always.

The part that saves you real money: extra payments (and how to model them)

I’m going to be a little opinionated here: if you’re going to “pay extra,” you should be crystal clear about what your lender does with it. Some places apply it to next month’s payment (which feels nice but doesn’t always reduce interest much). What you typically want is extra principal reduction — meaning it immediately lowers the balance, which lowers future interest.

Here’s the practical way to model it in your amortization schedule:

• Keep your normal payment the same.
• Add an “extra principal” column (like 200 per month, or maybe 5,000 once a year).
• Each month: New balance = Old balance − (scheduled principal + extra principal).

And you’ll see something kind of satisfying: the schedule ends early. Like, not “a little early.” Sometimes years early. That’s a lot of interest you don’t pay!

If you want to run the “what if I pay an extra 200 monthly?” scenario fast, use

One more thing (because I’ve personally tripped on this): if you’re comparing “extra payment” vs “invest the difference,” that’s not an amortization question. That’s a separate return-and-risk conversation. Amortization will tell you the guaranteed savings in interest, which is still super useful, but it won’t tell you what the market does next year.

Common gotchas I’ve seen (including in my own sheets)

But the spreadsheet only works if your assumptions match reality.

Here are the mistakes that keep showing up, over and over:

• Using annual rate as monthly rate. If you use 0.065 instead of 0.065/12, your schedule will look like a disaster.
• Mixing compounding conventions. Most consumer loans are quoted as an annual rate but applied monthly. Keep it consistent.
• Forgetting taxes and insurance aren’t part of amortization. Your “total payment” might be 2,450, but amortization is only the loan part.
• Rounding too early. Round for display, not for the actual math, or your ending balance will be weirdly not-zero.
• Refi comparisons without fees. If you’re using

So yeah, if your schedule ends with a balance of 43.12 after 360 payments, it’s probably just rounding. But if it’s off by 4,300, something’s broken.

FAQ

If you only take one thing from all this: amortization is just repeating the same three lines — interest, principal, new balance — and the “mystery” goes away once you see month 1 and month 2 side by side.

And if you don’t want to build the sheet, that’s fine too. That’s literally why I built ProCalc.ai.