perfectly recreating mortgage calculations

planetwave202

Hi All


I am trying to recreate the calculations which my mortgage provider (C&G) uses, in an Excel spreadsheet.
I have read the sticky thread on overpayment calculators and used most of the online/excel calculators (both very useful). I have also created some of my own.
Most of the calculators are intended to give a rough estimate (ie calculated monthly/yearly) of costs.

However hard I try though, I can’t get my spreadsheet to give exactly the same results as on my mortgage statement, even when calculating the interest daily.

The closest I can get it is within 1 to 4 pence per month. I’m almost everyone people would think that this is sufficiently accurate over a £100k, however I can’t see any reason why it shouldn’t be possible to get it absolutely exact, and I won’t be able to sleep until it is!


Either I have made a mistake, or there is a subtlty in the C&Gs calculations which I have not considered.

I have considered that as the difference is so small, it could be due to rounding of decimals, or exactly, how I’m compounding the interest, but I still can’t get it right having tried several variations.

If anyone fancies a challenge, the figures are below:

Date | Interest | Payment | Balance
31/03/2009 | 258.16 | |101526.98
06/04/2009 | | 504.68 |101022.30
30/04/2009 | 207.76 | |101230.06

(sorry i can't get thh formatting to display correctly, 258.16 and 207.76 are interest, and 504.68 is a monthly payment)


So the challenge is – given only the figures in row 1 and 2, calculate the interest and balance shown in row 3! That can’t be difficult!

The interest rate is 2.5%. And C & G say they calculate it like this:

“We calculate the interest on all new mortgages using the balance outstanding each day (daily interest) and add it to the mortgage at the end of each month. This means that whenever your balances changes – for example when you make a payment – interest starts to be charged on the new balance straight away.”

I thought I’d cracked it, by arbitrarily rounding up and down in various places, but when I tried the method on data for another month, it was even further out. If you manage to crack it, just check it works for May too:

Date |Interest | Payment | Balance
30/04/2009 |207.76 | | 101230.06
05/05/2009 | |504.68 | 100725.38
31/05/2009 |214.01 | | 100939.39

(sorry i can't get the formatting to display correctly, 207.76 and 214.01 are interest, and 504.68 is a monthly payment)

I will be most grateful if anyone can help. All the existing caclulators I've tried, are close but don't match these figures exactly. However, I'm happy to be corrected.


Many Thanks


Rob

planetwave202

How about we make a bit more interesting?

£10 reward via paypal for the first person to succesfully solve this?

laurasavon

I think March might be the wrong month to start with, I have a feeling C&G add any Feb payments into March calculations due to the short month

So looking at your payments above in April you paid £6.92 per day interest and May £6.90 per day (it would go down slightly as you owe slightly less)

planetwave202

Many thanks for taking the time to respond.

I agree the different length of months does throw a spanner in the works for trying to calculate the interest monthly. My solution to this is to calculate the interest daily as C&G do. However, I still can't get the maths to add up!

Still a free £10 going to anyone who has the answer!

Cheers

Rob

pencekeeper

Excel will certainly calculate days between dates so it should be possible to allow for variable day months?

ElleStar

I've been trying to do exactly the same thing for months and I still end up a few £1s out. I'd be happy with a few pence difference!!!

lardyabroad

Hello Rob,

Looks like the interest is calculated daily - but - is added at the end of each month; I think the differences your getting is the lack of compound interest on the mortgage interest between payments and month end.

Apologies this is my first post and I can't get the html tags working to post a table (!), but in summary:

01/04/09 - balance £101,526.98 x 2.5% / 365 days = £6.95 daily interest
02/04/09 to 05/04/09 - as above
06/04/09 - balance £101,526.98 less payment of £504.68 = £101,022.30
£101,022.30 x 2.5% / 365 days = £6.92 daily interest
07/04/09 to 30/04/09 - as above

Monthly interest = 5 days x £6.95 + 25 days x £6.92 = £207.75 (1p out *mutter* hehe)

01/05/09 - balance = £101,022.30 + previous months' interest of £207.75 = £101,230.05
£101,230.05 x 2.5% / 365 days = £6.93 daily interest
02/05/09 to 04/05/09 as above
05/05/09 - balance £101,230.05 - £504.68 = £100,725.37
£100,725.37 x 2.5% / 365 days = £6.90 daily interest
06/05/09 to 31/05/09 as above

Monthly interest = 4 days x £6.93 + 27 days x £6.90 = £214.02 (1p out the other way)

01/06/09 balance = £100,725.37 + £214.02 = £100,939.39


I knocked up a quick spreadsheet and I get £207.75 interest in April & £214.01 in May - 1p differences must be rounding errors.

Hope this makes some kinda sense

Richard

planetwave202

Many thanks for this Richard.

I haven't had chance to go through your workings yet, but at a glance, it is looking good!!

I'll be in touch soon!

Rob