Calculating mortgage repayments when overpaying

PokerPortal

This is probably a question for the Excel guru's.

I have created an Excel spreadsheet with all 300 monthly terms (25 years) listed, each on a separate row. I use the formula
=pmt(interest rate/12,terms,-total mortgage) to calculate my monthly payment. The terms start at 300 and drop with 1 every month. The total mortgage drops with the repayment part of my monthly repayment.
Ofcourse, I hear you say, your monthly payment will stay the same for the whole period as both the terms and the total mortgage drop.

However, I am also overpaying. So each month the overpayment (£500) comes off the total mortgage as well. I have asked the lender to lower the monthly payment, instead of shortening the length of the mortgage.
Each month my monthly payments drop about £3.

If only the monthly repayment and the overpayment were on the same date (daily calculation) there would be no problem, the above formula would still do the trick (I think). But the repayment is on the 1st of every month, and the overpayment is on the 10th of every month.

And this is were I could use some help. How do I calculate the new monthly payment given the above information?

Thanks

village_life

woooooooooooooooooooooo

very complicated... i had thought i was good with excel

i'd love to see a copy of the spreadsheet though when you've figured it out :-D

tomstickland

It's more of a maths question than an Excel question.

Does mortgage interest work like savings accounts? ie: daily rate is annual rate / 365 and compounding only applies monthly?

xlt_hunter

I thought interest was done on a daily bases for mortages? - I think its going to be a bit of a hard one to crack with the payments going out on the 1st and 10th..

I thought the one I working on was a little hard :eek:

tomstickland

Daily interest rate is annual_rate/365
An average month has 365/12 days in it

Monthly interest charge is

10*daily_rate*total_owed_start_of_month +
(365/12-10)*daily_rate*amount_owed_after_overpayment

Each month you add the total interest accrued to the previous month's total,
then subtract the payment

along those lines anyway

xlt_hunter

Porker

Have a play with this
http://freespace.virgin.net/mike.briktopia/overpay.xls

Something I quickly knocked up - don't know if it will help in anyway?

PokerPortal

Thank you all for your responses. I will have a play with the spreadsheet you made xlt hunter. Our spreadsheet look very much alike. About 16 columns wide and 300 rows long. I have 3 columns where I make entries because they are variable (interest rate, overpayment etc). It is interesting to see what happens to the length of the mortgage when you start altering the overpayment (or the interest rate). But at the moment I am about 35p out every month, which I amend manually when I have received the letter stating my new monthly payment. Somehow amending a spreasheet manually takes away the charm.

The estimated mortgage free date in my signature is based on a £500 overpayment until the mortgage is paid off. I am currently on a fixed rate, but that will end in March 2007. New decisions then.

Thefunkygibbons

Personally, I fix the overpayments and it is only when the annual statement arrives that I realise how much the monthly payment has dropped

Nice annual surprise

originalmiscellany

How's it going Pokerportal? Any update?