Calculating interest on savings account

AndyRat

Hi, I have been searching for weeks now, on here and loads of other sites for an interest calculator. Basically, I want to be able to enter my transactions and the relevant interest rates and then see what interest I should have received for each monthly interest period. The balance does not remain static during the month. Interest is calculated daily (or at least, it should be).

At the moment, the interest paid never seems to be very close to what I calculate, sometimes under, sometimes over what I think.

I was hoping to do it in a simple spreadsheet, so I could add the transactions and the interest rate changes.

thanks in advance for any help........

Stompa

A spreadsheet is the obvious way to do this. You just need a column with the balance for each day (which reflects any transactions you make). Another with the interest rate for each day, and yet another showing the interest earned each day.

That last one is the tricky one, but you can calculate it by:

=balance*(EXP(LN(AER+1)/365)-1)

where 'balance' is the balance, and AER is the interest rate for the day in question. Using this method I'm usually only a few pence out with my monthly interest calculations.

tomstickland

That's assuming that AER is daily rate raised to the power 365, which it normally isn't.
Generally the daily interest rate is actually the gross rate/365, not the 365th root of the gross rate.

ie: daily multiplier = 1 + gross%/(365*100)

Daily paying accounts also tend to compound monthly. So
AER= ((1 + gross/(1200))^12-1)/100

espresso

The other factor is income tax. Unless you have submitted an R85 tax exemption form, you will not get Gross interest but Net interest i.e. basic rate tax deducted.

:mad:

Stompa

tomstickland wrote:

Generally the daily interest rate is actually the gross rate/365, not the 365th root of the gross rate.

Thanks, I see what you mean. Curiously though (for me at least!) using rate/365 produces values which are not quite as accurate as my original method. By 'not quite as accurate' I mean they differ more from the actual amount of interest paid out by the bank/BS.

tomstickland

That's odd; read the small print on how they calculate interest. They don't normally compound it until the end of the month. ie: interest is calculated and a running total kept. This total is then moved over into the main account total at the end of the month.

Stompa

tomstickland wrote:

That's odd; read the small print on how they calculate interest. They don't normally compound it until the end of the month. ie: interest is calculated and a running total kept. This total is then moved over into the main account total at the end of the month.

Yes, you're correct, it's not compounded until the end of the month. However, using the 365th root of the rate still gives me an answer closer to the actual value than using rate/365. Now I know that doesn't make sense, but I've tried it on 3 different accounts and it's the same for them all.

AndyRat

Thanks guys, will see if I can add these to a spreadsheet to see which gives me the closest result.

At the moment a few of the transactions are significantly out (but seem to pretty much cancel each other out) and the rest are pretty close.

Any thought on how best to incorporate in a spreadsheet........so far, I have listed each transaction, and thereby derived the balance at each change, then I have used VLookup to calculate the balance at each day and calculated the interest payable per day........when I sum the interest per day for each interest bearing period, I get the result above......

Any thoughts??