bank cal for interest on cd

[Frank Malone]

What is the formula a bank uses for calculating the interest on a cd for
$10,000 for 4 years with a APR of 5% and not receiving any money until the
end of 4 year period. What formula do they use if you recieve a check each
quarter for same amount and interest.

[Fred Smith]

The bank should be using the FV formula, as in:

=FV(5%,4,0,-10000)

If you want just the interest amount, subtract the original principal.

If you receive a check every quarter, total interest would be:

=10000*5%*4

--
Regards,
Fred


"Frank Malone" <[email protected]> wrote in message
news:[email protected]...
> What is the formula a bank uses for calculating the interest on a cd for
> $10,000 for 4 years with a APR of 5% and not receiving any money until the end
> of 4 year period. What formula do they use if you recieve a check each quarter
> for same amount and interest.
>
>

[[email protected]]

Frank Malone wrote:
> What is the formula a bank uses for calculating the interest
> on a cd for $10,000 for 4 years with a APR of 5% and not
> receiving any money until the end of 4 year period.

The term APR is ambiguous here. I assume you mean nominal annual
interest rate, not effective compounded interest rate. US banks often
use the terms "annual rate" and "yield" respectively.

(Caveat: But I have found that investment brokers that offer CDs are
sloppy with the terminology. It is wise to always ask.)

In the US, bank CDs usually compound daily; but some might not, so
check the terms of the CD and adjust the formulas below as needed.

For daily compounding, the total interest would be:

=fv(5%/365, 365*4, 0, -10000) - 10000

> What formula do they use if you recieve a check each quarter
> for same amount and interest.

Do you mean by "same amount __of__ interest"? I presume they pay
you the interest accumulated for the period.

The total interest would be:

=4*4*(fv(5%/365, 365/4, 0, -10000) - 10000)

The first 4 is years; the second 4 is the number of payments per year.
I separated them (instead of writing 16) to make it easy to see how to
customize the formula for other terms.

Note: Those are approximations. You would get more accurate
results by using the dates of deposit, payments and expiration to
compute the period in days.

And if you are truly quoting the effective compound interest rate
(yield),
not the nominal rate, replace 5%/365 with rate(365,0,-1,1+5%).

[Frank Malone]

Using the first formula I got 43,101.25 so one of us must of done something
wrong. In my message I put APR and I meant APY which all of our local banks
put in paper.
"Fred Smith" <[email protected]> wrote in message
news:u%[email protected]...
> The bank should be using the FV formula, as in:
>
> =FV(5%,4,0,-10000)
>
> If you want just the interest amount, subtract the original principal.
>
> If you receive a check every quarter, total interest would be:
>
> =10000*5%*4
>
> --
> Regards,
> Fred
>
>
> "Frank Malone" <[email protected]> wrote in message
> news:[email protected]...
>> What is the formula a bank uses for calculating the interest on a cd for
>> $10,000 for 4 years with a APR of 5% and not receiving any money until
>> the end of 4 year period. What formula do they use if you recieve a check
>> each quarter for same amount and interest.
>>
>>
>
>

[Frank Malone]

Using your first formula I think is what I wanted for not receiving any
interest until CD comes due. I got $2,213.86 using your formula and using a
calculator dividing 5%/4=.0125+1=1.0125 raising to the 16 power I get
$2,198.89 which is a $14.97 difference. Never understood why banks always
paid me more than I thought I had comming. But clerks never were able to
tell me why, except to say that is what computer shows.

Using your second formula I got $2012.41 so I guess this means if I received
a check each quarter this would be the total interest I receive.

I couldn't figure out how to use your last formula.
=4*4(fv(365,0,-1,1+5%,365/4,0,-10000)-10000) One more thing I made a
mistake in my message. I meant APY the way local banks in my area put in
newspaper.

<[email protected]> wrote in message
news:[email protected]...
> Frank Malone wrote:
>> What is the formula a bank uses for calculating the interest
>> on a cd for $10,000 for 4 years with a APR of 5% and not
>> receiving any money until the end of 4 year period.
>
> The term APR is ambiguous here. I assume you mean nominal annual
> interest rate, not effective compounded interest rate. US banks often
> use the terms "annual rate" and "yield" respectively.
>
> (Caveat: But I have found that investment brokers that offer CDs are
> sloppy with the terminology. It is wise to always ask.)
>
> In the US, bank CDs usually compound daily; but some might not, so
> check the terms of the CD and adjust the formulas below as needed.
>
> For daily compounding, the total interest would be:
>
> =fv(5%/365, 365*4, 0, -10000) - 10000
>
>> What formula do they use if you recieve a check each quarter
>> for same amount and interest.
>
> Do you mean by "same amount __of__ interest"? I presume they pay
> you the interest accumulated for the period.
>
> The total interest would be:
>
> =4*4*(fv(5%/365, 365/4, 0, -10000) - 10000)
>
> The first 4 is years; the second 4 is the number of payments per year.
> I separated them (instead of writing 16) to make it easy to see how to
> customize the formula for other terms.
>
> Note: Those are approximations. You would get more accurate
> results by using the dates of deposit, payments and expiration to
> compute the period in days.
>
> And if you are truly quoting the effective compound interest rate
> (yield),
> not the nominal rate, replace 5%/365 with rate(365,0,-1,1+5%).
>

[[email protected]]

Frank Malone wrote:
> I meant APY the way local banks in my area put in newspaper.

Well, the APY is the effective compounded annual rate. But I am
suspicious. It is rare for the APY to be a nice round number like 5%.
If that is indeed the APY, then the nominal rate is about 4.88%. The
bank should be able to give you both numbers, as a double-check.

> I couldn't figure out how to use your last formula.
> =4*4(fv(365,0,-1,1+5%,365/4,0,-10000)-10000)

You should write:
=4*4 * (fv(rate(365,0,-1,1+5%), 365/4, 0, -10000) - 10000)

But no matter. It is wrong if 5% is indeed the APY (effective
compounded rate). Fred's first formula is correct for the case where
interest is not paid until maturity. It is the same as my first
formula,
using the rate(...) result for the daily interest rate. I have to give
Fred's second formula some thought. Ostensibly it seems correct.
But I want to review the US legal definition of APY.

> Using your first formula I think is what I wanted for not receiving any
> interest until CD comes due. I got $2,213.86 using your formula [...].
> Using your second formula I got $2012.41 so I guess this means if I
> received a check each quarter this would be the total interest I receive.

Those numbers are valid only if 5% is the nominal rate, not the APY.

[[email protected]]

Frank Malone wrote:
> Using the first formula I got 43,101.25 so one of us must of
> done something wrong.

You did. It appears that you typed fv(5%,4,-10000), missing one
parameter. fv(5%,4,0,-10000) yields $12,155.06, which I believe is the
correct answer if 5% is indeed the APY (effective compounded rate).

[Frank Malone]

You are right I forgot to put comma after the 0 then I got 12,155.06 or
2155.06 APY as you say.


<[email protected]> wrote in message
news:[email protected]...
> Frank Malone wrote:
>> Using the first formula I got 43,101.25 so one of us must of
>> done something wrong.
>
> You did. It appears that you typed fv(5%,4,-10000), missing one
> parameter. fv(5%,4,0,-10000) yields $12,155.06, which I believe is the
> correct answer if 5% is indeed the APY (effective compounded rate).
>

[Frank Malone]

Now using your last formula I get 1963.56 but I don't think that is enough
but then again what kind of interest do you call it for the last formula. I
still like your first formula for calculating the interest. I get 2213.86
and using my pocket calculator I get 2198.89 for 14.97 difference and this
is more in line with the way bank pays me then any other formula. They
advertise it as being APY but they do show another rate being a little less.

<[email protected]> wrote in message
news:[email protected]...
> Frank Malone wrote:
>> I meant APY the way local banks in my area put in newspaper.
>
> Well, the APY is the effective compounded annual rate. But I am
> suspicious. It is rare for the APY to be a nice round number like 5%.
> If that is indeed the APY, then the nominal rate is about 4.88%. The
> bank should be able to give you both numbers, as a double-check.
>
>> I couldn't figure out how to use your last formula.
>> =4*4(fv(365,0,-1,1+5%,365/4,0,-10000)-10000)
>
> You should write:
> =4*4 * (fv(rate(365,0,-1,1+5%), 365/4, 0, -10000) - 10000)
>
> But no matter. It is wrong if 5% is indeed the APY (effective
> compounded rate). Fred's first formula is correct for the case where
> interest is not paid until maturity. It is the same as my first
> formula,
> using the rate(...) result for the daily interest rate. I have to give
> Fred's second formula some thought. Ostensibly it seems correct.
> But I want to review the US legal definition of APY.
>
>> Using your first formula I think is what I wanted for not receiving any
>> interest until CD comes due. I got $2,213.86 using your formula [...].
>> Using your second formula I got $2012.41 so I guess this means if I
>> received a check each quarter this would be the total interest I receive.
>
> Those numbers are valid only if 5% is the nominal rate, not the APY.
>

[[email protected]]

Frank Malone wrote:
> Now using your last formula I get 1963.56 but I don't think that
> is enough

Why not? Did the bank give you a different number?

> but then again what kind of interest do you call it for the last formula.

People are sloppy with terminology. The "correct" terms are
"nominal interest rate" and "effective interest rate". The latter is
the
APY. Some people call the former "APR", which is funny because
with mortgages, the APR is akin to the APY in the sense that it is a
compounded rate. Any way, if I don't use the word "nominal" , I
would just call it "the" interest rate or annual interest rate -- but
not
the annual percentage rate, to avoid confusion.

> I still like your first formula for calculating the interest. I get 2213.86
> and using my pocket calculator I get 2198.89 for 14.97 difference

It is not a question of "like" or "dislike". Both formulas are
"correct",
depending on what "kind" of interest rate you are using.

> and this is more in line with the way bank pays me then any other
> formula.

Again, it depends on what "kind" of interest is used in the
computation.

> They advertise it as being APY but they do show another rate being
> a little less.

The lower rate is the nominal interest rate -- use rate/365 for the
daily
rate. The higher rate is the effective interest rate -- that is, the
effect
of compounding the nominal rate. Use Fred's formula in that case.

[[email protected]]

Errata ....

I wrote in response to Frank Malone:
> > They advertise it as being APY but they do show another rate
> > being a little less.
>
> The lower rate is the nominal interest rate -- use rate/365 for
> the daily rate.

I should have written "__might_be__" the nominal interest rate.
It would be helpful if you posted both rates, as well as any other
descriptive text in the ad (e.g. "if compounded daily").

It is possible that the lower rate is the APY when you opt for
quarterly interest payments. Both Fred and I ***-u-me-d that
the same APY would apply to both payment options (quarterly
or at maturity). Perhaps so. But in theory, it does not have to
be the case. At the moment, I am not familiar with how banks
structure CDs that pay out before maturity.

I will try to research this. In the meantime, the more details
you can provide, the better, if only for verification purposes.

[[email protected]]

Frank Malone wrote:
> What is the formula a bank uses for calculating the interest on a cd for
> $10,000 for 4 years with a APR of 5% and not receiving any money until the
> end of 4 year period. What formula do they use if you recieve a check each
> quarter for same amount and interest.

First, let's get some terms straight. Everything I say applies (only?)
to the US. You did not specify the geographic jurisdiction that your
question applies to. For the US, we should not confuse (nominal)
annual interest rate, effective annual rate, APR and APY. I am guilty
of interchanging some of those terms myself.

For the US, APR (annual percentage rate) has a technical definition
related to mortgages and other loans. It is not directly related to
either the effective annual rate or the (nominal) annual interest rate.
We really should not use the term APR in connection with cash and
money market accounts. But sadly, many people (including some banks!)
do use the term APR in that context as a synonym for nominal annual
interest rate :-(.

For US savings and other cash accounts, banks and other institutions
might specify both a (nominal) annual interest rate and an APY. The
annual interest rate is what the bank sets. It, not the APY, is the
basis for all of the bank's interest and APY computations.

For the US, APY (annual percentage yield) has a technical definition
that is not necessarily the same as the effective annual rate. You can
learn the technical definition of APY by googling "regulation dd
appendix a", without quotes, and looking at any of the ".gov" URLs [1].
In a nutshell, the formula is (together with one Excel form):

APY = (1 + totalInterest / principal) ^ (365 / term) - 1

=fv( rate(term, 0, -principal, principal + totalInterest), 365, 0, -1 )
- 1

where "totalInterest" is the total amount of interest earned for the
term, and "term" is the full time that the account is held, in days.
Here, I have expressed APY as a fraction (e.g. 0.05); multiply by 100
to express APY as a percentage (e.g. 5%), or in Excel simply format the
cell as Percentage with at least 2 decimal places.

Note: Sometimes RATE() has trouble with this computation unless you
also include a "guess" parameter. The "guess" parameter can be
totalInterest / principal / term.

Of course, normally totalInterest is the amount that we usually want to
compute. You can estimate totalInterest by:

totalInterest = ( (1 + APY) ^ (term / 365) - 1 ) * principal

=fv(APY, term/365, 0, -principal) - principal

Note that this technical definition of APY is independent of how the
bank actually compounds and pays interest. The federal APY definition
assumes daily compounding. It is not a realistic estimate of the true
yield of your account or the effective annual rate, except in the case
where interest does indeed compound daily and interest is indeed paid
only at maturity. APY is merely a tool for comparing different terms
for different deposit offerings.

Theoretically, each bank might differ on the relationship between the
annual interest rate and the APY. You can learn a particular bank's
specifications by requesting their "Truth in Savings" disclosure
statement (aka "FDIC disclosure", a misnomer IMHO). In practice, I
suspect that all US bank specifications are very similar, if not
identical. According to the Bank of America TIS disclosure:

"We use the daily-balance method to calculate the interest on your
account. [....] The daily rate is 1/365 -- or 1/366 in a leap year --
of the interest rate. [....W]e pay interest up to, but not including
the date of the withdrawal. [....] The annual percentage yield that
applies to your account assumes that interest will remain on deposit
until maturity. Withdrawals will reduce earnings".

The take-away from that is: (1) it is the nominal annual interest
rate, not the APY, that remains the same regardless of the compounding
rate, interest payment frequency and term of the deposit; (2) interest
compounds daily, generally at 1/365 of the annual interest rate; and
(3) the advertised APY assumes that interest is paid only at maturity;
for other payment schedules (e.g. monthly, quarterly, etc), the APY
will be different.

(And for the BofA at least, since the advertised APY always assumes
interest paid only at maturity, that APY is indeed the same as the
effective annual rate. But note that it might not be the "APY" that
actually applies to the terms of your deposit.)

In your example, you said later that 5% is the APY, not the annual
interest rate. You can estimate the nominal annual interest rate
(4.88%) as follows:

=365 * rate(365, 0, -1, 1+5%)

(Previously, I said that it is "rare for the APY to be a nice round
number"; ergo, I ***-u-me-d that 5% is the nominal annual interest
rate. But apparently it is indeed common practice today for the APY to
be a nice round number, based on a sampling of online offerings.)

In the simple case where interest is paid only at maturity and the term
is a multiple of years (4), you can estimate the total interest
($2155.06) by:

=fv(5%, 4, 0, -10000) - 10000

Both the APY and the effective annual rate are 5% in this case.

Note that this is just an estimate (but a very good one!) because
interest accrues daily at the daily based on the actual number of days
between opening the CD account and its maturity date. Moreover, the
daily rate throughout the year is the
annualRate / daysInYear, which might differ between normal and leap
years.

In the case where interest is paid quarterly, you can estimate the
total interest ($1,963.56) by:

=16 * ( fv(rate(365,0,-1,1+5%), 365/4, 0, -10000) - 10000 )

The APY is 4.58%, according to US federal regulation. Note that the
APY is less(!) than the nominal annual interest rate. That is the
"cost" (effect) of compounding daily for only a quarter instead of for
the full term of the deposit.

You also might notice that that is the formula that I posted in
response the first time, although I assumed 5% was the (nominal) annual
interest rate. Later I said that formula is wrong. I now believe I
was wrong that it is wrong ;-).

On the other hand, the effective annual rate remains at 5%. Given only
the nominal annual interest rate (4.88%), the effective annual rate can
be estimated as follows:

=fv(intRate/365, 365, 0, -1) - 1

FYI, there really is no difference in computation between the two
cases. In the first case (interest paid only at maturity), you can
also estimate the total interest with the following formula, which be
useful if you use cell references in place of some of the constants:

= fv(rate(365,0,-1,1+5%), 365*4, 0, -10000) - 10000

I hope this helps to sort things out.


-----

[1] http://www.fdic.gov/regulations/laws...6500-3250.html is one
source of the "Truth in Savings" Regulation DD Appendix A.

[[email protected]]

Fred Smith wrote:
> If you receive a check every quarter, total interest would be:
> =10000*5%*4

Assuming that 5% is the APY for the case when interest is paid
only at maturity, I believe that formula computes total interest over
the 4-yr term when interest is paid annually, not when it is paid
quarterly as the OP specified. For details, refer to my lengthier
response to the OP, posted today.