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:

1. You want to refinance your mortgage (pay off first and take another)
2. You inherit, earn or otherwise obtain large amount of money and decide to payoff your mortgage (wise choice!)
3. Selling your old home
4. Selling your old home and buying a new one

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:

1. Ask Your Bank

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.

2. Use Your Internet Banking

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:

3. Use our Excel Calculator for Mortgage Payoff Lump Sum Amount

As the entry parameters for this calculator you will need:

1. Original (initial) mortgage amount
2. Interest rate (as % per annum or yearly rate)
3. Repayment frequency (daily, weekly, fortnightly or monthly)
4. Interest compounding frequency (usually same as Repayment frequency – if unsure, ask your bank what it is)
5. Repayment amount
6. If repayments are in advance or in arrears (usually in arrears)
7. Mortgage drawing date (or the first date you took the loan)

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:

• Date and Repayment Amount Adjustments. If you ever missed or paid late any repayment, just overwrite the values in the Repayment amount or Payment date fields. The calculator will automatically adapt and calculate the actual amounts.
• Extra Repayments. Did you ever manage to pay something extra? Great, just enter it into the Calculator and it will take care of.
• Mortgage Modelling Capabilities. The great thing about this calculator is that you can easily perform loan modelling. Adjusting any parameters in the data entry section or actual dates and repayments will instantly translate into updated mortgage schedule. You can see how long it will take to pay it off as well as what is the total interest paid.

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!