why does my interest calculation never coincide with what I get?

WhoisDannie

When I calculate how much interest I can expect from my bank account in excel why does it never coincide with what I actually get.
i.e 

£34,637.63

at 5.05% for 29 days according to my excel formula gives £138.98 yet I actually get  £139.42.
=IPMT((S17/365*29),1,1,-S16) where S16 is 

£34,637.63 and S17 is %5.05

Hoenir

Could you provide dates and balances from 1st February. 

eskbanker

WhoisDannie said:

When I calculate how much interest I can expect from my bank account in excel why does it never coincide with what I actually get.
i.e 

£34,637.63

at 5.05% for 29 days according to my excel formula gives £138.98 yet I actually get  £139.42.
=IPMT((S17/365*29),1,1,-S16) where S16 is 

£34,637.63 and S17 is %5.05

Is 5.05% definitely the gross rate rather than the AER?
Is the interest period definitely aligned with calendar months?
When recognising that February has 29 days, it seems odd to use 365 days as the divisor!
Was the balance constant throughout the interest period?

What's the provider and product?

Cold_comfort

The interest you received would be consistent with an interest rate of 5.08% (and an AER of 5.20%) rather than 5.05% (and allowing for 366 days in the year).

139.42
=IPMT((S17/366*29),1,1,S16) where S17 = 5.08% and S16 = 34,637.63

BooJewels

I just received interest from an NS&I Guaranteed Income Bond at 5.27% monthly interest, of course.  I definitely got annual interest / 365 days x 29 - to the penny.  I put that calculation into my projection column, not knowing how they'd treat the leap year and was surprised when it was actually spot on.  It would have been 34p less if they'd used 366 days.

I also get the same number as @Cold_comfort if using 5.08%, as that's the monthly rate for the Santander EA and 366 days.  It would be £139.80 if they used 365 days in that case.