Mortgage Payment Spreadsheet

Building a Mortgage Payment Spreadsheet That Actually Holds Up

A mortgage payment spreadsheet is fundamentally a schedule that breaks down each monthly payment into principal and interest over the life of the loan. Most people make theirs too simple and then find it unusable when real life happens. The trick is getting the engine right before you add the polish. The core formula most people need is the PMT function. In Excel or Google Sheets it looks like =PMT(rate/12, term*12, -principal). Plug in your annual interest rate divided by twelve for the monthly rate, multiply your loan term in years by twelve for total months, and enter the loan amount as a negative so the result comes out positive. That gives you a single monthly payment number. But that single number is only useful if you can break it apart each month, which is where most beginner spreadsheets fall apart. I need to walk through the amortization engine, because that is what actually matters. Each row represents one payment period. Column A is the payment number. Column B is the payment date, which you can auto-fill using a starting date plus =B2+28. Column C is the beginning balance. Column D is the monthly payment from PMT. Columns E and F are the interest and principal portions. Column G is the remaining balance. The interest calculation for any row is =C2*rate/12. The principal portion is =D2-E2. The next period balance is =C2-F2. Drag those formulas down for however many months you are modeling, usually 360 for a standard 30-year loan.

Here is something people consistently get wrong: the PMT formula assumes equal payments every single month, but your actual mortgage statement may vary due to escrow, late fees, or adjusted rates if you have an ARM. A basic spreadsheet will not catch that discrepancy. I built one for a client in 2019 and forgot to account for monthly escrow variations. The total paid over the life of the loan showed as $274,000 when the actual bank statements totaled $291,000. The difference was entirely property taxes and insurance that came due mid-year and got tacked onto the payment. Lesson learned: build a separate section for escrow and label it clearly so you are not confusing principal-only math with what the bank actually charges. Another detail beginners miss is how prepayments interact with the schedule. If you throw an extra $500 at principal in month 14, the spreadsheet does not automatically adjust unless you wire the logic correctly. You need to add a column for additional principal and change the balance formula so subsequent interest calculations use the reduced amount. Without that, the amortization table lies to you by showing a longer payoff timeline than actually exists. For a more complete version, I include a section where you can input your loan details at the top: loan amount, annual interest rate, loan term in years, start date, and optional extra payment amount. Then the schedule pulls from those cells so you can swap scenarios without rewriting formulas. Use data validation on the rate cell to prevent entering 6 when you mean 0.06, which happens more often than you would think.

Here is a realistic workflow I use: open a blank sheet, set up the inputs section in rows 1 through 5, put the PMT formula in D1 to get the base payment, build the amortization table starting in row 8 with the columns I described, and then add conditional formatting to highlight rows where the principal portion exceeds the interest portion, which typically happens around year sixteen or seventeen on a 30-year loan at current rates. That visual cue is actually useful when you are explaining to someone why early payments feel like they accomplish very little. There are tools that do this automatically, like Bankrate's calculator or NerdWallet's mortgage amortization generator, but those output PDFs or web pages you cannot edit. A spreadsheet stays useful because you can layer in your own assumptions: what-if scenarios for refinancing, comparison between two loan products side by side, or modeling the impact of bi-weekly payments versus monthly. The last one alone saves roughly 3.5 payments per year due to the extra payment embedded in the schedule, which shaves about four years off a 30-year loan at typical rates. The biggest limitation of any DIY spreadsheet is that it assumes perfect regularity. Real mortgages have adjustments, escrow shortages, and occasional payment holidays. A spreadsheet will not predict those. If you need to track actual payments against what the bank charges month to month, you have to maintain a reconciliation column that flags variance. I do this by adding a column that pulls from my bank statement and compares it to the scheduled payment. Anything outside a $20 threshold gets highlighted yellow. It is tedious but necessary if you want the model to reflect reality rather than theory.

If you want a starting file, the structure I described above can be assembled in about twenty minutes. I keep a template at a shared drive link, but honestly the value is in understanding the logic so you can adjust it when something does not behave the way you expect. The spreadsheet is only as good as the assumptions you feed into it, and that is worth remembering before you trust any number it produces.