Errata...

I wrote:
=FV(annualRate/365, currentDate - startDate, 0, -principal)

I assumed that you would know the nominal annual rate. That is probably
wrong. In the US (at least), the rate of return is stated as an APY
(compounded annual rate); also stating the nominal annual rate is optional.

See Fred's response for the best way to use the APY, assuming that interest
is compounded in the first place.


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

"JoeU2004" wrote in message
...
"thehed" wrote:
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.

Okay. That is very different from something "sort of like an amortization
schedule for a mortgage" that you mentioned in your original posting.

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. The CD worksheet can get the summary date by
referencing the date cell on the summary worksheet.


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.

Yes, you could do that. It would be simply:

=FV(annualRate/365, currentDate - startDate, 0, -principal)

To give you some idea of how big the "ballpark", consider a $10,000 5-year
CD at 5% compounded monthly, and currentDate is a month before maturity.
Your ballpark calculation would be effectively:

=FV(5%/365, 365*5 - 30, 0, -10000) [$12,787.38]

The actual value would be effectively:

=FV(5%/12, 12*5 - 1, 0, -10000) [$12,780.34]

Less than 0.1% error. I would agree that's a good ballpark figure.


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

"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.