Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
APY (annual percentage yield)
Is there a formula for a bank statemenet using monthly APY?
Scenerio: $500 initial deposit, $25 added every 2 weeks, APY (1.25%) compounded monthly Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
APY (annual percentage yield)
Icky Joe wrote:
Is there a formula for a bank statemenet using monthly APY? Scenerio: $500 initial deposit, $25 added every 2 weeks, APY (1.25%) compounded monthly What do you mean by "monthly APY"? That translates into "monthly annual percentage yield". I presume you can now see the ambiguity. If 1.25% is truly the APY -- that is, the compounded annual rate, not the nominal rate -- then the monthly rate can be computed by: =rate(12, 0, -1, 1+1.25%) Caveat: Sometimes RATE() fails with an error indicating that you need to enter the "guess" parameter. In this case, an adequate "guess" should be APY/12. But if 1.25% is really the nominal annual rate, the monthly rate is simply 1.25%/12. One problem with applying that to the scenario is that "every 2 weeks" is not the same as "semimonthly" (twice a month). Consequently, the deposits (that is, every pair of deposits) do not align nicely with the compounding interval. Another problem with the scenario is that you do not specify the period over which you want to estimate the future value, if that is indeed your intention. You need to say "every 2 weeks for N months", where you fill in N. The following might approximate the future value after 12 months: =fv(monthlyRate, 12, -25*26/12, -500) On the other hand, if your intention is simply to verify a monthly bank statement or to create an annuity schedule, you could compute the ending monthly balance as follows: =(begBalance + 25*numDeposits) * (1 + monthlyRate) Caveat: In both examples both, I assume that interest is computed based on the ending monthly balance. More typically in my experience, when interest is paid monthly, it is computed based on the average daily balance. If that is really the case for you, there is more work to be done. On the other hand, to the extent that these are only estimates anyway, the difference for these small amounts might not significant enough to justify the extra effort. HTH. If not, sorry for the misdirection. Post back and clarify your question and scenario. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
APY (annual percentage yield)
Thanks. Excellent formula. The spreadsheet will not be used based on a
time, but when a certain amount is reached. I appreciate your time and patience. " wrote: Icky Joe wrote: Is there a formula for a bank statemenet using monthly APY? Scenerio: $500 initial deposit, $25 added every 2 weeks, APY (1.25%) compounded monthly What do you mean by "monthly APY"? That translates into "monthly annual percentage yield". I presume you can now see the ambiguity. If 1.25% is truly the APY -- that is, the compounded annual rate, not the nominal rate -- then the monthly rate can be computed by: =rate(12, 0, -1, 1+1.25%) Caveat: Sometimes RATE() fails with an error indicating that you need to enter the "guess" parameter. In this case, an adequate "guess" should be APY/12. But if 1.25% is really the nominal annual rate, the monthly rate is simply 1.25%/12. One problem with applying that to the scenario is that "every 2 weeks" is not the same as "semimonthly" (twice a month). Consequently, the deposits (that is, every pair of deposits) do not align nicely with the compounding interval. Another problem with the scenario is that you do not specify the period over which you want to estimate the future value, if that is indeed your intention. You need to say "every 2 weeks for N months", where you fill in N. The following might approximate the future value after 12 months: =fv(monthlyRate, 12, -25*26/12, -500) On the other hand, if your intention is simply to verify a monthly bank statement or to create an annuity schedule, you could compute the ending monthly balance as follows: =(begBalance + 25*numDeposits) * (1 + monthlyRate) Caveat: In both examples both, I assume that interest is computed based on the ending monthly balance. More typically in my experience, when interest is paid monthly, it is computed based on the average daily balance. If that is really the case for you, there is more work to be done. On the other hand, to the extent that these are only estimates anyway, the difference for these small amounts might not significant enough to justify the extra effort. HTH. If not, sorry for the misdirection. Post back and clarify your question and scenario. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
APR (Annual Percentage Rate) calculator | Excel Discussion (Misc queries) | |||
Annual Percentage Interest cost of discount not taken | Excel Discussion (Misc queries) | |||
bank cal for interest on cd | Excel Worksheet Functions | |||
Formulas calculate specified percentage of annual budget in Excel | Excel Worksheet Functions | |||
Annual Percentage Rate | Excel Discussion (Misc queries) |