L
LeeB
What function do I use to calculate the payout (or future value) of a
Certificate of Deposit?
Certificate of Deposit?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
joeu2004
LeeB said:What function do I use to calculate the payout (or future value) of a
Certificate of Deposit?
You need to look at the details of the CD disclosure statement to see how
interest is computed. There are several methods in practice.
If interest is compounded daily, the future value of the CD can be computed
by:
=fv(rate/365, daysHeld, 0, -deposit)
where "rate" is the interest rate, not the APY, and "daysHeld" is computed
by subtracting the date of deposit from the date of maturity.
I want to reiterate the fact that not all CDs work this way. Some CDs
compute simple interest on the balance, reinvesting the accumulated interest
periodically (weekly, monthly or quarterly).
Even "daysHeld" is not determined in a uniform manner. For example, some
"9-month" CDs mature in 270 days. Some CDs mature on the anniversary date 9
months late; for example, a 10/1/2005 CD maturing on 7/1/2006 -- 273 days.
J
joeu2004
Errata....
Actually not necessary if you use the __APY__ correctly in the
computation.
The following formula should always work, as long as "daysHeld" is
determined correctly (see below):
=fv(rate(365, 0, -1, 1+APY), daysHeld, 0, -deposit)
where "APY" is expressed as 5.12% or 0.0512, for example; not simply
5.12 (without "%"). The latter is a common mistake.
This presumes that you do not withdraw interest periodically, an option
that some banks offer.
That formula relies on fact that an advertised APY conforming to the US
"Truth in Savings" regulation (which it must if the CD is FDIC insured)
is determined based on a formula that presumes that interest is
compounded daily, whether or not it is. The TIS APY is computed as
follows:
=( ( 1 + (totalInterest / principal) ) ^ (365 / daysHeld) ) - 1
Regarding "daysHeld".... As I wrote previously, "daysHeld" is not
determined in a uniform manner. For example, some "9-month" CDs mature
in 270 days. Some CDs mature on the anniversary date 9 months later;
for example, a 10/1/2005 CD maturing on 7/1/2006 -- 273 days.
Admittedly, the difference is relatively small.
You need to look at the details of the CD disclosure statement to see how
interest is computed. There are several methods in practice.
Actually not necessary if you use the __APY__ correctly in the
computation.
If interest is compounded daily, the future value of the CD can be computed by:
=fv(rate/365, daysHeld, 0, -deposit)
where "rate" is the interest rate, not the APY
The following formula should always work, as long as "daysHeld" is
determined correctly (see below):
=fv(rate(365, 0, -1, 1+APY), daysHeld, 0, -deposit)
where "APY" is expressed as 5.12% or 0.0512, for example; not simply
5.12 (without "%"). The latter is a common mistake.
This presumes that you do not withdraw interest periodically, an option
that some banks offer.
That formula relies on fact that an advertised APY conforming to the US
"Truth in Savings" regulation (which it must if the CD is FDIC insured)
is determined based on a formula that presumes that interest is
compounded daily, whether or not it is. The TIS APY is computed as
follows:
=( ( 1 + (totalInterest / principal) ) ^ (365 / daysHeld) ) - 1
Regarding "daysHeld".... As I wrote previously, "daysHeld" is not
determined in a uniform manner. For example, some "9-month" CDs mature
in 270 days. Some CDs mature on the anniversary date 9 months later;
for example, a 10/1/2005 CD maturing on 7/1/2006 -- 273 days.
Admittedly, the difference is relatively small.