calculating interest using excel


T

Tracy

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

Tracy
 
Ad

Advertisements

R

Ronald Raygun

Tracy said:
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%.
 
T

Tracy

Ronald Raygun said:
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%.
Hi I already had a normal high interest account with ICICI (opened on the
advice of my father) where I put my savings and was "invited" to transfer
some of my savings to an account that paid out 0.2% more than the

Interest Rate (Gross annual) : 5.65%

that I already had, so I did.

I am just starting out on understanding all this savings etc but at the
moment I am not looking at the long term I just want a new car in a years
time and I want to enjoy my life just now and there is always the rich
husband in the future :) lol

plus living at home with your parents is also a financial advantage.

So back to rewriting my spreadsheet.


Tracy
 
J

John Boyle

Tracy said:
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

Tracy
For some reason that I dont understand, but which Ronald will no doubt
explain, the microsoft FV function assumes the first payment is made at
the end of the 1st month and therefore doesnt seem to take the interest
due on the last months payment into account.

I think the difference in our clacs (apart form the different advertised
rate for ICICI) is that you are using their published AER rate in your
excell fomraul whereas you should be using the gross rate, which for the
currently advertised AER of 5.65% is 5.51%. If you are actually getting
an AER of 5.85% then Ronalds calc for a gross rate of 5.7 would be
correct.

I calculate the answer by using 12 lines of the sheet, showing the
compounding interest being added monthly. Mathematically inelegant I
know, but at least it gives the right answer.
 
G

GSV Three Minds in a Can

Bitstring <[email protected]>, from the wonderful
person John Boyle said:
I calculate the answer by using 12 lines of the sheet, showing the
compounding interest being added monthly. Mathematically inelegant I
know, but at least it gives the right answer.
I beg to differ - mathematically VERY elegant because you can see WTF is
going on. We probably both recall the famous O-level maths chant of
'Show your working'. 8>.

It also works good for iterative equation solving, of the sort 'what
monthly interest rate do I need to turn £10/month into £1765 after 4
years'.
 
R

Ronald Raygun

John said:
For some reason that I dont understand, but which Ronald will no doubt
explain, the microsoft FV function assumes the first payment is made at
the end of the 1st month and therefore doesnt seem to take the interest
due on the last months payment into account.
The key lies in the "type" argument. See below the description of the
FV function provided with Linux's "gnumeric". I don't use MS excel, but
the description is likely to be compatible.

[I never dreamed I would be giving advice on spreadsheets, especially to
an old (very old) hand like JV, because I take the view that spreadsheets
are an invention of the devil].

Synopsis

FV(rate,term,pmt,pv,type)

Description

FV computes the future value of an investment. This is based on periodic,
constant payments and a constant interest rate. The interest rate per period
is @rate, @term is the number of periods in an annuity, @pmt is the payment
made each period, @pv is the present value and @type is when the payment is
made. If @type = 1 then the payment is made at the begining of the period.
If @type = 0 it is made at the end of each period.
 
Ad

Advertisements

J

John Boyle

Ronald said:
John said:
For some reason that I dont understand, but which Ronald will no doubt
explain, the microsoft FV function assumes the first payment is made at
the end of the 1st month and therefore doesnt seem to take the interest
due on the last months payment into account.
The key lies in the "type" argument. See below the description of the
FV function provided with Linux's "gnumeric". I don't use MS excel, but
the description is likely to be compatible.

[I never dreamed I would be giving advice on spreadsheets, especially to
an old (very old) hand like JV, because I take the view that spreadsheets
are an invention of the devil].

Synopsis

FV(rate,term,pmt,pv,type)

Description

FV computes the future value of an investment. This is based on periodic,
constant payments and a constant interest rate. The interest rate per period
is @rate, @term is the number of periods in an annuity, @pmt is the payment
made each period, @pv is the present value and @type is when the payment is
made. If @type = 1 then the payment is made at the begining of the period.
If @type = 0 it is made at the end of each period.
Ah !! You learn something every day! Thank you RV.
 
R

Ronald Raygun

John said:
Ronald said:
[I never dreamed I would be giving advice on spreadsheets, especially to
an old (very old) hand like JV, because I take the view that spreadsheets
are an invention of the devil].
Ah !! You learn something every day! Thank you RV.
RB, please. I can't imagine varifocals will do it for me. All this
adjusting focus by tilting my head will give me backache.
 
Ad

Advertisements

J

John Boyle

Ronald said:
John said:
Ronald said:
[I never dreamed I would be giving advice on spreadsheets, especially to
an old (very old) hand like JV, because I take the view that spreadsheets
are an invention of the devil].
Ah !! You learn something every day! Thank you RV.
RB, please. I can't imagine varifocals will do it for me. All this
adjusting focus by tilting my head will give me backache.
You'll get used to it, we all do.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top