Biweekly Mortgage Calculator with Extra Payments in Excel

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), 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. In this article, you will get a biweekly mortgage calculator with extra payments in Excel. The free template is very useful.


Download Practice Workbook

Download the free template for a biweekly mortgage calculator with extra payments in Excel from here.


Biweekly vs. Monthly Mortgage

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.


2 Steps to Calculate Biweekly Mortgage Calculator with Extra Payments in Excel

We have made our Bi-weekly mortgage calculator with 26 payments per year. So, 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 templates, 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.


Step 1: Input Loan Details

The first step is to input the loan details.

Loan details for biweekly mortgage calculator with extra payments excel

You can also give some irregular payments as input.

biweekly mortgage calculator with extra payments excel

  • 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 already made 2 years of payments on 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 those 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, and 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 are 28 years and you add some value in this field (Extra Amount), for 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.

Step 2: Calculate Biweekly Payment with Extra Payments

The next step is the calculation of the bi-weekly payments. The formula in this template is

=-IF(payment_type=1,PMT(rate,nper,loan,,1),PMT(rate,nper,loan,,0))

biweekly mortgage calculator with extra payments excel

  • The formula is already in the template. After giving your loan details, you will get the output automatically.

biweekly mortgage calculator with extra payments excel

The Bi-Weekly payment is $552.69.


Calculate Accelerated Biweekly Payment

You can also calculate the accelerated Bi-Weekly Payment. For this template, you need to divide the monthly payment by 2 and paid this amount after every 14 days as bi-weekly payments. This is the formula in M20.

=(PMT((1+apr/VLOOKUP(interest_compounded,periodic_table,3,0))^(VLOOKUP(interest_compounded,periodic_table,3,0)/VLOOKUP("Monthly",periodic_table,3,0))-1,term*12,-loan,0,payment_type))/2

Excel will automatically calculate the output.

Calculate Bi-weekly mortgage payment with extra payment

The accelerated bi-weekly payment is $599.55.


Advantages of Making Biweekly 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 the amount of $250,000 for 30 years with a 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 of $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 of $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.

Advantage of Bi-weekly mortgage

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 savings 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 Biweekly Mortgage

If you’re paying your monthly payment automatically, first, you need to know whether it is cancelable 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 was worth it. By changing from Monthly to Bi-weekly, you will save thousands of dollars.


Conclusion

A 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 loans. 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 comments 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!


Related Excel Templates

Kawser

Kawser

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 them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

6 Comments
  1. can’t open file in Office 365. Error message states; “This file is corrupted.”

  2. How can you adjust for missed payments?

  3. How do you revise for missed payments?

Leave a reply

ExcelDemy
Logo