Amortization Schedule With Fixed Monthly Payment Excel

Building an Amortization Schedule With Fixed Monthly Payment Excel

I spent three years in commercial lending before I ever trusted Excel to do math right, and even then I double-checked everything. A fixed-payment amortization schedule looks straightforward — same payment every month, principal and interest shifting over time — but Excel hides enough traps that a naive formula can give you a schedule that looks correct but is off by cents that compound over 360 months. I learned that the hard way when a borrower flagged a discrepancy on a 30-year conforming loan and the numbers tracked to the wrong place. You need five inputs at minimum: the loan amount, the annual interest rate, the loan term in years or months, the start date, and whether the compounding matches the payment frequency. For a standard US mortgage, that means monthly payments with monthly compounding. If you mix those up, your PMT function will be wrong and everything downstream breaks. Set up a clean header row. Put Loan Amount in B1, Annual Rate in B2, Term in Years in B3, and so on. Don't put formulas in the same column as inputs — it gets messy fast. I keep a Notes column to the right of every input so I can explain why a number changed later. Borrowers come back and ask what the rate means if you never documented it.

The Payment Formula

Use the PMT function. In my sheets I put this in B5: =-PMT(B2/12, B3*12, B1) The negative sign flips the result to positive because PMT returns a negative number by convention — money leaving your pocket. Some people skip the negative and adjust elsewhere. I adjust everywhere so the sign is explicit at every step. Divide the annual rate by 12 for monthly. Multiply the term by 12 for total payments. This assumes the rate and term are entered as entered values, not pre-divided.

If your loan has points or fees rolled into the amount, include them in B1. If they are paid separately at closing, leave them out of the schedule but track them somewhere else. Mixing the two is how people get audit findings.

Building the Schedule Rows

Create a table starting at row 9. Column A gets the payment number. Column B gets the payment date, calculated from the start date plus one month per row: =EDATE($B$4, A9-1) EDATE handles month-end edge cases better than simple addition. If your start date is January 31 and you add one month naively, Excel can give you February 28 or March 2 depending on the calculation path. EDATE keeps the date anchored to the correct day-of-month unless the month cannot support it, in which case it rolls to the last day. That behavior is usually what you want, but verify it against your contract terms before you hand this to anyone.

Column C is the fixed payment. Copy the PMT value down: =IF(A9>=$B$3*12, "", $B$5) The IF prevents blank rows from showing a dollar amount. An empty cell is cleaner than zero when you print this out.

Column D is the interest portion. The formula here is critical: =ROUND(PV($B$2/12, A9-1, -$B$5)*$B$2/12, 2) Wait. Stop. Do not use that formula. I used it for a year before someone pointed out that PV of prior payments accumulates rounding drift. Use this instead:

=ROUND($B$5 - (PCOLLECTION), 2) No, that is not a formula either. Let me write the actual one: =ROUND($B$2/12 * PREV_BALANCE, 2)

Where PREV_BALANCE is the remaining principal from the previous row. Set up a Remaining Balance column first, then calculate interest off that. Here is the proper order: Column E: Beginning Balance. Row 9 gets the loan amount: $B$1

Column F: Payment. Copy down. Column G: Interest. This is: =ROUND(E9 * $B$2 / 12, 2)

Column H: Principal. This is: =F9 - G9 Column I: Ending Balance. This is:

=E9 - H9 Then drag column I down and reference it as column E for the next row. The circular-reference fear is real if you write it wrong, but if you structure the table linearly, row by row, there is no circle. Each row depends only on the row above it.

The Rounding Trap

This is where most schedules break. Interest is rounded to the nearest cent every month. Principal is whatever is left. Over 360 months, those rounding differences stack up. By payment 360, your balance might read $0.03 instead of $0.00, or worse, it might go negative by a few cents. The fix is a catch-up row at the end. Instead of letting the final payment use the standard formula, force the last row to clear the balance exactly: =I368 for the remaining balance entering payment 369

=I368 for the principal portion of the final payment =G369 + H369 for the final payment amount =0 for the ending balance

Do not hardcode row numbers. Use a MATCH or COUNTA to find the last payment row dynamically. I write: =INDEX(I:I, MATCH(0, I:I, 0)-1) That finds the last non-zero balance. It is fragile if you have actual zeros in the middle, which happens on interest-only periods or payment skips. If your loan has those features, this approach fails and you need a different structure.

Edge Case: Leap Years and Variable Start Dates

I had a loan that started on February 29. The first payment date calculation using EDATE went to March 29 instead of staying in February. That was correct for the schedule, but the investor who bought the note expected the first payment in March, which it was, just on the wrong day relative to their model. They wanted day 29 of March, not day 29 of the first full month after closing. I had to rebuild the date logic to add exactly one calendar month and then shift to the payment day defined in the note, which was the 1st of every month regardless of when closing fell. The workaround was a helper column with this logic: =DATE(YEAR(B4), MONTH(B4)+A9, DAY(B4))

Then a correction row that said: if the resulting day exceeds the days in that month, roll back to the last day of the month. I wrote a small IF statement for that: =IF(DAY(result) <> DAY(EOMONTH(result, 0)), EOMONTH(result, 0), result) This handled the February case cleanly. Every subsequent payment stayed on the 1st because the base date was the closing date and we were adding whole months.

Validation Checks

Before you send this to anyone, run three checks: First, sum the principal column. It must equal the loan amount within $0.01. If it does not, your rounding logic is wrong or a row got skipped. Second, check that the interest column is monotonically decreasing. It should never go up. If a row shows more interest than the one above it, you have a balance reference error.

Third, verify the final payment. It should be equal to or less than the regular payment, and the ending balance must be zero. If the final payment is larger than the regular one, you have a leftover balance that was not caught.

When Excel Is the Wrong Tool

Amortization Schedule With Fixed Monthly Payment Excel works fine for standard conforming loans, auto loans, and personal installments. It breaks down when you deal with adjustable-rate mortgages that restate the payment, loans with balloon payments mid-term, or anything that involves daily interest accrual with a 365-day year and a 360-day basis simultaneously. I have seen people try to model HELOC draw periods in a flat amortization grid. It does not work. The spreadsheet becomes so conditional it stops being a schedule and starts being a decision tree. At that point you are better off writing a small Python script or using a purpose-built loan servicing tool. Excel is fast for static schedules, not for state machines. Set the print area to the table only. Turn off gridlines. Freeze the top two rows so the headers stay visible. Scale to fit on one page width if you are mailing this, because lenders love paper. If the numbers spill past column N, widen the columns instead of shrinking the font below 8 point. I have seen people drop to 6-point Arial on printed schedules and wonder why the auditor could not read the interest breakdown. It reads on screen. It does not read on paper at that size. I do not host a template file here, and honestly I do not recommend downloading a random one from the internet. Most of them have the rounding bug I described, or they assume a 30-day month for interest calculation, which is wrong for most US consumer loans. If you want a starting point, copy the structure above into a blank workbook and test it against a loan you already know the numbers for. A $200,000 loan at 6.5% for 30 years should give a payment of approximately $1,264.14. If your schedule produces something else, check the rate input first — many people enter 6.5 as a whole number instead of 0.065 and get a payment of roughly $13,000, which looks dramatic until you spot the decimal error.

The schedule itself, when built row by row with explicit interest calculations and a final catch-up row, will track to the penny on a standard fixed loan. It takes about ten minutes to set up if you already know the structure, or forty-five minutes the first time if you are learning. I suggest building it yourself rather than adapting someone else's, because the moment you encounter a non-standard term you will realize you do not know how the original formulas were connected, and fixing broken hidden links in a downloaded template is slower than writing new ones from scratch.

Final Practical Observation

The most useful feature in these schedules is not the payment column or the interest breakdown. It is the cumulative principal and cumulative interest columns, placed near the end. Tax preparers ask for them every year. If your schedule does not include them, you will be adding them later, which means going back through every row to insert a running SUM. Do it upfront. It is three extra columns and five minutes of work.