Calculating daily/monthly interest?

retiredin2011

Due to being retired and waiting till it is time to go on holiday I sometimes have a lot of time on my hands to worry about trivial things.


How do banks/building societies, particularly Nationwide calculate interest on accounts?


With my flexdirect account I get £10.38 gross interest, pay £2.07 tax and gain £8.31 when there are 31 days in the month and in November these figures were £10.05, £2.01 and £8.04.


Yet according to my excel spreadsheet these figures should be £10.64, £2.13 and £8.64 for 31 days and £10.30, £2.06 and £8.24 for 30 days.


For the interest rate I use 5%/365 and the formula to calculate the interest is =FV(E3,E4,-E2,-E5,0) where E3 is 5%/365 E4 number of days and E5 is £2500


E2 contains a 0 amount, it is there for calculating when a regular deposit is made.


So why does my excel spreadsheet not agree with the Nationwide? :eek:

CLAPTON

retiredin2011 wrote: »

Due to being retired and waiting till it is time to go on holiday I sometimes have a lot of time on my hands to worry about trivial things.


How do banks/building societies, particularly Nationwide calculate interest on accounts?


With my flexdirect account I get £10.38 gross interest, pay £2.07 tax and gain £8.31 when there are 31 days in the month and in November these figures were £10.05, £2.01 and £8.04.


Yet according to my excel spreadsheet these figures should be £10.64, £2.13 and £8.64 for 31 days and £10.30, £2.06 and £8.24 for 30 days.


For the interest rate I use 5%/365 and the formula to calculate the interest is =FV(E3,E4,-E2,-E5,0) where E3 is 5%/365 E4 number of days and E5 is £2500


E2 contains a 0 amount, it is there for calculating when a regular deposit is made.


So why does my excel spreadsheet not agree with the Nationwide? :eek:

surely the interest rate should be 4.89%/365

bsms1147

CLAPTON wrote: »

surely the interest rate should be 4.89%/365

Yep, OP you forgot about compound interest.

retiredin2011

Yep, OP you forgot about compound interest

Ah!.


Still 2p out though

Archi_Bald

£10.05 for 30 days gross, and £10.38 for 31, is spot on if you use the standard Excel rounding.

retiredin2011

standard Excel rounding

I must be doing something wrong with my rounding because it still shows £2510.40 for 31 days

=ROUND(FV(E3,E4,-E2,-E5,0),1)

Archi_Bald

I never used the ROUND function, I just formatted the calculation results after each step to 2 decimals. Letting Excel do what it does.

retiredin2011

I know why I get a different answer but I don't know why it happens


The excel formula I am using gives £2510.40


=FV(A1/365, A2, 0, -A3) - A3


this formula gives £2510.38 =A3*A2*A1/365 so it would appear that Nationwide does not use excel formulas.

Archi_Bald

You have lost me for good, I'm afraid. My Excel comes up with the values Nationwide come up with, and in any case, I wouldn't worry about the occasional 1 p difference.

retiredin2011

Due to being retired and waiting till it is time to go on holiday I sometimes have a lot of time on my hands to worry about trivial things

My Excel comes up with the values Nationwide come up with,

What formula do you use?

CLAPTON

retiredin2011 wrote: »

What formula do you use?

it depends upon what you assume n/wide do


but assuming they apply the 'simple ' yearly rate of 4.89%
and work out interest daily but only 'capitalise' the interest monthly


then the formula is simply

2,500 x 4.89%/365 * 30 = 10.05 for 30 days
or
2,500 x 4.89%/365 * 31 = 10.40 for 3 days