# Thread: Recursive equations in VBA. (Excel 2000)

1. ## 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.

2. ## 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
'.....

3. ## 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

4. ## 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.

5. ## 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

6. ## 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 user-defined 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.

7. ## 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

8. ## 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 Function

9. ## Re: Recursive equations in VBA. (Excel 2000)

<P ID="edit" class=small>(Edited by rory on 20-Jan-03 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!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•