annuity calculation for monthly payments but quarterly compoundin

[JayM]

I see the "Future Annuity Calculator" template but what I'm trying to do is
calculate the FV of annuity based on monthly payments with quarterly
compounding. FV calculation will not allow the mix.

 

[Joe User]

I see the "Future Annuity Calculator" template but
what I'm trying to do is calculate the FV of annuity
based on monthly payments with quarterly compounding.
FV calculation will not allow the mix.

Yes it does. You just need to know how to do it properly. Consider the
following example.

$100,000 in A1 (initial investment). $333 in A2 (monthly annuity). 5% in
A3 (annual return, compounded quarterly). 3 in A4 (term of annuity in
years). The future value is:

=FV(A3/4, A4*4, A2*3, -A1, t)

where t is 0 or 1 depend on how you model the investment return.
Specifically, t=0 if the quarterly return is based on the beginning balance
before payments (e.g., 1st-quarter ending balance is A1*(1+A3/4) - 3*A2); or
t=1 if the quarterly return is based on the ending balance after payments
(e.g. 1st-quarter ending balance is (A1 - 3*A2)*(1+A3/4)). I would opt for
t=1.

Of course, most annuities have longer terms. I chose a term of only 3 so
that it is easy to verify that the FV formula is giving you what you expect
based on a 12-quarter annuity schedule that you can construct manually.

If not, post a follow-up in this thread with the details of your 12-quarter
annuity schedule. That will provide useful insight into exactly what you
expect.

 

[JayM]

That's close but no cigar.
That does not return an actual quarterly compounding. That uses the value
of your 3 monthly deposits and calculates the deposit as 1 coming in at the
beginning of the period. That won't work when you have 3 monthly deposits
and a compounding addition of interest at the end of the quarterly period.

 

[JayM]

That's close but not cigar.
By using that method your compounding calculation is not accurate. That
method changes the deposits to being a single deposit at the beginning of the
period (quarter) rather than 3 monthly deposits; and the interest calculation
performed on a single deposit at the beginning with the compounding on the
full amount at the end of the quarter. Not an accurate calculation.

 

[Joe User]

That's close but not cigar.

As I said, if that is not exactly what you expect, post a 12-quarter annuity
schedule to demonstrate how you think the computation should be done.

That method changes the deposits to being a single deposit

"Deposits"? Do you mean withdrawals? You said this is an annuity.

the interest calculation performed on a single deposit at the
beginning with the compounding on the full amount at the end
of the quarter. Not an accurate calculation.

Sure it is, if interest is indeed compounded quarterly, as you said
originally.

Do you mean, perhaps, that interest is __paid__ quarterly, but compounds on
a different schedule, perhaps daily, perhaps monthly?

The FV function (and PV function) can be made to work whenever the payment
is regular and invariant and the interest is regular and invariant, even when
payment and interest are on different schedules.

But exactly how to make that work depends on the details. When you are
clear on the details, we can be clear on the solution.

(However, note that terms like "end of month" and "end of quarter" may not
be really "regular" if interest compounds daily because the number of days
varies per interval.)


----- original message -----

 

[Joe User]

That's close but not cigar.

Did you ever get a solution to your problem?

If not, I hope you haven't given up on us. I'm sure that someone can help
you here.

Some second thoughts of mine....

By using that method your compounding calculation is not accurate.
That method changes the deposits to being a single deposit at the
beginning of the period (quarter) rather than 3 monthly deposits

In a previous response, I questioned whether you meant "withdrawal" instead
of "deposit" because you had used the term "annuity" in your original
posting.

I had an "annuity contract" in mind. But of course, in financial
engineering, the term "annuity" also can be used to describe a series of
deposits or investments.

If that's what you have in mind (series of deposits), you probably want the
following paradigm based on my example, to wit: $100,000 in A1 (initial
investment). $333 in A2 (monthly annuity); 5% in A3 (annual return,
compounded quarterly); and 3 in A4 (term of annuity in years).

=FV(A3/4, A4*4, -A2*3, -A1, 1)

Note that the only difference is the sign of the 3rd parameter. Moveover,
A1 might be zero in your case.

This misunderstanding(?) of annuity deposit v. annuity withdrawals may be
the primary reason why the original was "close but no cigar".

That method changes the deposits to being a single deposit at the
beginning of the period (quarter) rather than 3 monthly deposits;
and the interest calculation performed on a single deposit at the
beginning with the compounding on the full amount at the end of the
quarter. Not an accurate calculation.

In a previous response, I noted that that depends on interpretation.

I suggested that you post a "12-quarter" [sic] annuity schedule to
demonstrate your assumptions more clearly. First, I meant "12-month". And
second, a 3-month (1-quarter) example should suffice.

The issue is: terminology like "compounded quarterly" is not clear. It
could mean several things. I interpreted as: interest is computed and paid
(compounded) quarterly. In that case, I believe my formula is correct, with
the change for "deposit" instead of "withdrawal" above.

However, you might have meant, for example: interest is computed monthly
and paid (compounded) quarterly. In that case, I agree that my formula can
be "significantly" off. (The degree of "significance" depends on the
interest rate. At 10%, the error is still less than 1%.)

(You might have even meant computed daily. In that case, the "paid"
frequency is not strictly regular. But modeling that as a monthly
computation should be "close enough".)

Using my example above, the monthly/quarterly model can be demonstrated as
follows.

B5 (monthly interest rate): =A3/12
C5 (initial balance): =A1
B6 (1st month interest): =SUM(C5,$A$2)*$B$5
B7 (2nd month interest): =SUM(C5,2*$A$2)*$B$5
B8 (3rd month interest): =SUM(C5,3*$A$2)*$B$5
C8 (1st quarter balance): =SUM(C5,3*$A$2,B6:B8)

You can copy B6:C8 and paste it down any number of times to compute
subsequent quarterly balances.

Notice how that model removes any ambiguity about how amounts are intended
to be computed and credited to the account.

Normally, I would model that scenario using FV as follows:

=FV(RATE(3,0,-1,1+A3/4),4*A4,-A2,-A1,1)

In effect, I have changed the quarterly compounding model to a monthly
compounding model. The RATE expression computes a compounded monthly
interest rate that equals the real quarterly interest rate. But note that
the RATE result is a fictitious rate. It cannot be used to compute the real
monthly unpaid interest.

However, I should note that the FV formula is actually an approximation.

I think it is good approximation. For deposits and interest computed
monthly and interest paid (compounded) quarterly, the error is less than
0.36% for interest rates up to 100%; less than 0.0045% for interest rates up
to 10%; and less than 0.00019% for interest rates up to 2%. (Those errors
assume an initial deposit of zero. The error can be significantly smaller
as the initial deposit increases.)

The exact formula for deposits and interest computed monthly and interest
paid (compounded) quarterly is:

=A1*(1+3*B5)^B4 + 3*A2*(1+2*B5)*((1+3*B5)^B4-1)/3/B5

where B5 is monthly interest rate defined above, and B4 is the integer
number of quarters, i.e. =4*A4.

Note that that formula has been simplified specificantly for
monthly/quarterly model. The constants 3 and 2 cannot be generalized for
other combinations of deposit/interest and compounding frequencies.
(Although I could provide a more general formula, it is more difficult to
read.)

I have not been able to develop an FV formula that exactly duplicates that
result. But I reiterate that the usual FV solution -- modeling monthly
compounding, in this case -- seems to be very good.

I hope this is useful. Again, please note that the direct application (or
not) of this particular solution to your problem depends on assumptions that
you have not yet provided. But maybe I got lucky ;-).


----- original message -----