Results 1 to 9 of 9

20030115, 23:24 #1
 Join Date
 Jan 2003
 Location
 Denver, Colorado, USA
 Posts
 7
 Thanks
 0
 Thanked 0 Times in 0 Posts
Recursive equations in VBA. (Excel 2000)
I have the NPV formula mathematically calculated...It's:
NPV = CF1/(1+r) + CF2/(1+r)^2 + CF3/(1+r)^3 + ... + CFN/(1+r)^N
Where CF1 is a cash flow in period 1 and CF2 is a cash flow in period 2, etc. r is rate and N is the last period.
If I'm given NPV and all the Cash flows in a UDF like:
Function CalcNPV (NPV as double, CashFlows() as double)
I'm trying to solve for r. Issues are the # of CashFlows can change every time. This is similar to a post on another message board.

20030116, 06:55 #2
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Recursive equations in VBA. (Excel 2000)
Where would the UDF be called from?
If from a worksheet formula, the answer is relatively simple:
Function CalcNPV (NPV as double, CashFlows as Range)
Dim oCell as range
For Each oCell in CashFLows
'........
Next
'.....Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030116, 11:28 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Recursive equations in VBA. (Excel 2000)
The "simplest" way to do this is to use GOAL SEEK. No VB required (but NOT live)
Create in a cell:
=NPV(interest,Value1, value2, etc)
The use Goal seek to set this cell = "NPV desired" by changing the Interest range.
Steve

20030116, 16:07 #4
 Join Date
 Jan 2003
 Location
 Denver, Colorado, USA
 Posts
 7
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Recursive equations in VBA. (Excel 2000)
I don't want to use Goal Seek, b/c I want it to be dynamic (as in a UDF). Also, I'm looking for the specific math to solve this issue. Once I have that, I'm pretty sure I can set it up in a UDF. I'm just having trouble converting the NPV formula to a way I can use it in VBA. Thanks.

20030116, 16:20 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Recursive equations in VBA. (Excel 2000)
It can NOT be solved directly. It can only be done by trial and error.
The UDF iteration can be done many ways:
You could start at an "r" of 0, calc the NPV can compare it to "target", then increment by 0.01 recalculate, compare, and continue until the target "=" NPV (Equal could be within a given tolerance)
Or to be a little quicker:
Start at r=0, and r= some high limit perhaps 0.5 or 1 calc NPV for each.
If the target is between, halve the distance and compare again, continually halving the distance in "r" until the calc NPV equals the target.
If the target is outside the range, calc a new r limit and continue the iteration.
HTH,
Steve

20030116, 16:26 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Recursive equations in VBA. (Excel 2000)
The equation you are trying to solve for r is a polynomial equation of degree N (= the number of cash flows). For N = 1, 2, 3 and 4, there are (increasingly complex) methods for finding the exact solutions.
For N greater than 4, there exists no method to solve the equation exactly; you must find an approximation by iteration. Although very clever methods have been designed to do this efficiently, it is much slower than just applying a formula; in certain circumstances iteration methods can get out of hand.
Even though it is possible to program this in a userdefined function, you might not like the results  it could slow down recalculation of your spreadsheet enormously.
If you really want to tackle the UDF, I suggest that you get a math book on numerical solutions, or do a search in Google (or whatever your favorite search engine is); there are plenty of (financial) math sites out there.
Note: GoalSeek is available in VBA as a method of the Range object. You can use this to automate the input to GoalSeek, so that the user doesn't have to enter all parameters manually.

20030116, 16:51 #7
 Join Date
 Jan 2003
 Location
 Denver, Colorado, USA
 Posts
 7
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Recursive equations in VBA. (Excel 2000)
Thanks for the math help. I wrote this function, but I must be getting the syntax run as it won't run. What am I missing?
Function ReverseNPV(myNPV As Double, Values() As Double)
Dim i As Long
Dim CompareNPV As Double
i = 0.001
Do While i < 1
CompareNPV = NPV(i, Values())
If Round(CompareNPV, 2) = Round(myNPV, 2) Then
ReverseNPV = i
Exit Function
End If
i = i + 0.001
Loop
ReverseNPV = i
End Function

20030116, 21:01 #8
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Recursive equations in VBA. (Excel 2000)
First, I'm out of my depth with your objective: Steve and Jan Karel among others know this stuff much better than I do. So you had better look very carefully at what I've done and verify that I didn't munge your calcs. Second, I'm certain parts of my code can be improved (e.g., there may be an easier way to get the Values array in), and there's very little error handling.
Anyhow, I don't see how you are getting you cash flow values array into the function, you can't do it the way you are trying to. Also, you declared i as Long and tried to use decimals; that won't work. Next, and I may be wrong, the VBA Round function doesn't appear to take negative rounding arguments the way the Worksheet Function does.
So, at your risk <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, here goes:
Function RateSolver(varNPV As Variant, rngValues As Range)
Dim dblNPV As Double, dblDompNPV As Double, dblD As Double
Dim dblValues() As Double
Dim lngC As Long
Application.Volatile
' handle input for NPV whether range or hardcode
If TypeName(varNPV) = "Range" Then
dblNPV = varNPV.Value
Else
dblNPV = varNPV
End If
' get Cash Flow into Values Array
ReDim dblValues(1 To rngValues.Count)
For lngC = 1 To rngValues.Count
dblValues(lngC) = rngValues.Cells(lngC, 1).Value
Next lngC
dblD = 0.00001
Do While dblD < 1
dblDompNPV = NPV(dblD, dblValues)
If Int(dblDompNPV) < Int(dblNPV) Then ' calc fell below target
RateSolver = dblD  0.00001 ' back it up by 0.00001
Exit Function
End If
dblD = dblD + 0.00001
Loop
RateSolver = dblD
End FunctionJohn ... I float in liquid gardens
UTC 7ąDS

20030120, 13:33 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,354
 Thanks
 4
 Thanked 229 Times in 210 Posts
Re: Recursive equations in VBA. (Excel 2000)
<P ID="edit" class=small>(Edited by rory on 20Jan03 13:33. )</P>John,
To get the range into an array, you can just use
<pre>Dim varValues as Variant
varValues = rngValues
</pre>
FWIW.
Later Edit:
John, Sorry I should have read your post, not just your PM! In this case you have to load the array the way you did originally since the NPV function requires a double array as its second argument and won't accept a variant array even if all the elements happen to be doubles!Regards,
Rory
Microsoft MVP  Excel