From: Ronald Raygun 
Subject: Re: calculating interest using excel
Newsgroups: uk.finance
Date: Mon, 19 Mar 2007 11:46:50 GMT

Tracy wrote:

> Hi, it would appear that my method of calculating interest using excel is
> flawed in relation to income tax deducted from the interest. So if I have
> a savings account that supposedly pays 5.85% interest (ICICI high high
> interest not the "ordinary" high interest account) how do I calculate the
> interest I will get, allowing for the income tax being deducted every
> month, which I never took into account before?
> This is my present formula
> 
> =FV(C3/12,C4*12,-C2,-C5,0) where C2 is monthly deposit, C3 is interest
> rate, C4 is length of term (in years) C5 is  lump sum

What "high high interest" account?  I can't (without looking too hard)
find anything from them other than the 5.51%pa/5.65%AER one.

Your formula looks OK except that I think you want 1 at the end instead
of 0, and you need to replace "C3/12" with "C3*0.8/12" to take account
of tax deducted at source.

You need to establish whether 5.85% is the nominal annual rate (i.e. 12
times the actual rate used for monthly interest), in which case 0.0585
would be the correct value to use for C3, or whether it is the AER, in
which case (making the usual assumptions) you must compute the actual
monthly rate first, by setting C3 to 1.0585^(1/12)*12-12, but you should
really try to discover the actual rate they use by searching their
literature, because reported AERs are generally just an approximation,
and back-computing the actual rate from an AER is unsound.

If the AER is 5.85, chances are the annual rate is actually 5.70%.