Prev: Average numbers in noncontiguous columns ignoring 0's.
Next: Function similar to "LEFT"
From: JayM on 15 Apr 2010 16:46
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.
From: Joe User on 15 Apr 2010 17:26
"JayM" wrote:
> 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.
 | 
Pages: 1
Prev: Average numbers in noncontiguous columns ignoring 0's.
Next: Function similar to "LEFT"