Normally at the end of mortgage when it’s fully repaid the balance is zero. However, in some situations you will need to calculate the outstanding balance of your loan.
This happens usually when:
Off course, there may be other reasons, but the above 4 are the most common ones.
So, the question is:
How do you calculate the outstanding amount which needs to be paid as a lump sum to your bank?
Well, the simplest way is:
Your bank will tell you how much you owe them.
If you don’t want your bank know that you are thinking about refinancing the mortgage then there are other ways.
If you use internet banking and have your mortgage linked to your account, you can see the outstanding balance there. Most banks these days offer this kind of information.
If you have no internet banking or if it doesn’t show the outstanding balance then you can calculate it manually.
The problem here is that the calculation is not that simple. Just summing up all your repayments paid so far, then subtracting them from the initial mortgage amount wouldn’t work.
Repayments are the same amounts in every period. However, they consists of Principal and Interest and the proportion keeps changing with every single repayment.
To solve this mathematical problem you need so called amortization schedule. The good news is, we have created mortgage payoff calculator in Excel that includes such schedule.
Now we are getting to the next option:
As the entry parameters for this calculator you will need:
Once you have all the above information, simply enter it into the calculator. The calculator will then generate an amortization schedule for your mortgage.
Now finding the mortgage payoff lump sum amount is easy – simply check the Loan Balance column corresponding to actual date. It will tell you exactly what the outstanding balance is.
Other handy features of this calculator:
Given the flexibility of this Mortgage calculator it can help you choose the loan that best suits your needs. And even after you took the loan, it will enable you to keep tweaking it on the fly.
Sample calculator: click here to download
To buy the full version: click here to purchase
The sample version is fully functional calculator; however it’s limited to just 30 repayment periods.
If you need more than that (with most mortgages you do unless you pay your mortgage yearly) then the full version is basically unlimited.
It is pre-set for 8000 repayment periods. This should be sufficient for almost any type of mortgage. Even if you paid your mortgage daily, this would give you more than 20 years worth of amortization schedule.
If for any reason this still isn’t enough, you can simply drag and copy the last line of the calculator. You can add as many extra repayment periods as you wish.
If you need more detailed User Guide, you can find it here.
We hope that you will find useful our Excel tool designed to calculate the mortgage payoff lump sum amount. If you have any questions or suggestions, please leave us comment below!