Free Amortization Schedule Excel: How to Build Your Own Loan Tracker

You are probably reading this at a kitchen table covered in statements, or staring at a glowing screen at midnight, wondering where all your money actually went this month. You just signed for a mortgage, or you are looking at a personal loan, and the thought of handing over thousands of dollars in interest makes your stomach turn. You want to see the math. You want to know if making that extra £100 payment next month actually puts a dent in the principal, or if it just vanishes into the bank's profit margin.

So you typed "free amortization schedule excel" into a search bar. You do not want a complicated financial textbook. You do not want to pay £30 for a software subscription to manage your own debt. You just want a clean spreadsheet that lays out every single month of your loan, shows you the split between principal and interest, and lets you breathe a little easier knowing exactly where you stand.

Let us build that spreadsheet together.

Why the Banks' Loan Summaries Are Not Enough

When you take out a loan, the lender gives you a payment figure. They tell you that every month, like clockwork, you will pay £750. What they often hide in the fine print—or bury behind obscure customer portal menus—is the mechanical reality of how that money is divided.

In the beginning, amortization is heavily front-loaded with interest.

If you look at your first payment on a new loan, you might be shocked to see that £500 of that £750 goes straight to interest, while only £250 chips away at the actual debt. You feel like you are running on a treadmill. You made a big payment, but your loan balance barely moved.

This is why downloading a free amortization schedule excel template is a game-changer. It strips away the mystery. When you can see month 12, month 60, and month 360 laid out in rows, the psychological weight of debt shifts. It stops being a vague, terrifying monster and starts being a finite math problem with an end date.

Finding or Building Your Spreadsheet

You have two choices when looking for an Excel amortization schedule. You can download a pre-made template, or you can build a simple one from scratch using Excel's built-in financial formulas. Building it yourself is actually better, because once you understand how the columns talk to each other, you can test "what-if" scenarios that generic templates cannot handle.

Let us look at what goes into a standard loan tracker:

• The Inputs (At the top): Loan amount, annual interest rate, loan term in years, and start date.
• The Columns: Payment number, payment date, beginning balance, total payment, principal portion, interest portion, and ending balance.

If you prefer not to mess with formulas right this second and just want to test your numbers visually on the web, you can always plug your details into a tool like the Amortization Calculator to see the exact breakdown instantly, which gives you the exact blueprint you need to copy over into Excel.

A Worked Example: Meet Sarah and Her Car Loan

Let us walk through a concrete example so you can see how the numbers flow.

Meet Sarah. Sarah just bought a reliable used car to get to her new job. She borrowed £15,000 at a fixed interest rate of 6% per year, to be paid back over 5 years (60 months).

She wants to build an Excel sheet to track her progress. Here is how the math works out behind the scenes, and how she sets up her spreadsheet rows.

Step 1: Calculate the Monthly Payment

In Excel, Sarah uses the PMT function to find her fixed monthly payment. The formula looks like this: =PMT(Rate, Nper, Pv)

Translating that to Sarah's numbers:

• Rate: 6% annual rate divided by 12 months = 0.06 / 12 = 0.005 (or 0.5% per month)
• Nper (Total number of payments): 5 years × 12 months = 60
• Pv (Present value / Loan amount): 15000

So Sarah types =PMT(0.06/12, 60, -15000) into an Excel cell. Excel spits out her monthly payment: £289.99.

Step 2: Build the First Row of the Schedule

Now, Sarah sets up her table columns in Excel:

• Column A (Month): 1
• Column B (Beginning Balance): £15,000.00 (Her starting loan amount)
• Column C (Total Payment): £289.99 (Referencing her PMT formula)
• Column D (Interest Paid): She calculates this by multiplying her beginning balance by the monthly rate. (=B2 * (0.06/12)). For month 1, this is £15,000 × 0.005 = £75.00.
• Column E (Principal Paid): This is her total payment minus the interest. (=C2 - D2). So, £289.99 - £75.00 = £214.99.
• Column F (Ending Balance): Her beginning balance minus the principal paid. (=B2 - E2). So, £15,000 - £214.99 = £14,785.01.

Step 3: Drag Down to Month 60

For Month 2, Sarah sets her Beginning Balance (Column B) equal to the Ending Balance of Month 1 (£14,785.01).

Then, she highlights the formulas in row 2 and drags them down to row 61 (representing months 1 through 60).

Instantly, the entire life of her car loan is mapped out in front of her. She scrolls down to Month 60 and sees the ending balance hit £0.00. More importantly, she looks at Month 30—the exact halfway point of her loan timeline.

Because of how amortization works, she notices something wonderful at Month 30: the interest portion has dropped from £75.00 down to around £40, meaning a much larger chunk of her £289.99 is finally eating away at the principal. Seeing that visual progression makes her feel infinitely more in control.

Common Mistakes That Break Your Excel Sheet

When people build or download a free amortization schedule excel file, things often go wrong in predictable ways. If your ending balance refuses to hit zero at the end of the term, or your numbers look wildly off, check for these three common traps:

1. Mixing Up Annual and Monthly Rates

This is the number one culprit. If your annual interest rate is 5%, you cannot just put 5 into your monthly interest calculation. You must divide it by 12. If your spreadsheet is showing that your first month's interest on a £200,000 mortgage is £10,000, you forgot to divide by 12.

2. Forgetting Negative Signs in Excel Functions

Excel's financial formulas (PMT, IPMT, PPMT) treat money flowing away from you (payments) as negative, and money flowing to you (the loan disbursement) as positive. If you do not put a minus sign in front of your loan amount in the PMT function, Excel might return an error or output a negative payment that throws off your balance subtraction formulas.

3. Hardcoding Values Instead of Using Cell References

If you type 75 as the interest for month one instead of writing a formula that multiplies the beginning balance by the rate, your entire sheet becomes a dead document. The beauty of a proper amortization schedule is its dynamism. If you change your loan amount from £15,000 to £16,000 at the top, every single row beneath it should recalculate automatically. If your numbers do not update, check for hardcoded text.

What Changes the Answer? (The Power of Extra Payments)

Here is where owning your own Excel spreadsheet really pays off. A standard loan amortization schedule assumes you pay the exact minimum amount every single month, right on the due date, for the entire duration of the loan.

Real life is rarely that rigid.

What happens if you get a £500 work bonus and throw it at Sarah's car loan in month six? Or what if Sarah decides to round her monthly payment up from £289.99 to an even £320 just to make the math easier?

In a generic bank portal, you might not easily see the long-term impact of that choice. But in your custom Excel sheet, you can add an extra column for "Extra Principal Payment."

If Sarah adds just £30 extra to her payment every single month:

• Her monthly cash flow barely feels the pinch—it is the cost of a couple of coffees.
• Her spreadsheet will show that her 60-month loan drops down to 54 months.
• She shaves half a year off her loan duration and saves hundreds of pounds in total interest over the life of the agreement.

When you see that happen in your own spreadsheet cells, the abstract concept of "saving money on interest" becomes a tangible victory. You realize that you are not just a passive passenger paying a bill; you have active levers you can pull to shorten your timeline.

The Psychological Shift of Seeing the Numbers

Money anxiety thrives in the dark. When you do not know the exact mechanics of what you owe, your brain imagines the worst-case scenario. Every bill feels like a permanent fixture of your life, an endless tax on your existence.

The moment you map out a free amortization schedule excel file, the mystery evaporates. You see the peak of the interest mountain in the early months, you watch the slope flatten out in the middle years, and you see the clear, definite flat ground at the end where the balance hits zero.

You realize that every single payment—even the frustrating ones where most of the money goes to interest—is a brick laying down a road out of debt.

Take an hour this weekend. Open up a blank spreadsheet, set up your inputs, drop in the PMT formula, and drag those rows down. Look at your debt not as an intimidating cloud over your head, but as a grid of numbers waiting to be crossed off.

(Note: The calculations and examples shared here are for general educational purposes to help you understand loan mechanics and do not constitute formal financial advice.)

---

For quick calculations on the go when you are away from your desktop spreadsheet, you can also check your figures using the free Finlaa app.

Frequently Asked Questions

Can I use Google Sheets instead of Microsoft Excel for an amortization schedule?

Yes, absolutely. Google Sheets uses the exact same financial formulas (PMT, IPMT, PPMT) with the exact same syntax as Microsoft Excel. You can build this exact same schedule in Google Drive for free without needing any installed software, and it will update across your phone and laptop seamlessly.

Why does my final payment in Excel leave a few pennies leftover?

Because of rounding fractions of a penny across dozens or hundreds of months, amortization schedules often end month 60 or month 360 with a tiny residual balance of £0.02 or -£0.01. This is normal rounding variance. In practice, your lender's final payment will adjust automatically to clear the exact remaining balance to zero.

How do I add extra payments into an existing Excel template?

To account for extra payments, add a dedicated column labeled "Extra Payment" next to your standard payment column. In your Ending Balance formula, subtract not only the standard principal portion, but also add that extra payment cell into the subtraction. Your subsequent beginning balances will automatically shrink faster, shortening your overall loan term.