Setting Up a Bi-Weekly Amortization Schedule That Actually Works

Most templates out there are built wrong. I spent three years building mortgage payoff models for investment properties before I stopped using any pre-built template and just wrote my own. The problem with most Bi Weekly Mortgage Payment Amortization Template Excel files is they treat bi-weekly payments as exactly half of the monthly payment. That is not how interest compounds, and it will give you slightly wrong results over a 30-year loan. Maybe a couple thousand dollars off at the end. If you are just looking for a rough estimate that works, fine. If you need it to match what your loan servicer would show you, you need to get the mechanics right.

Bi Weekly Mortgage Payment Amortization Template Excel

Here is how you build one properly from scratch. It takes about 20 minutes and once it is done, you can drop in any loan and have it spit out the schedule. Start with these cells in row 1: Loan Amount, Annual Interest Rate, Loan Term in Years, Closing Date. In row 2, put your actual values. For a $350,000 loan at 6.75% over 30 years starting January 1st 2024, those go in the four cells. In column A starting at row 4, label it "Payment Number". Column B is "Payment Date", column C is "Beginning Balance", column D is "Principal portion", column E is "Interest portion", column F is "Ending Balance", column G is "Cumulative Principal Paid", and column H is "Total Interest Paid". For the payment amount calculation in a separate area, you need the standard monthly payment first. Use PMT with the annual rate divided by 12 and total months. Then divide that by 26 to get the true bi-weekly payment amount, not by 12. This is where everyone messes up. Splitting the monthly payment by 2 gives you a weekly-ish number that is slightly too high because there are 52.14 weeks in a year, not 52. For the date logic, take the closing date and add 14 days for each subsequent payment. Use the EDATE function or just add 14 in a cell and drag. Note that this will occasionally skip a calendar week or land on the same date twice in a Gregorian calendar month. That is normal and expected. The interest calculation per period is the beginning balance multiplied by the annual rate divided by 365 times the actual days between payments. Some lenders use 360-day years. Check your note. Most residential mortgages use actual/365. If you use the wrong day-count convention, your schedule will drift from what the servicer reports and you will waste time trying to reconcile it. For the principal portion, subtract the interest from your bi-weekly payment. For the ending balance, subtract the principal portion from the beginning balance. Drag these formulas down. You will need about 156 rows to cover the full term since 30 years of bi-weekly payments is roughly 390 payment periods, not 360. The cumulative columns are straightforward running totals. The key cell to watch is the one where the ending balance first goes below zero or reaches your target payoff amount. For a standard 30-year loan with bi-weekly payments, you will typically pay it off in about 22 to 23 years depending on the rate and exact payment amount. I ran into a specific issue last year with a client who had a assumable VA loan at 3.5% with a prepayment penalty structure. The standard template accelerated payoff so aggressively that it triggered the penalty clause in quarter 18 because the extra principal payments crossed a threshold in the loan documents. I had to add a guard column that checked each period's cumulative principal against the penalty trigger amount and flagged any payment that would cross it. That took me about 45 minutes to build into the template. After that, every similar case took two minutes. The most useful thing this template gives you is visibility into the actual interest savings. On a $350,000 loan at 6.75%, switching from monthly to bi-weekly saves roughly $42,000 to $48,000 in total interest over the life of the loan and cuts about 7 years off the term. The exact number depends on whether your lender applies payments immediately or holds them. Some servicers process bi-weekly payments as if they were monthly, which completely defeats the purpose. Call your lender and ask how they apply bi-weekly payments. If they pool them and disburse monthly, you are not getting the benefit and should switch to making one extra full payment per year manually instead. One counter-intuitive detail most people miss: the bi-weekly method is mathematically identical to making 13 monthly payments per year, but the timing difference matters. Because you are paying sooner rather than later, each payment reduces principal faster, which compounds. The difference between true bi-weekly and the 13-payments-per-year shortcut is usually under $500 over 30 years. The shortcut is easier to set up if you just want a ballpark figure. A limitation you should know: this template assumes a fixed rate. If your loan has an adjustable rate, you need to rebuild the schedule every time the rate changes. I usually add a separate section for rate adjustment dates where you input the new rate and the template recalculates from that point forward. Without that, the schedule becomes useless after the first adjustment period. Another scenario where this fails completely is interest-only loans. The payment structure is fundamentally different and the amortization logic does not apply until the IO period ends. I keep a separate template for those that handles the switch from IO to fully amortizing payments at the adjustment date. If you want to download something usable, I do not host files. But the structure above is simple enough that you can build it in about 20 minutes and it will be more accurate than most free templates you find online. The main thing to verify after building it is that the final payment is close to zero. If it is more than $50, your day-count convention or payment calculation is slightly off and you should adjust the divisor or the interest calculation method to match your loan documents.