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.

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.

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.