"Fred Smith" wrote:
The only complication would be the compounding period,
which I doubt is monthly -- it's more likely annual.

Well, perhaps that depends on the country of origin, as well as the
institution.

In the US, Wells Fargo Bank offers 3, 6, 10, 13, 18 and 25-month CDs and a
1-yr CD, all of which compound daily. Scwhab Brokerage offers CDs ranging
from 1 month to 10 years. The 1 and 3-month CDs pay interest at maturity;
of the remaining, 60 pay monthly, 26 pay semi-annually, and 12 pay at
maturity (all of which are 1-yr or under).

However, it should be noted that Schwab CDs do not normally compound
(although reinvestment might be an option for some; none in my experience),
so the APY is the same as the annual rate. And Wells Fargo specifies only
the APY (compounded rate) for its CDs.

So, if the OP has only the APY, not the annual rate (APR), and if interest
compounds at any frequency (note: that is not a valid assumption for the
majority of Schwab CDs), the daily interest rate can be approximated by
RATE(365, 0, -1, 1 + apy), where "apy" is expressed in the form 1.23%. So
the "daily" FV expression could be:

=FV(RATE(365,0,-1,1+apy), currentDate - startDate, 0, -principal)

(And of course, it would be preferrable to compute the RATE() expression one
time in a cell, which can be reference in the FV() expression.)


it's more likely annual. If so, use:
=fv(IntRate,(today()-PurchaseDate)/365,0,-InvestAmount)

Any presumption of compounding can result in a significant error if CD
interest is not compounded (reinvested).

For example, for a $10,000 5-year CD at 5% APY paid monthly without
compounding (reinvestment), both "daily" and Fred's (fractional) annual FV
formulas yield a value of $12,762.82 at maturity, compared to an actual
value of $12,500.

That is why I wrote: ``But because the terms of CDs vary widely, I would
put the "value to date"
calculation on the CD worksheet, and simply reference the calculated value
on the summary page.``


----- original message -----

"Fred Smith" wrote in message
...
It's a simply future value calculation. The only complication would be the
compounding period, which I doubt is monthly -- it's more likely annual.
If so, use:
=fv(IntRate,(today()-PurchaseDate)/365,0,-InvestAmount)

Regards,
Fred

"thehed" wrote in message
...
I think I need to ask my question more clearly.

I track my investments in Excel. I have a workbook where each
worksheet is a separate investment. The first worksheet is a summary
of the others, # of shares, Very simple average cost basis, percentage
increase(decrease). The summary worksheet pulls the security price
from the web.

I would like to put the CD on the summary sheet. I would like it to
show the "value" of the CD as of the date I am looking at it. So if
I'm looking at the summary worksheet 6 months and 1 week from the CD
purchase, the summary page will show the initial amount of the CD plus
the 6 months accrued interest.

e.g.

DATE
8/8/9

VALUE
$1001.21

Where the date is auto filled in using the proper command (=today
()?).

Maybe I could use the FV formula with the interest calculated for an
equivalent daily rate and then use the starting date and =today() to
find difference in days from start to present and plug those number of
days into FV formula.

I do NOT need this to be EXACT...just a ballpark amount for the
present value of the CD.

I will try the above solutions...I'm working nights and my brain isn't
as sharp as it needs to be.

Thanks for the help.