Compound Interest

[Maddoktor]

Hi all,

How do I calculate compound interest like a bank account as interest
being calculated daily.

Thanx in advance.

 

[joeu2004]

How do I calculate compound interest like a bank account
as interest being calculated daily.

In the US typically, if the annual interest rate is 4%, the interest
compounded daily on a balance of $10,000 held since Jan 1 can
be computed by:

=FV(4%/365 ,TODAY()-DATE(2006,1,1),, -10000) - 10000

If you have a spreadsheet that records irregular periodic deposits,
with dates in A2:A3 and initial balance in B2 for example, the new
balance in B3 can be computed by:

=FV(4%/365, A3-A2,, -B2)

 

[Niek Otten]

It depends on the definitions of the rates.
As you can see from the result of this formula, it yields more than 4% in a year, although it would yield 4% if the interest was
added only once.
Depending on how your yearly rate is defined, you might have to use =NOMINAL(4%,365)/365 instead of just 4%/365.

--
Kind regards,

Niek Otten

| Maddoktor wrote:
| > How do I calculate compound interest like a bank account
| > as interest being calculated daily.
|
| In the US typically, if the annual interest rate is 4%, the interest
| compounded daily on a balance of $10,000 held since Jan 1 can
| be computed by:
|
| =FV(4%/365 ,TODAY()-DATE(2006,1,1),, -10000) - 10000
|
| If you have a spreadsheet that records irregular periodic deposits,
| with dates in A2:A3 and initial balance in B2 for example, the new
| balance in B3 can be computed by:
|
| =FV(4%/365, A3-A2,, -B2)
|

 

[joeu2004]

It depends on the definitions of the rates.
As you can see from the result of this formula, it yields more than 4%
in a year

That is why there is a difference between the nominal rate and the APR.

I have used the procedure I described for many years (decades!) to
determine
and verify returns on CDs, MMFs etc. I usually come within pennies of
the
bank's number, if not exactly the same.

Caveat: My comments are strictly from a US perspective. I am not
familiar with how interest rates are applied in other countries.

 

[joeu2004]

It depends on the definitions of the rates.
As you can see from the result of this formula, it yields more than 4% in a year

Not to put too fine a point on it, but the following web pages all
describe exactly
the procedure that I did, namely: the period interest rate is r/n,
where "r" is the
annual rate and "n" is the number of compounding periods in a year.

http://www.math.wm.edu/~lutzer/class108/spring06/Lab05.pdf
http://www.opm.gov/oca/pay/backpay/OPMINSTR.HTM
http://www.eduworks.com/Documents/Workshops/EdMedia1998/class/compound.htm
http://gs.fanshawec.ca/tlc/math270/3_3_compound_Interest.htm
http://www.math.hawaii.edu/~hile/math100/consd.htm

Note that one web page is Canadian, which surprises me considering the
way
that Canadians do mortgages. One web page is really unrelated to
banks, but
I believe it does reflect US bank law, albeit only indirectly in this
citation. (I am
having trouble finding the applicable US bank statute specifically. I
confess that
I am not making a serious effort to do so. I think it is a moot
point.)

I should also note that none of the above is dispositive insofar as
none is a
banking web page. But I take some solace in the fact that "1000
lemmings
cannot all be wrong" . I'm sure I could find 995 more supportive
web sites,
if I want to slog through all the google hits. (Not!)

 

[Niek Otten]

In no way did I want to question your answer. It's just that there is a lot of confusion about rates. I immediately accept that
your solutions is the general practice in the financial world. But sometimes questions come from people's own interpretation of
those rules. I just wanted to emphasize that there may be different ways to look at it or ways to explain different results.

Thanks for your rather convincing list of sources!

--
Kind regards,

Niek Otten


| Niek Otten wrote:
| > It depends on the definitions of the rates.
| > As you can see from the result of this formula, it yields more than 4% in a year
|
| Not to put too fine a point on it, but the following web pages all
| describe exactly
| the procedure that I did, namely: the period interest rate is r/n,
| where "r" is the
| annual rate and "n" is the number of compounding periods in a year.
|
| http://www.math.wm.edu/~lutzer/class108/spring06/Lab05.pdf
| http://www.opm.gov/oca/pay/backpay/OPMINSTR.HTM
| http://www.eduworks.com/Documents/Workshops/EdMedia1998/class/compound.htm
| http://gs.fanshawec.ca/tlc/math270/3_3_compound_Interest.htm
| http://www.math.hawaii.edu/~hile/math100/consd.htm
|
| Note that one web page is Canadian, which surprises me considering the
| way
| that Canadians do mortgages. One web page is really unrelated to
| banks, but
| I believe it does reflect US bank law, albeit only indirectly in this
| citation. (I am
| having trouble finding the applicable US bank statute specifically. I
| confess that
| I am not making a serious effort to do so. I think it is a moot
| point.)
|
| I should also note that none of the above is dispositive insofar as
| none is a
| banking web page. But I take some solace in the fact that "1000
| lemmings
| cannot all be wrong" . I'm sure I could find 995 more supportive
| web sites,
| if I want to slog through all the google hits. (Not!)
|

 

[joeu2004]

In no way did I want to question your answer. It's just that there is a lot of
confusion about rates.

I agree. You posted again before I had a chance to post my "mea
culpa".
I should learn not to post just before I rush out the door.

If your original point was "sometimes banks specify the compounded
effective
rate instead of or in addition to the nominal rate, and the OP needs to
know
which one he has and convert it appropriately to a daily rate", then I
agree
wholeheartedly. I erred in ass-u-me-ing a nominal rate without
question. I
also ass-u-me-d that when the OP referred to a "bank account", he is
talking
about an interest-earning savings or money market account, not a loan.

I just wanted to emphasize that there may be different ways to look at it
or ways to explain different results

Good point!

Depending on how your yearly rate is defined, you might have to use
=NOMINAL(4%,365)/365 instead of just 4%/365.

Yes. If the specified rate is the compounded effective rate, we could
also
use one of the following formulas, which does not require the Analysis
ToolPak add-in, to determine the daily rate:

(1+4%)^(1/365) - 1
RATE(365,, -1, 1+4%)