paying money back monthly from a lump sum

Bitstring , from the wonderful person Ron said

In Excel it is pretty easy to program this sort of thing .. just have

100 rows, two columns, first column is your dates when money goes in/out, second column is the amount left.

This is the amount in the cell above, plus the interest, less any withdrawal (or plus any addition). Interest is amount of balance (above) times compounded interest rate .. i.e. (1+5.5%)^(number of days/365) * amount above will give you the new amount, plus interest (assuming the bank does add/compound daily). Copy the formula down as many times as needed. Even simpler if you assume all months are 1/12 of a year.

To check you got the right answer, if you took the £30 out at the =endof each month (i.e. you got interest on the initial £3k for a whole month before paying anything - that's £13.67 interest if the first month was Jan 2007) I reckon you'd have about £914 left when you took the last £30 out (over 8 years after you started mind!).

What do you mean "Thanks Ron"? How did you know I would answer? :-)

If the account credits (and compounds) interest monthly, it's actually easier to work out than if it does so annually.

Let k be 1.00458333 (i.e. 1+0.055/12).

Month 0: £3000.00 Month 1: £3000.00 * k - £30 = £2983.75 Month 2: £2983.75 * k - £30 = £2967.43 Month 3: £2967.43 * k - £30 = £2951.03 etc, which is something you'll be able to see how to get excel to work out.

Better:

Let A be £3000 and B be £30.

Month 0: A Month 1: Ak-B Month 2: (Ak-B)k-B = Ak^2 - Bk - B Month 3: Ak^3 - Bk^2 - Bk - B Month n: Ak^n - Sum[i=0 to n-1](Bk^i)

which is Ak^n - B(k^n-1)/(k-1)

And if you've withdrawn £3000 this means you've withdrawn £30 100 times, so n0.

So the answer is (£3000*1.579780928=£4739.34) - (£30*126.4976571=£3794.93) which is £944.41.

With annual compounding, during year 1 the balance reduces from £3000 to £2640 and then jumps up by 0.055 times the average balance that year, so up it goes by £155.10.

Then you need to work out what happens in years 2 to 8, by which time there will have been 96 withdrawals, and then take off another 4*£30, but there'll be 4 months' accrued interest to add on.

I reckon the balance after 100 withdrawals would be £896.07 plus £17.80 of accrued interest not yet credited.

In message , GSV Three Minds in a Can writes

No bank that I know of compounds daily for retail accounts. Usually only services such as 1 day fixed or overnight LIBOR deposits do this. Most retail accounts will accrue daily but will compound the accrued amount either monthly, quarterly or annually.

If the interest is accrued daily then it would be £14.01 (3000*.055*31/365) or £13.75 if calculated on a monthly basis assumes

1/12th of a year.

Right scale of figure. I got £896.07 after 8 years 4 months assuming monthly accrual and annual compounding.

In message , Ronald Raygun writes

No, the final balance would be £2670 if the £30 is taken at the end of the month, which you must have assumed in order to give full value to the initial £3k for the first month. So, for the purposes of calculating the interest, the average balance would be £2835 giving a 1st year interest payment of £155.92 (and a halfpenny which my system cunningly credits to my own account).

AAAAGGGHHHH! in my previous post in reply to GSV 3*(MiaC) I forgot about the accrued interest in the final year which, in may calc, was £22.30 (with .8p for me)

>

No, the final balance is £2640 as I stated. But yes, the balance during the whole of the final month is £2670. You've caught me out.

Quite so.

You old rogue.

Nah. I stand by £17.80.

Since we agree on the £896 balance at the end of month 4 (of year 9), we presumably agree that the interest generating balances during each of the first four months of the year are £1016, £986, £956, and £926, respectively (all plus 7p). Average £971.07. Times 4/12 times 5.5%?

Bitstring , from the wonderful person John Boyle said

Me neither. However the Op didn't specify exactly what the 5.5% was (flat, APR, monthly, whatever).

But 5.5% *31/365 accrued daily wouldn't come out at 5.5% APR would it, it'd be rather higher (5.7%-ish, iirc). You need 1.055^(31/365)-1 instead. However we are arguing about the pence, not the overall figure of £900+/-. Once you can program the spreadsheet you can assume whichever way you like. It make more difference if the £30 comes out at the start of each month!

In message , GSV Three Minds in a Can writes

No, but the OP said 5.5% PA, nor EPR. (APRs only relate to loans)

True!

In message , Ronald Raygun writes

Yes, you are right, I think I added the last month of the previous year! Any way it is piffle. The real problem is that this means I only get .294982p. Damn.