Calculating interest earned on savings

geek84

Hi Folks

This may sound like a silly question, but if an individual has some savings in, say, a bank, how would he/she calculate the daily interest earned, taking into account the fact that he/she will be adding/withdrawing money from the account about 2 or 3 times a month?

Would the formula to calculate the daily interest look something like this -

Amount of Savings multiplied by interest rate divided by 365

Are there any websites or 'calculators' available on the internet that can do this automatically?

Thanks in advance for your response.

anselld

geek84 wrote: »

Amount of Savings multiplied by interest rate divided by 365

Approximately correct.

To get a precise daily rate you need to use "to the power of" (^)

eg Annual rate 5%

Daily Rate 1.05 ^ ( 1/365) - 1

mildred1978

geek84 wrote: »

Hi Folks

This may sound like a silly question, but if an individual has some savings in, say, a bank, how would he/she calculate the daily interest earned, taking into account the fact that he/she will be adding/withdrawing money from the account about 2 or 3 times a month?

Would the formula to calculate the daily interest look something like this -

Amount of Savings multiplied by interest rate divided by 365

Are there any websites or 'calculators' available on the internet that can do this automatically?

Thanks in advance for your response.

Aren't you studying accountancy?!

System

If you use a spreadsheet program like Microsoft Excel (or similar) then you can calculate interest as follows:


Daily Interest = (Balance*((RateOfInterest ^ (1 / 365))-1))
Monthly Interest = Balance*((RateOfInterest ^ (1 / 365))-1)*(365/12)


Where
Balance - Is the current balance in the account
RateOfInterest - Is the APR percentage rate (e.g. 1.025 is for 2.5%)

So, as an example, you could create a spreadsheet to calculate the monthly interest that should be credited based on the current balance.

[FONT=Courier New][SIZE=2]

[COLOR=Blue]        Column A     Column B          Column C   Column D
Row 1[/COLOR]     Date        Particulars       Credit     Balance
[COLOR=Blue]Row 2[/COLOR]  31-Jul-2011   Opening amount               £30,262.31
[COLOR=Blue]Row 3[/COLOR]  31-Aug-2011   Interest          £ 63.47    £30,325.78

[/SIZE][/FONT]
[FONT=Courier New][SIZE=2]
[/SIZE][/FONT][COLOR=Magenta][I][FONT=Courier New][SIZE=2]To calculate the monthly interest between two dates:
Formula in cell (C3) = (D2*((1.025 ^ (1 / 365))-1)*(A3-A2))[/SIZE][/FONT]   [/I][/COLOR]

talexuser

Thank you for that, very interesting.

Is there any way for excel to calculate the number of days between 2 dates, so if you have a statement with the dates of deposits and withdrawals and balances you can have a column of number of days with that particluar balance and multiply that by the daily interest formula for an interest for balance column, and then add up the interests for an accurate check of a yearly interest?

Of course if the interest rate changes during the year, then you would have to enter the change dates (perhaps with same balance etc), and update the daily interest formula accordingly - ummm, my head starts to hurt...;)

opinions4u

talexuser wrote: »

Thank you for that, very interesting.

Is there any way for excel to calculate the number of days between 2 dates

Just type in your two dates in to cells A1 and A2.

Then in cell A3 put a formula "=A2-A1".

It really is that simple.

Make sure you format A1 and A2 for dates and A3 for numbers.

talexuser

thank you, I'll try the yearly interest totals.

SnowMan

Here is a link to a spreadsheet that enables interest on an account to be checked. It's based on my excel version of a spreadsheet I created but moved into google docs (so had to tweek it a bit).

In the example the account is one where the initial deposit is £300 on 1/1/2011 followed by £300 on 2/2/2011 followed by withdrawal of £300 on 1/4/2011. The AER is 4% initially changing to 5% on 1/3/2011 and then to 5.5% on 6/4/2011 (I wish).

In this case the account has been credited with it's first interest payment by the bank of £4.30 of net interest on 1/5/2011. The spreadsheet estimates the interest that should have been paid in cell k12 of 4.28 (net). So we would conclude if this was a real situation that the interest paid looks correct.

Click on the cells in column I to see the formulae being used. Effectively this column calculates a running balance including accrued gross interest (the accrued interest is zeroed after an interest payment).

The formula is based on an Amount increasing with gross interest over a period of days to

Amount x (1 + AER/100)^(Period in days/365.25)

Note the formula works regardless of whether interest is paid monthly or annually as long as you put in the AER (i.e. the equivalent annual rate). Some (not all) institutions may in practice pay marginally higher interest than that calculated by the spreadsheet because of the old -fashioned way they calculate interest between 2 dates.

opinions4u

SnowMan wrote: »

Note the formula works regardless of whether interest is paid monthly or annually as long as you put in the AER (i.e. the equivalent annual rate).

Surely you should apply the gross rate rather than the AER if the interest is being capitalised within the spreadsheet?

SnowMan

opinions4u wrote: »

Surely you should apply the gross rate rather than the AER if the interest is being capitalised within the spreadsheet?

No it is the AER. Easiest seen if the interest is paid monthly.

Consider an account which pays 5.8411% gross monthly or 6% AER where (1+0.058411/12)^12=1.06.

The first monthly interest on 10K is 0.058411 x 1/12 x 10,000 = 48.68

By the formula stated earlier (using AER) interest is calculated at 10,000 x 1.06^(1/12) - 10,000 = 48.68 which agrees

If you use 10,000 x 1.0584^(1/12) - 10,000 = 47.42 which isn't right.

So it is the AER rather than the gross rate.

It is the use of the power of 1/12 which adjusts for the fact interest is paid monthly so you start with the annual interest rate (i.e. AER).

I said earlier the interest is zeroed in column I after an interest payment in the spreadsheet but I should also have probably made it clear also that the interest actually paid is then treated as an addition to the balance as would appear if looking at the account on-line and as per the spreadsheet (i.e. the increase of 4.30 to give the final balance of £304.30 in the linked spreadsheet). Perhaps that is where the confusion has arisen.

Reverting to the example, month 2 interest is 10048.68 x 0.058411 x 1/12 = 48.91

and 10048.68 x 1.06 ^ (1/12) - 10048.68 = 48.91 also.


It sounds complicated but I think it is far and away the best and simplest formula to use to check interest as you don't have to worry about whether interest is paid monthly or annually, the spreadsheet just checks interest when an interest payment is made based on the AER.

It should give an exact calculation for interest for organisations such as Egg who use the power based modern method of calculation. It should give a very small understatement (and it is small) compared with the historical method (still in most common use) based on the fraction of a year method. But if you are being paid marginally too much interest why worry.

Should also say I have been checking interest payments for years using the above method and have always got incredibly close to the right answer.

The only exception has been Tesco who have been mysteriously generous with their interest, by a small enough amount for virtually no-one to notice, but unusually generous all the same. Will probably change with the switch to the new computer system

Yours geekily

SnowMan

opinions4u

It's never going to be spot on unless you use the gross rate, because the gross rate is the contractual rate of interest in law. It's what the banks actually pay before deduction of tax. AER is merely a notional rate calculation for marketing comparison purposes.

Another weakness of using the AER in the calculation is that it takes no account of the tax deduction. In the real world, where interest is added monthly and net of tax, the net rate is calculated against the gross rate and not the AER. Where a bonus rate last for less than a year, using the AER falls flat on its face. Where interest is paid annually, the AER and the gross are the same, so it would work fine.

As a rough guide it does the job well enough. But it isn't going to be accurate unless the gross rate is used.

The 365.25 days a year is also an interesting one - different providers handle the leap year in a range of peculiar ways!