Ever wondered how your flexible mortgage might look if you made a regular overpayment, or if you paid off a lump sum or two at some point during the mortgage term? This calculator will help you work out exactly what it would mean for your mortgage term.

I’ve created this calculator as an Excel workbook rather than a web page, as it is useful to be able to enter overpayments at any point during the term of the mortgage. It’s also useful to be able to change the interest rate at any point, to visualise how a change in interest rate might affect your payments in a few years time.

There are two worksheets in this workbook:

• Regular Overpayment – which shows the effect of making a regular overpayment on top of the minimum payment per month
• Regular Payment – which shows the effect of making a regular payment every month, regardless of what the minimum payment is

You would use the Regular Overpayment worksheet if you wanted to see the effect of paying (say) £100 more than the minimum amount per month.

You would use the Regular Payment worksheet if you wanted to see the effect of paying (say) £1,000 every month, regardless of your minimum payment.

Both worksheets also have the option to add in lump sum overpayments at any point.

You can change the regular payments and the interest rate at any point or points during the term, to see what effect the change would have.

This workbook can be used to try out a potential new mortgage, or to see how overpayments would affect your existing mortgage. You can change the starting balance, the interest rate, the remaining term (to the nearest month), the starting month, and the recalculation threshold.

Check out the comments on the column headers for more information about what is displayed in each column.

This workbook has been tested in Excel 2003 on Windows XP, and also in Excel 2004 and 2008 on Mac OS X.

The workbook uses pounds sterling as its currency, but it goes without saying that the mathematics applies to other currencies too.

I am indebted to the Mathematics of Money: Compound Interest Analysis with Applications page created by Dr. Hossein Arsham of the University of Baltimore, which provided both the inspiration and the maths for my calculator. His Compound Interest Schedule calculator was particularly useful.