Biweekly mortgage calculator with extra payments [Free Excel Template]

biweekly mortgage calculator with extra payments excel

Biweekly mortgage calculator with extra payments [Free Excel Template]

“No Macro used. No installation is necessary. Just download and start using.” – Kawser Ahmed (Template Developer)

Created using Excel 2016 version

Download Template

License: Personal Use (Not for resale or distribution)


Let us know (in the comment box) your criteria or the problems that you’re facing while using this template. We shall update the template.

Do you know just by paying your monthly mortgage bi-weekly, you can save a lot of time and money? When we take and pay a long-term loan (like home mortgages), our most of the payments go to pay the interest of the loan. If we can pay a good earlier (at the initial stage of the loan) amount of the principal, we will save a good chunk of money and time.

Bi-weekly vs. Monthly

Bi-weekly is a period of 14 days. If you pay the loan with the bi-weekly schedule, your total number of payments in a year will be 26 (26 x 14 days = 364 days). If you pay with the monthly schedule, you will pay 12 times a year.

So, regular bi-weekly is 26 payments per year. But if you plan to pay twice a month, your total payments will be 24.

How Does Our Bi-weekly Mortgage Calculator Work?

We have made our Bi-weekly mortgage calculator with 26 payments per year. So, after every 14 days, you will make one payment.

This Excel template has actually two templates. In the first template (on the left side of the worksheet), we have calculated the genuine bi-weekly payment (Equivalent). For other template, we have calculated the bi-weekly payment (Accelerated) from the monthly payment. We divided the monthly payment by 2 to get the bi-weekly accelerated payment.

How to Use Our Biweekly Mortgage Calculator Excel Template?

1) Loan Details

The first step is to provide your loan details. Input these values (in the blue background cells):

Input values in the Excel template

  • Original Loan Terms (Years): This is the original tenure of your loan. For home mortgages, it is normally 20-30 years of the period.
  • Remaining Years: If you have already made some payments and now you want to start your Extra Payments, input the Remaining Years of your loan. The year you will input, after that year your Extra Payment will be counted. Suppose, you have made already 2 years of payments of your 30 years mortgage. Now you want to start your Extra Payments to pay off your loan faster. So, input 28 years in this field. Only Value > 1 is accepted in this field.
  • Original Loan Amount: Input the original amount of your loan.
  • Annual Percentage Rate (APR): This is the Nominal Interest Rate.
  • Loan Date (mm/dd/yy): Input the date from when your interest is calculated.
  • Payment Type: There are two payment types: End of the Period and Beginning of the Period. Choose one that suits your bank. Generally, for Mortgage Loans, payments are normally done at the End of the Period.
  • Interest Compounding Frequency: Generally, if you make your payments Monthly, interest is compounded Monthly. For Bi-weekly payments, interest is also compounded Bi-weekly. But this is a specialized calculator. This calculator is made for them who are interested to make their monthly payment in two equal parts twice a month (after every 14 days). So, I have only allowed Interest Compounding Frequencies: Monthly, Bi-monthly, Quarterly, Semi-annually, Annually.
  • Extra (Recurring) Amount You Plan to Add: This is the extra recurring amount you want to add every bi-weekly. If your Remaining Years is 28 years and you add some value in this field (Extra Amount), the rest of the periods (bi-weekly), you will keep paying this extra amount. Just check out the total interest saving just adding 20-25$ every bi-weekly. Interest and time savings are mind-blowing.
  • Extra Payment (Irregular): You will find this column in our first Excel template (on the left). When you will pay some extra money rather than your regular and Extra Payment, you will input that payment in this column.

So, these are our Input Values. Only the blue cells and area are for your Inputs. Not other cells. They are locked. Unlock the worksheet: Review tab > Protect group of commands > Unprotect Sheet.

2) Template 1 (Equivalent Bi-weekly Payment)

This template uses the genuine Bi-weekly payment for calculating Due Bi-weekly payment. This is the formula:

=PMT(apr/26, term*26,-loan,,1)

Here,

  • apr: APR is the annual percentage rate
  • term: Original Loan Terms in Years
  • loan: Original loan amount

So, we get 552.69$ for a bi-weekly payment. And this is the output we get with our old loan details:

biweekly mortgage calculator with extra payments

3) Template 2 (Accelerated Bi-weekly Payment)

For this template, we divided the monthly payment by 2 and paid this amount after every 14 days as bi-weekly payments. This is the formula:

=(PMT(apr/12, term*12,-loan,,1))/2

In this template, you will not input any Irregular Extra Payment. If you have added any Irregular Extra Payments in the first template, those values will be also used by this template.

biweekly mortgage calculator with extra payments

Advantages of Making Bi-weekly Payments

The main advantage of making bi-weekly payments is saving a good chunk of money and pay off your mortgage loan faster.

Suppose, you took a loan of amount $250,000 for 30 years with 6% annual percentage rate. If you pay monthly, your monthly scheduled payment will be $1498.88. After 30 years of your regular payment, you will pay a total $289,596.80 as interest.

If you divide your monthly scheduled payment ($1498.88) into two equal parts, your bi-weekly scheduled payment will be $749.44. If you make this payment after every 14 days, you will pay only $226,748.14. You will save $62,848.66. And you will pay off your loan 5 years 6 months and 30 days earlier.  

These extra benefits come with your sacrifice. With the monthly payments, you had to make 12 monthly payments and the amount would be $17,986.56. But with this bi-weekly payment, you have to make 26 payments with every payment $749.44. So, your total paid amount in a year would be $19,485.44.

Extra amount would be: $19,485.44 – $17,986.56 = $1498.88.

Benefits of bi-weekly payments

So, you have to pay one month extra in one year and that provides all the above benefits (interest and time savings). Many people manage this extra payment from their little saving all over the year, tax refunds, and utilizing their performance bonuses. If you could have a raise in your salary, it would be much easier for you to pay off your mortgage loan earlier.

Considerations before paying bi-weekly

If you’re paying your monthly payment automatically, at first, you need to know whether it is cancel-able or changeable. Contact your lender to know all the details of changing your payment from monthly to bi-weekly.

Check out whether there are any penalties for the pre-payment of your loan. Some banks might charge a few hundred bucks to change your payment from Monthly to Bi-weekly. But it worth it. By changing from Monthly to Bi-weekly, you will save thousands of dollars.

Make sure one thing: your bi-weekly or extra payments (if you make) are applied to your account with immediate effect.

Related Excel Templates

Conclusion

Bi-weekly payment option is a good policy if you want to pay off your mortgage loan 4-5 years earlier. But before paying your mortgage loan, pay off all your high-paying credit card loan. Becoming debt-free, saving money for your retirement, making an emergency account: these all are blessings. It will give you full power and freedom in life. If you have any comment on my Biweekly Excel mortgage calculator (with extra payments), let me know in the comment box.

“Do not save what is left after spending, but spend what is left after saving.” – Warren Buffett

Wishing you a debt-free life.

Happy Excelling!


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply