We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
Max Overpayment Spreadsheet

83dons
Posts: 86 Forumite
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.
0
Comments
-
Not a spreadsheet but would give you the information you need.
https://www.moneysavingexpert.com/mortgages/mortgage-overpayment-calculator0 -
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.0
-
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.0 -
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 ) / 120 -
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."Everything comes to him who hustles while he waits" Thomas Edison
Following the Martin mantra "Earn more, have less debt, improve credit worthiness" :money:0 -
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.0 -
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.0 -
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.0
-
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.0
This discussion has been closed.
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 350.4K Banking & Borrowing
- 252.9K Reduce Debt & Boost Income
- 453.3K Spending & Discounts
- 243.4K Work, Benefits & Business
- 598K Mortgages, Homes & Bills
- 176.6K Life & Family
- 256.5K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards