FIX: Ref to NPV / IRR / MIRR Gives Undefined Functions Error (101245)

MORE INFORMATION

The following example shows how to use the NPV function. It is based on the example shown in the Visual Basic Help menu, but it also includes the declarations for the NPV, IRR, and MIRR financial functions. Without the declarations for these functions, the example will fail, giving a "Reference to undefined Function or Array" error.

Steps to Work Around the Problem

1. Start Visual Basic or from the File menu, choose New Project (ALT, F, N) if Visual Basic is already running. Form1 is created by default.
2. Add the following code to the General section of Form1:

      ' Enter each Declare statement on one, single line:
      Declare Function MIRRC Lib "MSAFINx.DLL" (values#, ByVal cvalues%,
         ByVal finance#, ByVal reinvest#) As Double
      Declare Function NPVC Lib "MSAFINx.DLL" (ByVal Rate1#, values#,
         ByVal cvalues%) As Double
      Declare Function IRRC Lib "MSAFINx.DLL" (values#, ByVal cvalues%,
         ByVal Guess#) As Double
   
      Function IRR (values() As Double, ByVal Guess As Double) As Double
   
         On Error GoTo IrrErr
         iArgMin% = LBound(values)
         cArg% = UBound(values) - iArgMin%
         IRR = IRRC#(values(iArgMin%), cArg%, Guess)
         Exit Function
   IrrErr:
         MsgBox (Str$(Err))
         Exit Function
   
      End Function
   
      ' Enter the following Function statement on one, single line:
      Function MIRR (values() As Double, ByVal finance As Double,
         ByVal reinvest As Double) As Double
   
         On Error GoTo MirrErr
         iArgMin% = LBound(values)
         cArg% = UBound(values) - iArgMin%
         MIRR = MIRRC#(values(iArgMin%), cArg%, finance, reinvest)
         Exit Function
   MirrErr:
         MsgBox (Str$(Err))
         Exit Function
   
      End Function
   
      Function NPV (ByVal Rate1 As Double, values() As Double) As Double
   
         On Error GoTo NpvErr
         iArgMin% = LBound(values)
         cArg% = UBound(values) - iArgMin%
         NPV = NPVC#(Rate1, values(iArgMin%), cArg%)
         Exit Function
   NpvErr:
         MsgBox (Str$(Err))
         Exit Function
   
      End Function
   						

3. Add the following code to your program in the Form_Click event:

      Sub Form_Click ()
         Static Values(5) As Double  ' Set up array.
         Fmt = "###,##0.00"  ' Define money format.
         Guess = .1  ' Guess starts at 10%.
         RetRate = .0625 ' Set fixed internal rate.
         Values(0) = -70000  ' Business start-up costs.
         ' Positive cash flows reflecting income for four successive years.
         Values(1) = 22000: Values(2) = 25000
         Values(3) = 28000: Values(4) = 31000
         NetPVal = NPV(RetRate, Values())    ' Calculate net present value.
         Msg = "The net present value of these cash flows is "
         Msg = Msg & Format(NetPVal, Fmt) & "."
         MsgBox Msg  ' Display net present value.
      End Sub
   						

4. From the Run menu, choose start (ALT, R, S) or press the F5 key to run the program. You will see a message box that contains the correct Net Present Value result of 19,312.57.