Interest calculation

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
calculation
Thanks Tricia

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 basis.
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:
Monthly Deposit Interest Rate Term Initial Amount Future Value
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:
=FV(B2/12,B3*12,-B1,-B4,0)
Thanks Tricia

In message , Ronald Raygun writes Well done at the back.

In message , Tricia writes 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.