Thread: APY CD Formula
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
joeu2004 joeu2004 is offline
external usenet poster
 
Posts: 2,059
Default APY CD Formula

On May 9, 10:22 am, sross002
wrote:
How should I input the dates?


That will depend on localization settings, as well as your cell
format. For the US, I can enter 1/9/2008 and 5/9/2008 into A1 and A2
respectively, and Excel recognizes those as dates.

The important thing is: do __not__ write FV(6%/365, "5/9/2008" -
"1/9/2008",....), although that might work for you.

These formulas aren't working. My answer
should be like $1,029.30. What could be wrong?


I cannot say because you do not say (a) what you are getting instead,
and (b) what you are entering, notably the dates.

At 6% compound daily, $1000 grows to $1029.27 in 176 days, assuming a
366-day year. If you are computing as of the end of business on
5/8/2008, that would suggest that the CD account was opened on or
about 11/14/2007.

How does that match reality?

Nitpick.... Since that crosses the leap-year boundary, the more
accurate formula to use in that is:

=fv(6%/366, A3-A2, 0, -fv(6%/365, A2-A1, 0, -1000))

where A1 has the start date in 2007, A2 has 12/31/2007, and A3 has the
end date (or current date) in 2008. Using 11/14/2007 and 5/8/2008,
that results in $1029.29.


----- original posting -----

On May 9, 10:22*am, sross002
wrote:
How should I input the dates? *These formulas aren't working. *My answer
should be like $1,029.30. *What could be wrong?



"joeu2004" wrote:
On May 8, 4:17 am, sross002
wrote:
Can someone help me with an APY formula for a 6% CD
investment of $1,000 with interest compounded daily?


I just want to know what the total amount will be
after 6 months if I invest $1,000.


Assume that A1 and A2 have the starting and ending dates. *Then either
of the following formulas should do the trick, whichever you feel more
comfortable with:


=fv(6%/365, A2-A1, 0, -1000)


=1000 * (1 + 6%/365)^(A2-A1)


Caveat: *In the US, financial institutions can (and usually do) use
366 instead of 365 in leap years.