Overview
How much interests I can possibly save if I use an mortgage offset account and top up the offset account on a regular basis, i.e. monthly, fortnightly, or weekly? How long does it take to repay my mortgage by using mortgage offset? If you have asked yourself these questions, you can use this FREE Mortgage Offset Calculator to calculate the time and interests that you might be able to save by using mortgage offset account and make regular deposits into the offset account.
This Mortgage Offset Calculator is far better than those currently available on the market. With this offset calculator, you can:
(1) choose home loan repayment frequency by weekly, fortnightly, or monthly;
(2) vary mortgage terms from 1 to 30 years;
(3) specify a start balance of the offset account;
(4) specify the offset account top-up frequency by weekly, fortnightly, or monthly as well as change top-up amount. The top-up frequency can be different from the home loan repayment frequency;
(5) calculate daily home loan repayment, interest, and principal;
(6) input occasional withdrawal from the offset account on a daily basis;
(7) input occasional deposit to the offset account on a daily basis.
Here is a screenshot that will give you a better idea that what you need to do and what this FREE Mortgage Offset Calculator can tell you.
Things You Need to Know
This calculator is built in Microsoft Excel worksheet. You need to have Microsoft Excel® 2013 & Above for Microsoft Windows® OR Microsoft Excel 2016 & Above for Mac® to use it.
All the calculators (paid and free ones) on this website are password protected. We don't provide unprotected versions of the PAID calculators due to copyright reasons. If you purchase the paid calculators because you want to get the unprotected version please don't make the purchase as we are not going to provide unprotected copies. By purchasing the paid calculators you agree that no unprotected copies of the PAID calculators will be provided to you. If you don't agree please do not purchase. If you need the unprotected version of any FREE calculator a fee will apply. The advantage of the unprotected version is that you can freely edit the tool without any limit although we still own the copyright of the unprotected calculator. Please note you cannot redistribute our calculators without a written approval from us even for the ones with your modification or customization. In addition we are not going to provide any support on unprotected calculators with any modification or customization.
How Mortgage Offset Works
First of all you need to have an offset account linked to your home loan account. An offset account is a transaction account that can be linked to your home or investment loan. The credit balance of your transaction account is offset daily against your outstanding loan balance, reducing the interest payable on that loan. With a 100% offset account the balance of the offset account is taken away from the principal remaining on the loan for interest calculation.
Let's have a look at an example. A customer with a $300,000 home loan over 25 years had an offset account linked to the home loan for the entire loan term with a constant balance of $50,000 in it, they would only pay interest on $250,000 for the entire loan term.
Remember one important thing that the offset account has to be linked to your home loan account! Otherwise the money in the offset account will NOT reduce your homeloan principal and you will NOT get any interested savings. Not linking the offst account to your home loan account is one of the most common mistakes that the banks will make when they set up your loan.
How Much Mortgage Offset Can Save You
So exactly how much you can save with a mortgage offset account on your home loan? It depends on several things:
Loan Amount: how much you borrow from the bank. Generally speaking the less you borrow the more you can save with the same amount of money in your offset account.
Loan Term: how many years the loan will last. Generally the longer the loan term is the more you can save with the same amount of money in your offset account.
Loan Interest: the interest rate of your home loan. Normally the higher the interest is the more you can save.
Loan Repayment Frequency: how often you repay your loan, i.e. monthly, fortnightly, or weekly. Normaly the less frequent you pay the more you can save.
Offset Account Balance: the money in your home loan account. Without any doubt the more you have in the offset account the more you can save.
Remember: You ONLY get interest savings as you still need to pay the full home loan amount back!
How To Calculate Offset Interest Savings
In order to work our the interst savings from an offset account, you need to compare the interest paid with and without the offset account. Let's see an example.
Loan Term: 25 years;
Loan Interest: 6% per annum;
Loan Repayment Frequency: 12 per year which means you pay 12 installments per year on a monthly basis;
Offset Account Balance: $50,000 from the start of the loan.
The Calculations
At the end the first month, you will need to pay the scheduled Monthly Repayment of $1,932.90. This can be calculated by using Excel PMT function:
Monthly Repayment = PMT(Loan Interest/12, Loan Term*12,-Loan Amount) = PMT(6%/12, 25*12, -300,000) = $1,932.90
Out of this $1,932.90 repayment, $432.90 is the principal payment and this can be calculated by using Excel PPMT function:
PPMT(Loan Interest/12,1,Loan Term*12,-Loan Amount) = PPMT(6%/12,1,25*12,-300,000) = $432.90
However, since there is an offset account, the interest you pay will be reduced by a amount calculated using this formula:
Offset Account Balance*Loan Interest/12 = 50,000*6%/12 = $250. This $250 is your first month offset interest savings!
In other words, this $250 will become principal repayment as you don't need to pay this $250 loan interest. Hence at the end of 1st month you actually pay back $682.90 principal instead of the scheduled $432.90.
If we continue this calculation process, we will be able to find out when the loan can be paid off and how much we can save on interests.
Download FREE Mortgage Offset Calculator
