Mortgage spreadsheet

Presumably those are the contract rates, not APRs.

If the rate is 5.99% then they'll charge you interest of 5.99%/12 per month on average, not 1.0599^(1/12) - 1. The quoted rate will not account for compounding, the APR will but that'll also include fees etc.

(I really wouldn't bother working out slight differences due to different lengths of months, the difference in the result will be insignificant over a whole number of years).

Did you remember to change the rate after 3 years?

Try adjusting as above and let us know.

In message , David Day writes

The interest doesnt compound daily as GSV's formula implies. Most accrue daily and compound annually but there are a number of variations. Have a look at your last mortgage statement and see if they apply interest monthly or annually.

Andy, I near enough sorted it out. I had remembered to take into account the change after 3 years. Im now only 941 out over the 30 years which is annoying but ok. I worked it out as 5.99 and 6.59 /12

=(1+$h$4)*(F3-(C4+D4))

Where $h$4 is 5.99/12

then after the 36 months it changes to i5 whch is 6.59/12

thanks for your help.

Good point! Interest was added on 6-monthly. So now how do i add it on? thats really confused me. All I have on my statement is the inital costs, fees etc, interest from mar-oct (i took the mortgage out on may 5th) direct debits every month then total and closing balance.

Having actually read the accompanying lett to my statement, I realise im blind. Lol. Interest is added on, on the first of every month although put on the statement as 1 lump sum.

So interest is calculated daily but added on monthly.

Try =(1+$h$4)*F3-(C4+D4)

Otherwise you deducting your payment before working out the interest on the previous month's balance.

In message , David Day writes

Good, you can crack it now!

In message , David Day writes

Use a spreadsheet with a row for each month. Calculate the interest as it accrues on a monthly basis then every six months add in the accrued total interest.

Add all the initial costs and your mortgage advance together, this is the amount which will attract interest.