Mortgage Amortization Spreadsheet

Building a Proper Amortization Schedule in Excel

A mortgage amortization spreadsheet is just a table that breaks down each payment into interest and principal over the life of the loan. Most people build them wrong. They use the PMT function once, then try to back-calculate the interest portion by multiplying the balance by the rate divided by twelve. That works for standard fully amortizing fixed loans, but the second you hit a case with rounding quirks, balloon payments, or a loan that wasn't originated at the exact stated rate, the whole thing falls apart. I learned this the hard way a few years back when a client came to me with a loan that had a 30-year amortization but a 7-year balloon. The spreadsheet I had been using for years produced numbers that were close but not exact to the lender's actual schedule. The discrepancy was small per month — fractions of a cent — but they compounded over 84 payments until we were off by nearly twelve dollars at payoff. The fix was to stop trying to derive everything from the PMT formula and instead use the actual payment amount the lender provided, then calculate each month's interest directly from the remaining balance and the daily rate method they used, which introduced its own headaches with leap years.

Mortgage Amortization Spreadsheet Basics

You need five inputs: the loan amount, the annual interest rate, the total number of payments, the payment amount, and the start date. The payment amount comes from the PMT function in Excel, which looks like this: =PMT(rate/12, nper, -principal). Note the negative sign on the principal — without it, Excel returns a negative payment, which confuses people who haven't seen this before. Once you have the payment, you build columns for payment number, date, payment amount, interest portion, principal portion, and remaining balance. The interest for any given month equals the previous balance multiplied by the monthly rate. The principal portion equals the total payment minus the interest. The new balance is the old balance minus the principal portion. That's it. Repeat for every period. The reason most spreadsheets fail isn't because the math is wrong. It's because people don't account for how lenders actually compute interest. Some use a 360-day year, some use 365, some use actual days between payment dates. If your spreadsheet assumes equal 30-day months but the lender is calculating on a 365-day basis with varying month lengths, your amortization will drift. I've seen schedules that were off by a full payment amount at the end because the builder assumed a 360-day year when the actual loan documents specified 365.

Another thing nobody tells you: rounding. Lenders typically round the monthly payment to the nearest cent, and they may also round the interest portion of each payment to two decimal places before subtracting it from the payment to get the principal. If you don't replicate that rounding behavior in your spreadsheet, you'll get the same drift problem I described above. Put ROUND functions around your interest and balance calculations and you'll match the lender's numbers almost exactly.

Setting Up the Spreadsheet

Start by laying out your inputs in a single area, preferably at the top left. Label each cell clearly. Loan Amount, Annual Rate, Loan Term in Years, Start Date. Then create a table below that with the column headers I mentioned earlier. Payment Number, Payment Date, Total Payment, Interest, Principal, Remaining Balance. For the first row of data, the remaining balance starts as the full loan amount. The payment is the PMT result. Interest is the balance times the monthly rate, rounded. Principal is the payment minus the interest, rounded. The next row's balance is the current balance minus the principal. Drag that formula down for however many payments you need — 360 for a thirty-year loan, 180 for fifteen, whatever the term is. For the payment date column, use =EDATE(start_date, payment_number) if you're doing monthly payments. This handles varying month lengths correctly and won't break when you hit February. If you're adding the days manually, you will eventually create a date that doesn't exist, like January 31st plus one month equaling March 3rd instead of February 3rd.

There's also a shortcut version that doesn't require you to calculate the payment yourself. You can use the IPMT and PPMT functions to pull the interest and principal portions directly for any given period. The formula for the interest portion of payment number N is =IPMT(rate/12, N, nper, -principal). This is cleaner for quick schedules where you don't need the running balance column, but it won't help you if you're modeling partial payments or extra principal reductions mid-term.

When Things Get Complicated

Adjustable-rate mortgages are where standard spreadsheets start showing their limits. The interest rate changes at specific intervals, which means the payment amount changes too. You can model this by breaking the schedule into segments where each segment uses a different rate and recalculates the remaining payment based on the new rate and the remaining balance at that point. It's more work but not difficult if you structure the spreadsheet so the recalculation happens automatically. Extra principal payments are another common requirement. When a borrower throws an extra hundred dollars at their mortgage each month, you can't just add it to the payment column and move on. You need a separate column for the extra amount, then adjust the remaining balance accordingly and recalculate the subsequent interest charges from the new lower balance. The payoff date shifts earlier, and the total interest saved is usually significant — on a typical 30-year loan at five percent, an extra hundred dollars a month saves roughly twenty thousand dollars in interest and shortens the loan by about four years. I once had a situation where a borrower made irregular extra payments throughout the year, not a fixed amount each month. The spreadsheet had to handle variable extra payments without breaking. The solution was to add a column for the optional payment each period and make the principal reduction equal the scheduled principal plus the extra payment plus any partial payment adjustments. Then the balance column just references the prior balance minus the total principal paid that month. Simple, but easy to mess up if you're not careful about the order of operations.

Validation and Common Errors

After building the schedule, always check that the final balance is effectively zero. If it's not, you have a rounding issue or a formula error somewhere. A residual balance of a few cents is normal with rounding; anything over a dollar means you should audit the formulas. Also verify that the sum of all principal portions equals the original loan amount. These two checks catch most mistakes. One persistent error I see is using the annual rate directly instead of dividing by twelve. Excel's PMT function expects a periodic rate, so if you enter 6% without dividing by 12, you're calculating payments for a loan with a 72% annual rate. The resulting payment will be absurdly high and the amortization will pay off in about four or five years instead of thirty. Always double-check that your rate input is divided by the number of payments per year. Another mistake is building the spreadsheet for one loan type and applying it to another without adjusting. An interest-only loan has a completely different structure — the principal portion is zero for the interest-only period, then jumps to a much higher amount when the amortization kicks in. Using a standard amortization template for an interest-only loan will give you wrong numbers from day one. Same goes for graduated payment mortgages, where the payment starts low and increases according to a schedule built into the loan terms.

If you need something more robust than a hand-built spreadsheet, there are dedicated mortgage analysis tools that handle adjustable rates, extra payments, balloon payments, and rounding methods automatically. They cost money but save time if you're modeling a lot of different scenarios. For occasional use, a well-structured Excel template with the rounding functions and EDATE dates built in will serve you fine. Just remember to validate it against an actual loan estimate from a lender before you trust the numbers for anything important.