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.
You can also give some irregular payments as input.
- 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))
- The formula is already in the template. After giving your loan details, you will get the output automatically.
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.
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.
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!
can’t open file in Office 365. Error message states; “This file is corrupted.”
Hi Denny,
I am using Microsoft 365 (Home) and it is working on my side perfectly. Please try again.
Thanks.
How can you adjust for missed payments?
This is not completely automatic, Lea!
How do you revise for missed payments?
This is not completely automatic, Lea!
Kawser, how do I contact you directly?
Hello, Kim! You can contact him through this email address:
[email protected]
Hi – I love what you built here…. thank you. Ive been trying to use your calculator and can never get the math quite right and I realized why. Its because in your formula, I am literally paying every 2 weeks, however Im with Chase, and they just hold my first payment, and dont apply it till they receive my second payment. Ive tried hacking what you build but I break it every time. Any chance you can show me a version where the bank only receives my money every 4 weeks, not every 2. Thanks in advance. -Jon
Hey there, Thanks for sharing your problem here. Try the following excel worksheet in the article
https://www.exceldemy.com/mortgage-calculator-with-extra-payments-and-lump-sum-excel/
and try to solve the issue with this excel template. and make sure to follow the instructions mentioned in the article.
If you have any queries regarding excel, feel free to share. Also, you can post your Excel-related problems in the ExcelDemy Forum (https://exceldemy.com/forum/) with images or Excel workbooks.
Regards
Exceldemy Team