Mortgage Amortization Schedule In Excel

Setting Up a Mortgage Amortization Schedule Without the Headache

I've built more of these than I care to count. They look simple on the surface, but the moment you deal with early paydowns, variable rates, or leap-year payments, everything falls apart if your framework is weak. Here's how to build one that actually holds up. Start with the inputs at the top of your sheet. You need the principal, the annual interest rate, the loan term in years, and the payment frequency. Most people skip the start date, which is a mistake. Without a precise start date, your payment schedule drifts because Excel's date functions assume a default that rarely matches your actual closing date. Put these five fields in cells A1 through A5 and label them clearly. Everything downstream depends on those numbers being unambiguous. The payment formula itself comes from PMT. It looks like this: =PMT(rate/periods_per_year, total_periods, -principal). The negative sign on the principal is important because PMT returns a negative number by default, representing cash outflow. If you want your payments to display as positive values, you can either negate the principal input or wrap the whole thing in ABS. I negate the principal in the formula. It keeps the logic transparent when someone else opens your file three months later.

Now build the actual schedule. Create columns for Payment Number, Payment Date, Beginning Balance, Payment Amount, Principal Portion, Interest Portion, and Ending Balance. That's seven columns. Anything more and you're adding complexity without value. Anything less and you'll be reconstructing data later when you need it. The payment date column uses EDATE. Take your start date and add the payment number minus one, then divide the months by your payment frequency. For monthly payments it's straightforward: =EDATE(start_date, payment_number-1). For biweekly payments, you need to calculate the interval differently because EDATE works in calendar months, not exact weeks. I use a helper calculation that adds 14 days per payment number instead. It's slightly less elegant but it keeps the dates accurate. Here's where most people mess up the interest calculation. The interest portion of each payment equals the beginning balance times the periodic rate. That periodic rate is the annual rate divided by the number of payments per year. So for a 6.5 percent annual rate with monthly payments, the periodic rate is 0.065 divided by 12, which equals 0.00541667. Multiply that by the beginning balance and you get the interest portion. The principal portion is the total payment minus the interest portion. The ending balance is the beginning balance minus the principal portion. Then the ending balance of one period becomes the beginning balance of the next. This rolls automatically if you reference the cells correctly.

Amortization tables are also available as downloadable templates online, and searching for Mortgage Amortization Schedule In Excel will turn up plenty of pre-built options. The problem with most of them is that they're designed for a single scenario. They assume monthly payments, fixed rates, and no extra principal contributions. When your actual situation diverges even slightly, the template breaks and you spend more time fixing someone else's work than building your own from scratch. I recommend using a template only as a reference for structure, not as a starting point for the actual model. Let me give you a concrete example. Say you have a $350,000 loan at 5.75 percent annual rate over 30 years with monthly payments. The PMT formula gives you a payment of approximately $2,046.14. In the first month, the interest portion is $350,000 times 0.00479167, which equals $1,677.08. The principal portion is $2,046.14 minus $1,677.08, which equals $369.06. Your ending balance after the first payment is $349,630.94. By payment 360, the interest portion has dropped to about $9.77 and the principal portion is $2,036.37. The total interest paid over the life of the loan is roughly $386,611. That's more than the original principal. This is the part that always surprises people who haven't seen the numbers laid out. Now here's a problem I ran into recently that took me two hours to track down. A client had a 15-year loan with biweekly payments, and the amortization schedule showed the loan paying off two months earlier than expected. The payment amount was correct. The interest calculations were correct. The dates were correct. The issue was that the loan had 180 biweekly periods, but because biweekly payments roughly equal 26 half-payments per year, the actual number of payment periods over 15 years is closer to 195 if you account for the extra payment that falls at the end of each year. I had been using 180 as the total periods because 15 times 12 divided by 2 equals 90, then doubled. That was wrong. The correct approach is to calculate the total number of biweekly periods as the number of years times 26, then adjust for the fact that some years have 53 Fridays. I changed the total periods to 225 and rebuilt the schedule. The loan paid off in 14 years and 10 months instead of 15 years and 2 months. That difference matters when you're advising someone on their payoff timeline.

Another pitfall people miss is the handling of leap years. If your loan starts on February 29 in a leap year, Excel's date functions can behave inconsistently depending on your regional settings. I learned this the hard way when a schedule I built for a client in the UK showed incorrect dates because their system defaulted to a different date format. The fix was to use TEXT functions to force the date output into a consistent YYYY-MM-DD format, then reference that formatted cell throughout the schedule instead of the raw date value. It's an ugly workaround but it eliminates the source of error entirely. There are also limitations you need to be honest about. An Excel amortization schedule assumes that payments are made on time, every time, with no missed payments or modifications. If the borrower skips a payment or makes a partial payment, the schedule becomes inaccurate unless you rebuild it from the point of deviation. It also doesn't account for escrow fluctuations, property tax changes, or insurance premium adjustments. If your client needs a schedule that reflects real-world variability, Excel is the wrong tool. You'd be better off using a purpose-built mortgage calculator or a spreadsheet that incorporates conditional formatting and data validation to flag when actual payments deviate from the projected schedule. For most people though, a standard fixed-rate amortization schedule is exactly what they need. Build it once, reuse the structure, and only adjust the input cells when the loan parameters change. That's the whole point of setting it up this way. The formulas do the heavy lifting. You just need to make sure the inputs are clean and the references are correct.