Hi Chris,

Some how, the increament of s should be related to fv(s)-val.
For example:

....
s=s+(fv(s)-val)/val
Loop until ABS(fv(s)-val) < 0.001 'This is tolerance

Note:
1- Pay attention to the sign (+ or -) of the fv(s)-val related to s so
that when fv(s)val the s will be increased or reduced accordingly.
2- The above expression will help to speed up the convergence
3- The tolerance will be defined up to you

Regards


M100C Wrote:
All,
This is more of a general programming question, but I am posting to
this
group to see if I can get help with the answer.

I have a public function that sums the future values (fv) of
investments,
using a given rate of return (r). For example, the syntax is "fv(r)",
where
"fv(0.03)" would return the future values at an annual growth rate of
3%.

Now, suppose the value returned is 30,000 but the actual value I was
expecting was 50,000 (val). I need a procedure to: 1) increment r, 2)
pass
it to fv, and 3) iterate through 1 and 2 until the fv function returns
a
value "close to" val.

What I have so far (see below) works well, but is ugly. Is there a
more
elegant way to do this iteration?

Thanks,
Chris

VBA:
'Start by guessing at 3%
s = 0.03

If fv(s) < val Then
Do
'increment by whole percents
s = s + 0.01
Loop Until fv(s) val

'oops ... too far ... back out a percent
s = s - 0.01

Do
'increment by a tenth percent
s = s + 0.001
Loop Until fv(s) val

'oops ... too far ... back out a tenth percent
s = s - 0.001

Do
'increment by a hundredth percent
s = s + 0.0001
Loop Until fv(s) val

'oops ... too far ... back out a hundredth percent
s = s - 0.0001

Else
' Code here if guess is too high
End If


--
Rock


------------------------------------------------------------------------
Rock's Profile: http://www.excelforum.com/member.php...o&userid=29723
View this thread: http://www.excelforum.com/showthread...hreadid=497297