Calculator for fixed mortgage overpayment plus regular payment

Question

In my personal budget, I allocate a fixed amount £x per month to mortgage repayments. This amount includes a sum which is going towards overpaying the mortgage so that I can get the thing over with as soon as possible. Each of these overpayments is above the threshold for reducing the subsequent monthly regular payment amount, so every month a larger proportion of £x is being allocated to overpaying and a smaller to the regular payment.

All mortgage overpayment calculators I've found assume you are overpaying a fixed £ or % amount each period and don't let you fix the total overpayment + regular payment amount, so it's hard for me to see how this strategy is going to play out. I'm interested in seeing how the payments are going to fall month on month, and how long it's going to take me in total to pay the thing off.

I played around with creating a spreadsheet using the IPMT family of functions but the results I came up with were always a few % off the lender's calculations even over one month, so over the remaining 23 years the figures wouldn't be very accurate.

If such a calculator exists I'd be more than happy to use it, or to receive advice on how to make a spreadsheet which has a good level of accuracy.

Answer 1

Happy to share my personal spreadsheet for this purpose. Go here:

Mortgage overpayment comparison sheet (.xlsx) on Scribd - download it and modify freely in Excel.

This assumes:

• interest is calculated monthly
• payment is applied at the end of that month, pays off interest
• your lender doesn't recalculate your monthly payment every time. Rather, they keep requesting the same amount, but apply the (increasing proportion of) overpayment to the capital until it's paid off – which I gather from your question is what you want to do.

If your lender calculates interest daily or on a different frequency than monthly, then yes this will be slightly out from their figures. But I doubt you'll need it to be too precise. The sheet will give a ballpark date for paying off the mortgage, and also show you roughly what you'll owe on a given future date so you can plan your remortgage / house move etc. if that's part of your plans.

Answer 2

You didn't say how you setup your test spreadsheet, but since IPMT is defined as "calculates the interest payment, during a specific period of a loan or investment that is paid in constant periodic payments, with a constant interest rate," (emphasis added) I think you could be making mistakes in how you use it. Are you attempting to use it for anything other than the first period?

Also, since you say the overpayments you are making cause the lender to recalculate the subsequent monthly regular payment, you'll need to understand exactly how and when they're doing this to be able to match it in your own forecasting. Hopefully, they're simply re-amortizing the outstanding principle for the remaining term, and doing this promptly upon receiving your payment. But it could be there's a lag time and your next payment's division into interest and principle is based on a previous month's calculation.

I do think setting up your own spreadsheet is the right way to go.

I think you'll need a row or column for each month individually, being very careful to get the outstanding principal balance right at the start of each month, to understand exactly how your lender is calculating interest, to understand exactly how your lender is recalculating payments amounts, and to match these through appropriate use of PMT, IPMT, PPMT, etc functions. You'd then create enough rows or columns to carry this forward until you hit loan payoff. (i.e. a brute force approach)

Answer 3

Further to the previous answers, there's nothing sacred about the lender's ideas about what the regular payment is for any particular month, and what is the "extra" amount" that you are paying.

You have debt balance, it's growing each month because of interest at some rate, and you're reducing it each month by throwing the same total amount of money into it. You and the lender will agree on all three of these figures.

So just use a mortgage calculator with these three figures as input, and find the total number of payments. Or put in a certain number of payment so find the balance owing at that time.

Or have I completely missed your problem?

Answer 4

It is a little unclear to me exactly what feature you are looking for in a calculator. However, check out this calculator here, it might offer what you are looking for.

It allows you to define an extra payment amount that will be applied each month in addition to your "regular" payment. This "over-payment" reduces the principal so that the amount of interest charged on all future payments is less, creating a scenario where more of your "regular" payment is being applied to principal each month rather that interest and thus will pay off the mortgage faster.

Is this what you mean by:

Each of these overpayments is above the threshold for reducing the subsequent >>monthly regular payment amount, so every month a larger proportion of £x is >>being allocated to overpaying and a smaller to the regular payment.

Also, it allows you to include several large lump sum payments of extra principal so you can see how that affects the mortgage schedule.