Loan Amortisation Table Excel

Building an Amortisation Schedule Without Losing Your Mind

The most common way people create a Loan Amortisation Table Excel is by typing out a bunch of formulas that look correct but quietly produce garbage numbers by month seven. I learned this the hard way when I was building a schedule for a commercial property refinance last year. The first twelve rows looked fine. Then I noticed the interest portion was actually increasing over time instead of decreasing. Turns out my rate division was silently rounding to zero in certain cells because I'd formatted the rate as a percentage but wasn't using the actual decimal value in the formula. Took me two hours to trace back to row three. You need five pieces of information sitting in cells somewhere near the top of your sheet. The loan amount goes in one cell. The annual interest rate goes in another. The loan term in months goes in a third. A fourth cell tracks whether payments happen monthly, biweekly, or quarterly. The fifth cell is either the start date of the loan or simply the number of periods, depending on which method you choose for the date column. I always put these inputs in a clearly separated block at the top, maybe cells B2 through B6, with labels in column A. This keeps the formulas below clean and makes it trivial to change one variable and watch the whole table update. It also stops you from accidentally referencing a numbered cell like B14 when you meant B4.

The Core Payment Formula

The monthly payment amount comes from the PMT function. The syntax is PMT(rate, nper, pv, [fv], [type]). Rate is your annual percentage divided by however many payments occur each year. For a standard monthly loan, that means dividing the annual rate by 12. Nper is the total number of payments across the life of the loan. Pv is the present value, which is the original loan amount, entered as a negative number if you want the payment to show as positive. Here is the exact formula I use in practice: =PMT($B$4/12, $B$5, -$B$2). The dollar signs lock the references so you can drag this formula down without breaking anything. If your loan has an odd first period or a balloon payment at the end, you can add the optional fv argument, but most consumer loans don't need it. Commercial loans sometimes do, and that is where people start running into trouble.

Building the Period-by-Period Breakdown

Each row represents one payment period. Column A is the period number, starting at 1. Column B is the payment date. Column C is the total payment amount, which stays constant. Columns D and E split that payment into the interest portion and the principal portion. Column F tracks the remaining balance after each payment. Column G, if you include it, shows the cumulative principal paid to date. The interest calculation for any given period uses IPMT. The formula looks like this: =IPMT($B$4/12, A2, $B$5, -$B$2). The second argument, A2, is the period number you are calculating for. That is the part that breaks when people copy the formula down because they forget to adjust or lock references properly. The principal portion uses PPMT the same way: =PPMT($B$4/12, A2, $B$5, -$B$2). The two amounts should always add up to your total payment from the PMT formula, give or take a cent of rounding error. The balance column is straightforward arithmetic. Start with the original loan amount in the first row. Subtract the principal portion from the previous row's balance to get the current row's balance. The formula in F3 would be =F2-E3, assuming E3 holds the principal for period 2. Copy that down. The final balance should equal zero, or very close to it if rounding introduces a small residual.

A Date Column That Actually Works

Most people skip the date column because it feels fiddly. It is not. Take the loan start date from your input section and add one month for each subsequent period using the EDATE function. In your first date cell, enter =EDATE($B$6, 0). In the cell below it, enter =EDATE(A2, 1) and copy down. EDATE handles month-end edge cases that simple addition does not, which matters if your loan starts on January 31st or February 28th. Without EDATE, you will get #VALUE! errors or wrong dates halfway through the schedule. Format the date column as a readable date, not as a serial number. Right-click the column, choose Format Cells, and pick a date format you can actually read. I use mmmm dd, yyyy because it is unambiguous when printed or shared with clients who do not live in the United States.

The Spreadsheet Function Alternative

If you are using Excel 365 or Excel 2021, there is a built-in function called AMORTIZATION.TABLE that generates the entire schedule automatically. The syntax is AMORTIZATIONTABLE(principal, rate, periods, [payment type]). You supply the inputs and Excel builds the table. It is fast, it is accurate, and it handles the date column correctly. The tradeoff is that it produces a static array, not a live formula-based schedule. If you change the principal or rate after generation, you have to regenerate the whole thing. I still prefer building the table manually because it stays dynamic and lets me insert additional columns for things like extra payments, fee schedules, or tax implications without recreating the core structure. When a loan has a variable rate or adjusts after a certain period, the standard amortisation table Excel approach breaks down because every period might need a different rate. I had a borrower with an ARM that adjusted annually for five years. My initial table used a single rate throughout, which was wrong. The workaround was to create a helper column for the periodic rate that changed each year, then reference that helper column inside the IPMT and PPMT formulas instead of using a constant rate. The payment amount itself does not change with a standard ARM adjustment unless the loan recalibrates, but the split between interest and principal shifts every time the rate changes. This required me to nest IF statements or use a LOOKUP table to pull the correct rate for each period based on the adjustment schedule. It added about twenty minutes of setup time but eliminated the systematic error that would have accumulated over the life of the loan. Forgetting to negate the principal in the PMT, IPMT, and PPMT functions is the single most common error. Excel treats pv as a cash flow direction, so a positive loan amount produces a negative payment. If you enter pv as negative, the payment comes out positive, which matches how people expect to see it. Mixing this up flips every number in your table to the wrong sign and you will not notice until you check the balance column and see it growing instead of shrinking.

Another mistake is using the annual rate directly in IPMT and PPMT without dividing by the payment frequency. =IPMT(B4, A2, B5, -B2) where B4 is the annual rate will produce wildly incorrect interest amounts because Excel interprets B4 as the per-period rate. Always divide by 12 for monthly payments, by 26 for biweekly, by 4 for quarterly. A third mistake is dragging formulas without locking input cell references. If your input block is in B2:B6 and you reference B2 without dollar signs, copying the formula down shifts the reference to B3, B4, B5, and so on. The calculation quietly uses the wrong value and the output looks plausible until you audit it. Always use absolute references for inputs: $B$2, $B$4, $B$5, $B$6.

Adding Extra Payment Scenarios

One thing people find useful is a column for optional additional principal payments. If a borrower wants to see what happens when they pay an extra $200 toward principal every three months, add a column H for extra payments and adjust the principal portion calculation accordingly. The formula in the principal column becomes =PPMT($B$4/12, A2, $B$5, -$B$2)+IF(MOD(A2,3)=0, $H$2, 0), where H2 contains the extra payment amount. This reduces the balance faster, shortens the loan term, and cuts total interest. The schedule recalculates dynamically because everything is formula-driven. A standard amortisation table Excel setup assumes a fixed payment amount and a fixed rate throughout the entire term. It does not accommodate graduated payment mortgages, balloon payments with complex fee structures, loans with embedded prepayment penalties that change over time, or loans where the lender compounds interest daily but payments are monthly. For those cases, the spreadsheet approach becomes either extremely complex or simply wrong. Daily compounding requires a different calculation entirely, usually based on the actual number of days in each period multiplied by the daily rate. If your loan product involves daily compounding, you should not use a standard amortisation table. Instead, build a day-count-based model or use a dedicated loan servicing system. The error margin from using a simplified monthly model on a daily-compound loan can be several hundred dollars over a thirty-year term. Once your table is built, format the currency columns with a consistent number format. Two decimal places, no thousand separators unless the amounts are large enough to warrant them. Round the interest and principal columns to two decimals using ROUND formulas if you need exact penny matching, otherwise let Excel handle the display formatting. Check the final row: the remaining balance should be zero or within a cent of zero. If it is not, you likely have a rounding drift issue. The fix is usually to round each period's interest and principal separately rather than relying on Excel's default precision, then force the last payment to absorb any residual difference.

The whole process, from blank sheet to working schedule, takes roughly ten to fifteen minutes if you know the formulas. The first time you build one, expect twenty to thirty minutes because you will catch yourself double-checking the IPMT syntax and verifying the sign convention. After that, you can generate a schedule in under five minutes. The template itself, once set up, is reusable for any loan by simply changing the input values at the top.