Hi I am new to computers and excel spreadsheet and was wondering what
formula is used by banks etc to calculate interest on a 50 deposit per
month when the interest rate was say 4%. I.E if I saved 50 per month for 5
years how much would I have? Also if I had a lump sum when I started saving
50 per month. Or point me towards a website that explains interest
First, unless you are able to complete the appropriate IR declaration,
the banks will deduct 20% income tax from the interest they pay you.
So 4% really means 3.2%.
Second, it depends on how often the bank pays and compounds interest.
Typically they might do so only once a year, but while of course taking
into account the daily balance. Generally this means they add up all
the daily balances and divide by the number of days involved.
If interest is paid on the anniversary of the first deposit, and your
depositing regime involves investing the same amount each month and
making no withdrawals, the result you get will be almost exactly the
same as if you averaged the balance on a monthly instead of daily
So in month 1 you'd earn 1/12 of 3.2% of £50, in month 2 it'd be
1/12 of 3.2% of £100, and so on. At the end of the year you'd have
earned [(1+2+3+..+12)/12 = 13x6/12 = 13/2] x 3.2% of £50, which is
£10.40. Plus you'd have your accumulated principal of 12x£50.
Bottom line: £610.40 at year 1.
For year 2 and subsequent years, treat it as a special case of your
other question, i.e. assume you're investing £50 a month on top of
an initial balance of £610.40.
Then you'd just add 3.2% of the lump sum. So if you have £1000 and
then add £50 each month, you earn £10.40 interest on the monthly £50
plus £32 on the £1000.
Your original question "what will it be worth after 5 years" is now
answerable by noting that 1 year into the plan, it's worth £610.40,
and 2 years into the plan it'll be worth 1.032 times as much, plus
the extra £610.40 coming from the next dozen £50 contributions. The
pattern emerges, that if at year X you have Y, then at year X+1 you'll
have Yx1.032 plus £610.40:
At year 0 you have £610.40 times 0.
At year 1 you have £610.40 times 1.
At year 2 you have £610.40 times (1.032 + 1).
At year 3 you have £610.40 times (1.032^2 + 1.032 + 1).
At year 4 you have £610.40 times (1.032^3 + 1.032^2 + 1.032 + 1).
At year N you have £610.40 times (1.032^(N-1) + ... + 1.032^0),
which is £610.40 times (1.032^N - 1)/0.032,
which for N=5 is £610.40 times 5.3304, or £3253.68.
If you also have an additional lump sum of Z, you can split it off and
treat it as compounding separately, so after 5 years it'll be worth
Z x 1.032^5.
Hi Thanks for the interesting explanation on how interest is calculated, but
I was looking for something along the lines of this which was emailed to me.
On a blank worksheet, type the following labels in cells A1, A2,
A3, A4, and A6, respectively:
Next, type your monthly deposit, your account's yearly interest
rate, the number of years you'll be saving, and the amount you'll
be starting with in cells B1, B2, and B3, respectively. Finally,
type the following formula in cell B5, and press Enter:
In message , Tricia
Thats a bit like saying "if your plane is leaving at 15.30 hours then
look for the big hand on 30 and the little hand between the 3 & the 4"
but in any event (I am sure Ronald will correct me if Im wrong) it gives
the wrong answer because it fails to take account of the last months
interest and it makes some huge assumptions as to the compounding
frequency of the deposit taking institution concerned.
For £50 a month @ 5.% per annum (forget tax, its irrelevant for this)
with no initial deposit and with interest compounding monthly the FV
formula gives a total of £613.94 at the end of the year (if you spotted
the error in the description you posted which fails to make it clear
that you must express the interest rate as a decimal), whereas in fact,
the true answer is £616.50.
Forget both suggestions so far and just build a spread sheet with a line
for each month. I tried to past an example here but I wont align
propewlrly, never mind, but form Ronalds explanation I am sure you can
figure it out.
Start with £50. IN the next cell put the rate of interest. IN the next
cell divide the previous cell by 100 and by 12. IN the next cell
multiply the cell with £50 in by the cell with the 100 and 12 in it. Add
this cell to the £50 cell. Repeat this downwards remembering to add the
answer from one line to the £50 cell in the line below.