Max Overpayment Spreadsheet

83dons

I would like to put together a spreadsheet showing what the max overpayments should be per month until my mortgage product is paid off that will indicate what the saving in the payment term will be for doing so. My mortgage is with Santander at a fixed rate for 2 years of 1.99% interest. Assuming this product continues for the duration of the payment (I know it will fluctuate in future likely) their rules for these mortgages are you can overpay 10% of the debt owed each year. I will be paying this monthly. How would I go about setting up a spreadsheet to roughly track the projected overpayments and term? I can work out the max overpayments part but not sure how to represent the monthly payments and how much interest the spreadsheet should account for in a given time period. Anyone done the same willing to share or can point me in the right direction? I have my various figures here.

kwame41

Not a spreadsheet but would give you the information you need.


https://www.moneysavingexpert.com/mortgages/mortgage-overpayment-calculator

83dons

Hi that is interesting but not as accurate as I would like. The 10% annual limit means the amount I can overpay will reduce each year meaning the term will be longer than projected here. I know interest rate fixed term deals will change but will base it on the current one for now. I would prefer a spreadsheet solution if any exist.

getmore4less

Write your own the functions are builtin to do the hard bits.

OK read your post that was what you were asking.

Start with reading up on what the functions you need do.

You can then do a year at a time.

nicholbb

You need to look at your mortgage terms. Some will say 10% at beginning of the year, some at time of paying off and other rules.


If 10% at beginning of year that is easy. (Mortgage value at start of year /10 ) / 12

Unicorn_cottage

getmore4less - I asked a similar question a while ago - not all of us are excel wizards. I think the page on mse about overpayments is a little bit simplistic and needs more info on factors like my mortage that allows you to overpay by 10% but they ALSO reduces your monthly payment at the same time.

83dons

Unicorn_cottage wrote: »

getmore4less - I asked a similar question a while ago - not all of us are excel wizards. I think the page on mse about overpayments is a little bit simplistic and needs more info on factors like my mortage that allows you to overpay by 10% but they ALSO reduces your monthly payment at the same time.

Precisely a very tricky excel calculation to me. The max overpayment will reduce each year and need to take into account interest too.

getmore4less

OK I get that not everyone is a wizard but most can do the simple calculations once you know which functions do the hard bits.

their rules for these mortgages are you can overpay 10% of the debt owed each year. I will be paying this monthly. How would I go about setting up a spreadsheet to roughly track the projected overpayments and term? I can work out the max overpayments part but not sure how to represent the monthly payments

This is a very simple series of calculation.

for the case when the 10% is based on amount at start of year

A.What's the normal payment
B. add 10%/12 of the outstanding amount
C. what's left after 1 year with this new payment

repeat for each year

only 3 steps each year

Most calculations can be described in simple terms like this if you break it down into steps which is great for spreadsheets.

From open office helps

---

Mortgages are actually very simple beasts the hardest bit is getting your head around how amortization works but lucky there are functions that do most of the calculations.

There are a only 4 variable to a mortgage and only 3 needed at any one time to work out the other

1. amount borrowed
2. interest rate
3. term
4. payment

The starting point is usually

got 1,2,3 what's 4?


A.What's the normal payment

spreadsheet speak with the payment function

PMT(Rate; NumPeriods; PV; FV; Type)

Num periods will be months.
PV is the starting mortgage(or the amount at the start of a year)
FV is the end amount(£0)
look up Type in the help(makes a small difference).

£100k 20y at 2%

PMT(2%/12; 20*12; 100000; 0; 0or1)

B. add 10%/12
Now you have the payment and the starting mortgage value you can add 10%/12 to the normal payment

C. what's left after 1 year with the new payment
Now we want to find the amount left after 12 of these new payments.

There is a function to do this called future value this returns how much is left after a number of payments(1 year is 12)

FV(Rate; NumPeriods; Payment; PV; Type)

That's year 1 done with just 2 functions.
Repeat for each year feeding the end of year amount owing into the next year.

Because these functions work for savings and debt sometimes you need to change the sign on the answers.
PMT will return a negative value(a payment reducing debt)

Another useful function is the one that for a given payment what's the term to see how much an overpayment takes off the term

NPER(Rate; Payment; PV; FV; Type)


These functions are powerful tools

eg you are trying to hit a LTV amount at the end of a fix what payment is needed, 2% 100k need to get to £80k in 2 years

PMT(2%/12; 24; 100000; 80000; 0or1)

as I said above mortgages are very simple beasts once you get the basics.

For a given month you only have 3 new numbers on top of the main 4 above.

1. amount borrowed
2. interest rate
3. term
4. payment
5. one months interest
6. one months capital
7. new balance.

(from the start and end amount you can work out the interest and capital)

There are other function but those three get you started and can do most things if you break the problem down into steps.

83dons

This is much more useful thanks very much. I have never heard of any of these functions but useful to know and I will have a go at this. I may post my final workings to see what folk make of them i am not that bothered about folk knowing the ins and outs of the amount due. Is there anything I need to ask the mortgage lender regarding the way they calculate interest or how they define the year period for the 10%? The company are also going to send me an illustration of the current fixed term period (2 years) and what my max payment can be over that period so I will be able to check my working against their official ones to see if they match up. That should make the following years easier to calculate. I still find it a bit confusing getting my head round what happens after the current 2 year fixed term deal finishes up - I guess I keep applying for similar deals until it is paid off and the rate will fluctuate a bit I am sure over the years. I will be avoiding their base rate though as it has high interest. By doing this and by overpaying to bring forward the term this should save a considerable amount of money in terms of interest not paid.

getmore4less

Is there anything I need to ask the mortgage lender regarding the way they calculate interest or how they define the year period for the 10%?

the issue with amortization is that it is based on same sized months and the same pay date.
BUt we have differnt sized months with weekends and holidays that move the date payments happen

Not worth modelling that as the differences are a few £ and most lenders sort it out by recalculating every now and then.

payment at start or end of the calculation period also only makes a small difference.

Interest in most cases rate/12 is good enough as that works for daily interest monthly compounding.

There may be some small difference if you lender does it differently.

Once you have an illustration you should be able to work out how they have calculated the interest.

Stick with the simple 2 functions till you need to get more accurate.

the last thing you need to know is the details of the ERC free overpayment.