Daily Interest

sav4it

Due to frequent fluctuations in a savings account,I want to calculate the interest earnt each day by inputting in an excel spreadsheet.
Is my thinking right:
If interest is 10% then daily interest is simply 1/365th of this ie 0.0002739726?
If I have one column for the amount in savings account each day and another for interest earnt previous day and add to new balance in another column, that should work?
And finally multiply by 0.8 to take tax into consideration?

BUT WHAT INTEREST RATE WOULD I USE?
I'm confused with gross, AER,EAR..... PLEASE HELP.

edit: I've come to the conclusion that it's the gross rate that I should do 1/365th of. In any event the account I'm analysing pays interest annually so gross=AER. Right?

Milarky

This is how I lay out a spreadsheet to calculate the running interest:

'A2' gives the cell reference, whereas '[31/07/07]' indicates contents (in this case actual data - and formulas are similarly indicated (eg 'D3' whose formula is given as: '=D2+B3-C3'

Date....................Credit........Debit........Balance..................Interest
A2 [31/07/07]......................................D2[£0.00]..............E2[6%]
A3 [01/08/07].......B3............C3............D3[=D2+B3-C3]......E3[=(A3-A2)*E$2*D3*0.8/365]
A4 [03/08/07].......B4............C4............D4[=D3+B4-C4]......E4[=(A4-A3)*E$2*D4*0.8/365]
A5 [06/08/07].......B5............C5............D5[=D4+B5-C5]......E5[=(A5-A4)*E$2*D5*0.8/365]
A6 [11/08/07].......B6............C6............D6[=D5+B6-C6]......E6[=(A6-A5)*E$2*D6*0.8/365]
A7 [31/08/07].......B7............C7............D7[=D6+B7-C7]......E7[=(A7-A6)*E$2*D7*0.8/365]

The important point I would note is that you can have balances changing on odd dates - not simply daily - so a 'difference' formula is used. [Also the use of 'absolute' referencing - in this this case to the cell containing the current interest rate - cell 'E2'. Addressed as 'E$2' instead means that copying the formula down the rest of column E doesn't change the reference to that particular cell. This can be useful if the interest rate is changed. Alternatively you could use another column to maintain the current interest rate and just enter a different figure in that column whenever the rate does change]

Finally the interest in column E accumulates but is not added to the balance every day (unless the account has daily compounding) so it is simply added up at the end - but the running balance in column D simply consists of the previous balance plus anything that might have been added on that date less anything that might have be withdrawn.

nrsql

Depends on when the intrest is paid. If your interest is paid daily and the rate you are using is the daily rate then sounds like you are correct.
If anything else you will be overstating it.

I use the annual rate for yearly interest and monthly for monthly interest.
e.g.
BB and Yorrshire have same ISA rate but BB is monthly and Y annual so I use 5.89% for BB and 6.05% for Y. I will update the balance of BB each month so the daily interest will increase month by month. Y will be greater but the capital and interest will be constant until year end. I will also hold the net apr so I can compare savings. I am interested in the daily interest so that I can compare it against expenses not in the total interest.

By what you are doing you should probably accrue the interest daily but not add it to the capital until it is paid. If you do that make sure you use the correct rate as in the example above.

Chrismaths

This is a bit over complex, you shouldn't be compounding interest until it is paid. Interest does not accrue on accrued interest!

If you really want to calculate accrued interest, you should have a seperate column for accrued interest, that is paid across once a year. The formula for the accrued interest would be
(accrued interest from previous day)+(net interest rate)/365. There is no compounding until it is in the "real" balance.

nrsql

He's saying the same thing. Calculate the interest daily on the capital and add it to the capital when it's paid by the institution (not daily). That won't be the exact amount you have accrued probably.
Use the monthly rate if it is paid monthly, annual rate if it is paid annually.

oldfella

yes, if its annual interest than divide the gross by 365 and add the interest at the end of the year

if its monthly, divide the gross by 365, and add the interest monthly

frivolous_fay

Sorry to bump... just getting my head round interest rates. I'm [EMAIL="I@guewssing"]guessing[/EMAIL] from the above that an account that pays the interest on maturation (say, 1 year) will have an impact on the attractiveness of that rate....

Eg. a 1 year 12% account with interest added at the end of the year will mean a straight 12%, (before tax) and no compounding, right?

nrsql

That's right.
It's why you get an aer to make it easier to compare rates.