Savings Calculations - Excel

SparciaM

Anyone have any formulas to work out exactly how much interest you should be getting on your savings?

I've a couple of accounts that keep dropping and some that say they have dropped, but then the bank confirm not dropped yet and I wanted a way to put in my balance into a formula or something and then work out how much interest I should get - as I really don't trust the banks to be accurate in their calculations.

SnowMan

SparciaM wrote: »

Anyone have any formulas to work out exactly how much interest you should be getting on your savings?

I've a couple of accounts that keep dropping and some that say they have dropped, but then the bank confirm not dropped yet and I wanted a way to put in my balance into a formula or something and then work out how much interest I should get - as I really don't trust the banks to be accurate in their calculations.

To calculate how much interest on an amount from one date to another date (in a spreadsheet):

Cell
A1 = amount
A2 = interest rate (format as percentage), enter the AER here rather than the gross rate as this is the true annual rate (usually)
A3 = date interest from
A4 = date interest to

Then gross interest is calculated by the formula

=A1*(1+A2)^((A4-A3)/365.25)-A1

And net interest is 0.8 times this figure

The 365.25 is to allow for leap years (which is more than close enough)

When you have typed in the formula try it with some obvious dates exactly a year apart and say an amount of £1,000 and interest rate of 10%. Is gross interest £100 (to be precise within some pennies of £100 due to the leap year approximation)?


This is the easiest formula to use and can be widely applied. In practice banks work out interest on a daily basis but no need to worry about that complication as the above formula is arguably the "correct" formula and should come very close.

Does that make sense, and can you see how that can be extended where you have a table of amounts and dates and interest rate changes?