Mortgage Interest Calculation help

WeegyAVLover

Distinguished Member
Hi All,

Trying to put together a spready for my mortgage so I can calculate out a few things for overpaying.
Now I am sure I could go onto the internet and find something that would do this for me but just fancied trying to do this myself.

However I am looking at my latest statement and there is a monthly interest charge on the account and I cannot seem to replicate this amount in my spreadsheet.

I have a mortgate a fixed rate of 1.99% and and my mortgage has an offset.

My current outstanding balance on my mortgage £21,062.23 (this is the mortgage amount - the offset).

I was charged £35.59 in interest, however I cannot produce this figure.

My thoughts were: outstanding balance x fixed rate / 12. (12 being the months in the year to get the monthly rate)

however this gives me an interest charge of £34.93.

not sure if I am missing something or if interest charges calculations are much more difficult than what I think.

PS - yes my Saturday nights are a total riot & just flyby. 😃
 

imightbewrong

Distinguished Member
Hi All,

Trying to put together a spready for my mortgage so I can calculate out a few things for overpaying.
Now I am sure I could go onto the internet and find something that would do this for me but just fancied trying to do this myself.

However I am looking at my latest statement and there is a monthly interest charge on the account and I cannot seem to replicate this amount in my spreadsheet.

I have a mortgate a fixed rate of 1.99% and and my mortgage has an offset.

My current outstanding balance on my mortgage £21,062.23 (this is the mortgage amount - the offset).

I was charged £35.59 in interest, however I cannot produce this figure.

My thoughts were: outstanding balance x fixed rate / 12. (12 being the months in the year to get the monthly rate)

however this gives me an interest charge of £34.93.

not sure if I am missing something or if interest charges calculations are much more difficult than what I think.

PS - yes my Saturday nights are a total riot & just flyby. 😃

Your calculation is right there will be some subtleties at work. Probably the calculation is based on the number of interest-accruing days in the month - i.e. weekdays and not including bank holidays.
 

ChuckMountain

Distinguished Member
Your calculation is right there will be some subtleties at work. Probably the calculation is based on the number of interest-accruing days in the month - i.e. weekdays and not including bank holidays.

No it is based on the calendar days and interest accrues on a daily basis with most mortgages these days. So will vary anyway each month as well as normally get lower anyway because you pay something off each month.
 

imightbewrong

Distinguished Member
that worked

Yep so based on the number of days in the month, but a simple model which treats each day equally.

Next month some will have been paid off so less interest will accrue each day and few more pence will go towards the sweet sweet capital reduction
 
Last edited:

DIYlady

Distinguished Member
hello WeegyAVLover

I have a little question
I've been trying to recreate your calculations and fail miserably every time.
what is your home value so your monthly interest is only 35 quid?
£36 - he lives in a tent :)
 

imightbewrong

Distinguished Member
hello WeegyAVLover

I have a little question
I've been trying to recreate your calculations and fail miserably every time.
what is your home value so your monthly interest is only 35 quid?

All that matters for the interest calc is the outstanding, non-offset balance, which is given above. To recreate the figure divide the annual interest by 365, multiply by the number of days in the given month, and take that percentage of the debt.
 

DPinBucks

Distinguished Member
All that matters for the interest calc is the outstanding, non-offset balance, which is given above. To recreate the figure divide the annual interest by 365, multiply by the number of days in the given month, and take that percentage of the debt.
In fact, I think they use 365.25, not 365. Otherwise, you'd be overcharged a day's interest every 4 years. Try using that in the calculations to see if it gets closer to the actual.
 

mjn

Distinguished Member
hello WeegyAVLover

I have a little question
I've been trying to recreate your calculations and fail miserably every time.
what is your home value so your monthly interest is only 35 quid?

As others have said, it is the outstanding balance. My interest is only 1.01% :D so whilst i owe about £65k my interest payment is about £60 / month. I spend more than that on beer every month :laugh:
 

imightbewrong

Distinguished Member
In fact, I think they use 365.25, not 365. Otherwise, you'd be overcharged a day's interest every 4 years. Try using that in the calculations to see if it gets closer to the actual.

Many loans assume a basis of 365 or even 360 days per year. Probably for extreme accuracy dividing by the actual days in the year would be right - this is a leap year currently.
 

The latest video from AVForums

AVForums Movies Podcast: Which is the best decade for horror movies?
Subscribe to our YouTube channel

Full fat HDMI teeshirts

Support AVForums with Patreon

Top Bottom