Effective interest rates and compounding

Locoblade

Hi All

Im currently writing an Excel spreadsheet to calculate montly payments and interest etc. Im writing it myself because the ones Ive found elsewhere don't allow you to take into account offset savings etc.

Anyway, can anyone confirm that mortgage companies who are charging daily interest compound the daily interest onto the loan for the month amount until a payment is made, and don't just charge interest on the capital amount?

If they do compound the interest back onto the mortgage, it renders a lot of online/spreadsheet mortgage calulators innacurate (including the one on the FSA website) because they generally seem to divide by 12 to calculate monthly payments (eg 6%/12=0.05 to give a monthly rate). The figures the FSA website generate exactly tie in with using the PMT function in Excel using rate/12 in the equation.

If they don't compound and you only ever pay interest on the capital borrowed regardless of how often interest is calculated, then the effective rate stays the same regardless, but it would mean those with offset mortgages can't directly compare the interest rates you earn on savings with the interest rate of your mortgage because if the money you have in an offset will effectively "earn" flat interest, whereas money in savings is usually compounded monthly so your effective rate is a little higher.

Either way though, I can't quite work out how First Direct's online monthly payment calculator does it's calculations because that gives me a monthly payment figure thats slightly higer than Excel / FSA website (ie with no compounding) but significantly less than the figures I get from Excel if the effective interest rate is calculated to take into account daily interest, using the financial functions in Excel EFFECT and NOMINAL.

Can anyone shed any light or point me in a direction how to calculate it more accurately taking compounding into consideration?

thanks

Locoblade

By the way, there aren't huge descrepancies between compounded and non compounded, but as an example, a mortgage of £100k over 25 years at 6%, the difference that daily compounding makes seems to be around £10 per month additional payment compared to what the FSA website calculator shows (with no compounding)

getmore4less

Barclays offset tracker.

Daily interest on net ballance, added at the end/1st of the month

compounded monthly NOT compounded daily.

NOTE: for this loans the payment day is the 16th and FIXED not the date the interest is added.


Also the monthly payment is calculated based on a year so does not exacly match every months interest.


The trick is to work out how the daily interest rate is calculated to get a spreadsheet to match.

Locoblade

Thanks, so although the interest is calculated daily, its only added to the mortgage monthly, and as you say it effectively compounds monthly but with slightly less interest added because of the daily calculation which will take into account your previous payment mid month.

Looks like I'll need to find out how FD calculate theirs to get my spreadsheet correct.

Locoblade

Right, according to FD's website their calculations are similar to Barclays, in that interest is calculated daily but only charged monthly in arrears.

Now all Ive got to do is work out how the hell I encorporate that into my spreadsheet

InMyDreams

Locoblade wrote: »

Right, according to FD's website their calculations are similar to Barclays, in that interest is calculated daily but only charged monthly in arrears.

Now all Ive got to do is work out how the hell I encorporate that into my spreadsheet

According to my calculations, FD do it very simply. Take your interest rate and divide by 365 for the daily interest rate. You will be charged that on whatever your balance is each day, but it is never actually added to the mortgage because you pay it separately. I've based this on the following figures...

My balance is a constant £146,299 (it's interest only). My interest rate is a fixed 4.99%. My billing cycle is from the 11th to the 10th of each month. On the 10th of each month I receive a request for the interest for that period. On days where the period is 31 days, I owe exactly £620.00 (before offset deductions) and for 30 days I owe £600.00. But here's the odd part (that's different to other mortgages I've had I think) I don't actually pay that interest until the 1st of the following month but it never appears on my mortgage balance either. So I never pay interest on my interest, even though I pay it 3 weeks late.

Using the calculations I've suggested, I should be charged £20.00088 per day. I would say that's close enough!

I imagine that any capital repayment (either regular or one-off) would simply mean a balance adjustment on the day it's handed over and the daily interest charged would change at that point but still wouldn't itself effect the capital balance.
That should be easy enough to incorporate into a spreadsheet. :beer:

Locoblade

That probably explains it thanks, if they actually change the amounts each month to take into consideration the days in the month then it looks like their website calculator takes an average of those rather hence why the figures were slightly out compared to mine.