Hi. Forgot to mention earlier...
Just having fun playing a little detective work here...

We will probably never hear from the OP, but when he mentioned the
following...

(((1+i)^n-(1+g)^n)/(i-g))*A

Now this gives me the answer when I have only one payment per year(A)
over a number of years(n).

The equation that he is saying "works" appears to be for payments that
are at the end of each period, and not at the beginning.
I may be wrong, but just thought I'd mention it. :)

Dana DeLouis



On 2/18/10 1:10 PM, Joe User wrote:
"Dana DeLouis" wrote, in response to excerpts
from difference postings from me:
Then, the future value (about $129,518.70) is:
=SUMPRODUCT(FV(B4,A5*(A3-ROW(INDIRECT("1:"&A3))),0,
-FV(B4,A5,-ROUND(A1*(1+A2)^(ROW(INDIRECT("1:"&A3))-1),2),0,1)))
[....]
I switched it to payments at the beginning of each month
If interested, here is what I get.
[....]
Returns:
129518.616

The difference -- my 129,518.70 (about) v. your 129,518.62 (about) -- is
due to rounding of the monthly payment, increased by 5% each year.

As I wrote in the "Notes" of my first response:

"2. The use of ROUND takes into account the real-world constraint that the
permium is paid in real currency. Not using ROUND makes a small
difference (about $129,518.62)."

I would be interested in the algebraic derivation of your formula.

I think I see one way to do it. But where I'm headed with it does not
seem to be as clean as yours. However, I do not have time right now to
finish the derivation and see if it "cleans up nicely".


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

"Dana DeLouis" wrote in message
...

I assume that premiums are paid at
the beginning of each month.

Then, the future value (about $129,518.70) is:

=SUMPRODUCT(FV(B4,A5*(A3-ROW(INDIRECT("1:"&A3))),0,
-FV(B4,A5,-ROUND(A1*(1+A2)^(ROW(INDIRECT("1:"&A3))-1),2),0,1)))


Ok. Thanks. I switched it to payments at the beginning of each month
If interested, here is what I get.

Sub Demo()
Debug.Print MyFv(150, 0.05, 0.08, 20)
End Sub

Returns:
129518.616

Function MyFv(n, gr, ir, yr)
Dim g As Double
Dim r As Double
Dim w As Double
Dim k As Double

g = 1 + gr
r = ir / 12
w = 1 + r
k = 1 + r - 1 / w ^ 11

MyFv = (n * k * w ^ 12 * (w ^ (12*yr) - g^yr)) / (r * (w^12 - g))
End Function