Help with interest workings...

GreyPilgrim

How exactly are monthly interest payments worked out..? Some nice round numbers for examples sake - £100 a month for 12 months, Interest rate of 10%

So after 12 months you have a total balance of £1,200, but the average balance over the year has been £600. Therefore an interest rate of 10% would give you total interest payment of £60 (right?)
However if I set up a spreadsheet and do each monthly interest payment of TOTAL AMOUNT * (10% / 12), the total interest comes out at £65.00
What am I doing wrong?

Jan £100 (interest = £0.83)
Feb £200 (interest = £1.67)
Mar £300 (interest = £2.50)
Apr £400 (interest = £3.33)
May £500 (interest = £4.17)
Jun £600 (interest = £5.00)
Jul £700 (interest = £5.83)
Aug £800 (interest = £6.67)
Sep £900 (interest = £7.50)
Oct £1,000 (interest = £8.33)
Nov £1,100 (interest = £9.17)
Dec £1,200 (interest = £10.00)
£0.83 + £1.67 + £2.50 + £3.33 + £4.17 + £5.00 + J£5.83 + £6.67 + £7.50 + £8.33 + £9.17 + £10.00 = £65.00

Bored...killing time at work, and was messing around with Excel. Feel free to point out my glaring error.

GP

NickX

OK you must be bored at work, but this did intrigue me.

The reason is that you are assuming that the balance increases by £100 right at the start of the month. This makes the average balance £650, not £600.

That is £100 + £200 + £300 + £400 + £500 + £600 + £700 + £800 + £900 + £1000 + £1100 + £1200 = £7800

Then £7800 / 12 = £650

If you work on the basis that the payment is made right at the end of the month, then the calculation is :

£0 + £100 + £200 + £300 + £400 + £500 + £600 + £700 + £800 + £900 + £1000 + £1100 = £6600

Then £6600 /12 = £550

However if you assume the payment is made half way through the month, so the average balance in the first month is £50, second £150 etc, then the calculation is :

£50 + £150 + £250 + £350 + £450 + £550 + £650 + £750 + £850 + £950 + £1050 + £1150 = £ 7200

Then £7200 / 12 = £600

Does this make sense ??

CLAPTON

assuming you save at the beginning of each month then your average amount invested is 650 and not 600 hence the interest earned is £65.
if you save in the middle of each month then indeed your calculation is correct.

GreyPilgrim

(*slaps self on head*)

Of course. Thanks Clapton.

My only defence for being dumb is that I've been working on a really complicated bit of VBScript for the past week and I just finished it at about 3pm. My brain is so fried I can just about remember the number for a white coffee from the vendapac machine in the kitchen.

Mozette

is the answer 42?