Results 1 to 9 of 9
  1. #1
    New Lounger
    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.

  2. #2
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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

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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 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. #7
    New Lounger
    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

  8. #8
    Uranium Lounger
    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 Function
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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!
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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