Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterative solving using VBA ...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterative solving using VBA ...
Hi Chris,
Why bother when IRR() does the job ? Cheers Carim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterative solving using VBA ...
But if you still like to do similar iterations, take the function below,
just as an example. -- Kind regards, Niek Otten ' ================================================== ================= Option Explicit Function Backward(ValueToBeFound As Double, MoreArguments As Double, _ Optional ReasonableGuess, Optional MaxNumberIters, _ Optional MaxDiffPerc) As Double ' This example function goalseeks another function, ' called Forward. It works for almost any continuous function, ' although if that function has several maximum and/or minimum ' values, the value of the ReasonableGuess argument becomes ' important. ' It calculates the value for ReasonableGuess and for ' 1.2 * ReasonableGuess. ' It assumes that the function's graph is a straight line and ' extrapolates that line from these two values to find the value ' for the argument required to achieve ValueToBeFound. ' Of course that doesn't come out right, so it does it again for ' this new result and one of the other two results, depending on ' the required direction (greater or smaller). ' This process is repeated until the maximum number of calculations ' has been reached, in which case an errorvalue is returned, ' or until the value found is close enough, in which case ' the value of the most recently used argument is returned Dim LowVar As Double, HighVar As Double, NowVar As Double Dim LowResult As Double, HighResult As Double, NowResult As Double Dim MaxDiff As Double Dim NotReadyYet As Boolean Dim IterCount As Long If IsMissing(ReasonableGuess) Then ReasonableGuess = 1.5 ' use default Values If IsMissing(MaxNumberIters) Then MaxNumberIters = 20 ' that make sense in the If IsMissing(MaxDiffPerc) Then MaxDiffPerc = 0.001 ' context of the function MaxDiff = ValueToBeFound * MaxDiffPerc NotReadyYet = True IterCount = 1 LowVar = ReasonableGuess LowResult = Forward(LowVar, MoreArguments) HighVar = LowVar * 1.2 HighResult = Forward(HighVar, MoreArguments) While NotReadyYet IterCount = IterCount + 1 If IterCount MaxNumberIters Then Backward = CVErr(xlErrValue) 'or some other errorvalue Exit Function End If NowVar = ((ValueToBeFound - LowResult) * (HighVar - LowVar) + LowVar _ * (HighResult - LowResult)) / (HighResult - LowResult) NowResult = Forward(NowVar, MoreArguments) If NowResult ValueToBeFound Then HighVar = NowVar HighResult = NowResult Else LowVar = NowVar LowResult = NowResult End If If Abs(NowResult - ValueToBeFound) < MaxDiff Then NotReadyYet = False Wend Backward = NowVar End Function Function Forward(a As Double, b As Double) As Double ' This is just an example function; ' almost any continous function will work Forward = 3 * a ^ (1.5) + b End Function ' ================================================== ================= "Carim" wrote in message oups.com... Hi Chris, Why bother when IRR() does the job ? Cheers Carim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iterative solving using VBA ...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iterative Equation | Excel Discussion (Misc queries) | |||
an iterative calculation -- sort of | Excel Worksheet Functions | |||
Excel Iterative calculations | Excel Worksheet Functions | |||
Iterative process, | Excel Discussion (Misc queries) | |||
Iterative Macro | Excel Programming |