How is interest calculated on regular savings accounts ?

property.advert

I want to open a regular savings account.

Say somewhere offers you 5% (just as an example) and calculates the interest daily, but pays it once a year. What is the mathematical calculation ?

Straight away you'd think about 5%/365 but that doesn't work as it fails to take into account the effect of compounding each day on day 2 through to day 365.

I want to put it in Excel and as a daily compound, I come to a 5% number of 4.8927482% which when divided by 365 and compounded over 365 days, gives £500 interest on £10,000 over 365 days. This is found using Goal Seek.

However, it is unwieldy and requires a Goal Seek to change the rate. I am looking for a daily compounding formula to give a true percentage (in this case 5%) over 1 year.

I need it daily as I want to be adding deposits weekly at the most and perhaps daily which will need to accrue interest from that day, not from the next month etc.

Any ideas ?

rb10

You're complicating it way too much! Interest is not compounded daily, it is only compounded each time that interest is paid into the account.

The answer is quite simple: each day you earn 5%/365 based on the balance in the account.

So if you have £100 in the account for the first 7 days, you earn 100*0.05/365*7 = £0./09589. Then add this to the next amount of interest accrued, and so on.

Simply adding all the amounts will give the total amount of interest received.

A simple way to calculate this in Excel is as follows (with // indicating that you move to the next cell):

.......A.........//......B.......//.....C.....//.......D......//............E...........
[From date] // [To date] // =B1-A1 // [Balance] // =D1*0.05/365*C1
etc, then
.................................................................... =sum(E1:Exx)

Start a new line each time the balance changes. So cell B1 is the same as C2 (the end date of one balance period is the same as the start date of the next period) etc.

property.advert

So you're saying that when they say interest is accrued daily, they are actually working from the wrong mathematical formula ? Your method just accrues straight line and takes no account of compounding. Or are you saying that they just flat line it ?

The way I see it, if interested is calculated daily, then it compounds daily and thus, you need a 365 calculation.

I am not looking for an approximation, that is easy, I'm looking to replicate the true calculation. The test for this must be to not add any other payments and see the annual interest equal the 5% after 365 days, compounded over 365 days, as the interest is calculated daily.

tomstickland

If interest is paid n times per year then if the payment dates are equally spaced, the rate for each chunck will be gross rate divided by n. To allow for compounding:

AER = ( 1 + gross_rate/n) ) ^ n - 1

Where gross rate and AER is already divided by 100, eg: 0.04 for 4%.

Rearrange to:
gross_rate = n * ((AER+1) ^ (1/n) - 1 )

tomstickland

Eg: to get to 5% AER and if paid daily:

gross_rate = 365 *( ( 1.05)^(1/365) - 1) = 0.048793
ie: A gross rate of 4.88% paid daily would compound up to 5% AER.

Mr_K

Just using the regular savings calculator here might be simpler :-

http://www.moneysavingexpert.com/savings/best-regular-savings-accounts

MarkFromCornwall

tomstickland wrote: »

If interest is paid n times per year then if the payment dates are equally spaced, the rate for each chunck will be gross rate divided by n. To allow for compounding:

AER = ( 1 + gross_rate/n) ) ^ n - 1

Where gross rate and AER is already divided by 100, eg: 0.04 for 4%.

Rearrange to:
gross_rate = n * ((AER+1) ^ (1/n) - 1 )

Surely the payment dates aren't spaced equally. The months are not of equal length and weekends and public holidays further complicate things. So how do the banks actually calculate it?

rb10

property.advert wrote: »

So you're saying that when they say interest is accrued daily, they are actually working from the wrong mathematical formula ? Your method just accrues straight line and takes no account of compounding. Or are you saying that they just flat line it ?

The way I see it, if interested is calculated daily, then it compounds daily and thus, you need a 365 calculation.

Interest is calculated on the balance every day, but is not compounded daily. It is only compounded when the interest is actually credited to the account.

Accruing is very different to compounding.

(N.B. There are a small number of accounts that do actually compound interest daily. I believe that some Egg accounts do this, but don't know of any other banks that compound daily.)

tomstickland

MarkFromMullion wrote: »

Surely the payment dates aren't spaced equally. The months are not of equal length and weekends and public holidays further complicate things. So how do the banks actually calculate it?

Yes, that further complicates things.

G_M

The reason most accounts have different gross rates depending whether you opt for monthly interest or annual interest is to allow for the compounding effect of monthly payments.

The AER is (usually) the same.

If interest was compounded daily in both cases, then having different gross rates would result in different AERs.

All that 'interest is accrued daily' means is that if you withdraw/add money or close the account part-way through the month/year, your interest will be calculated on the account balance each day.

Mortgage accounts used to be classic examples of annual accrual. They would calculate the interest to be charged for each year on the balance of the account on 1st Jan. If you made a huge over-payment to your mortgage on 2nd Jan, you'd still be charged interest for the rest of the year based on what you'd owed on the 1st Jan! Thankfully this is no longer the case (unless there's still an obscure BS using the old system!)