Help in Complex calculation on interest earnings with changeable criteria !

Daniela_2

Hi there,

I wonder if anyone can help with the following complex calculation.

I definitely need help on the interest earnings from a saving account with few changeable criteria:

I have made 5 deposits into a saving account where 3 of them done before the interest rate changed, and 2 of them after that.

Each of the 5 deposits are made on different dates.

And the 5th deposit I have made triggered another factor - changed the interest rate for the portion above 50.000 limit, at this account type.

And the calculation is becoming too complex for me to handle myself.

Let's work on the Gross Rate Only disregarding AER and the net rate:

Gross interest rate was 5.19 until 16.10.2008
And it was changed to 4.70 effective from 16.10.2008
for deposits up to 50.000

My fifth deposit (made after the 16.10.2008) also changed my total balance to over 50.000 :

which means deposits exceeds 50.000 reflects a different rate at this specific account type:

example: if the balance let's say becomes 51.050, that day, the first 50.000 interest rate stays the same, the amount above it (£50 as in this example) attracts : 4.46 (bit lower than the first 50.000)

I really appreciate any help very much in advance

Interest calculated daily, paid monthly

YorkshireBoy

Interest is calculated daily at the rate of...

daily closing balance x gross p.a. rate applicable / 365.

Sum up all the days in the month/statement period.

Deduct tax, ie multiply by 0.8

Add this figure to your account, and the new total then becomes your opening balance for the next month/statement period.

Repeat.

It'll be easier on a spreadsheet.

sloughflint

Do a variation of this spreadsheet:
http://forums.moneysavingexpert.com/showpost.html?p=13347605&postcount=44

Columns A to B as above
Column C: formula =(A2-A1)/365
Column D contains gross per annum rate for under 50k
Column E contains gross per annum rate for over 50k
Column F: Formula =IF(B1<50000,B1*C1*D1,C1*(50000*D1+(B1-50000)*E1))
Cell G1: formula =SUM(F:F)
Cell H1: formula =G1*0.8

Insert a new row for each balance or rate change.

If happy with that, then you can modify slightly to accommodate for multiple interest paying cycles.

Daniela_2

Thank you indeed. I have tried to create such excel sheet, but unable to as I haven't got much experience with it.

None of the formulas I have managed to enter properly (or I am not sure if I have, as there's no way I could check the correctness

Even entering interest rate comes out may 19

any further suggestions other than having the burden on you to create such xls for me to download it please.

Just enter the formulas and interest rates I have provided and leave me the deposit amounts I have made, if you could please

if you cannot, thank you so far that you've done your best to help me

kind regards

Daniela_2

I have sent you a private message with the details earlier, would you be able to do the xls shhet for me please

sloughflint

Two small things to add on its general use:
1) the first and last dates in column A should be the date after interest has been/should be paid.
2) 50.000 if typed in would be considered as 50 not 50 000.

Daniela_2

Thank you again, sloughflint.
all the best
daniela