#1   Report Post  
Old May 8th 08, 12:17 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
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.

  #2   Report Post  
Old May 8th 08, 10:13 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
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   Report Post  
Old May 9th 08, 06:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
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   Report Post  
Old May 9th 08, 06:47 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
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   Report Post  
Old May 9th 08, 08:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
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.



  #6   Report Post  
Old May 9th 08, 09:37 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:11 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017