A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

APY CD Formula



 
 
Thread Tools Display Modes
  #1  
Old May 8th 08, 12:17 PM posted to microsoft.public.excel.misc
sross002
external usenet poster
 
Posts: 29
Default APY CD Formula

Hello,

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.

Thanks in advance.
Ads
  #2  
Old May 8th 08, 10:13 PM posted to microsoft.public.excel.misc
joeu2004
external usenet poster
 
Posts: 2,061
Default APY CD Formula

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.
  #3  
Old May 9th 08, 06:22 PM posted to microsoft.public.excel.misc
sross002
external usenet poster
 
Posts: 29
Default APY CD Formula

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

  #4  
Old May 9th 08, 06:47 PM posted to microsoft.public.excel.misc
sross002
external usenet poster
 
Posts: 29
Default APY CD Formula

The interest isn't compounded daily. It takes effect at takeout after 6
months. So I guess it's semi-annually.

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

  #5  
Old May 9th 08, 08:58 PM posted to microsoft.public.excel.misc
joeu2004
external usenet poster
 
Posts: 2,061
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.

  #6  
Old May 9th 08, 09:37 PM posted to microsoft.public.excel.misc
joeu2004
external usenet poster
 
Posts: 2,061
Default APY CD Formula

On May 9, 10:47 am, sross002 >
wrote:
> The interest isn't compounded daily. It takes effect at takeout after
> 6 months. So I guess it's semi-annually.


It would take a year for $1000 to grow to about $1030 at 6% compouned
semi-annually.

Be careful with terminology. The industry is quite fickle about
this. And I would not be surprised if it varies from one country to
another.

In the US, CD interest can __compound__ at one frequency (typically
daily), but it might be __paid__ at another frequency (for example,
monthly, semi-annually, annually, or at maturity).

Another fickle area of terminology is the terms "interest rate", "APR"
and "APY". For example, it is quite common for US institutions to
misuse the term "APR" where they mean APY (i.e. compounded rate),
whereas controlling US law uses the term "APR" to refer to the
(simple) interest rate.

Originally, you asked how to compute the "APY" for a "6% CD". (Oops,
I never answered __that__ question. I leaped ahead to your next
question, namely: the total amount after 6 months.)

So I interpreted 6% to be the annual simple interest rate, for which
the daily rate is determined by 6%/365 (or 366), at least for FDIC-
insured US CDs. (That is, they are following the US "Truth in
Savings" regulations.)

But the "6%" might be the APY, and you care obviously unclear about
the compounding frequency.

Get your facts straight, then post back with the details.



On May 9, 10:47*am, sross002 >
wrote:
> The interest isn't compounded daily. *It takes effect at takeout after 6
> months. *So I guess it's semi-annually.
>
>
>
> "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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:06 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.