Example of NPV and IRR Financial Functions in VB for Windows

Last reviewed: June 21, 1995
Article ID: Q110888
The information in this article applies to:

- Professional Edition of Microsoft Visual Basic programming system

  for Windows, version 3.0

SUMMARY

This article explains the NPV and IRR financial functions and gives a sample program.

MORE INFORMATION

The NPV, IRR, and MIRR functions are used for investments that are a series of nonconstant cash payments made at equal intervals. You pass the series of nonconstant payments in an array.

The nonconstant-payment functions (NPV, IRR, and MIRR) are in a different category than the financial functions for annuity investments (FV, IPmt, Rate, NPer, PV, Pmt, and PPmt). In an annuity, each cash payment is the same constant amount, made at equal intervals.

The present value (PV) of a future cash receipt is the amount of money that, if received today, would be considered equivalent to the future receipt, at a given interest rate. The present value is less than the future receipt because you can earn interest on money received today. NPV (Net Present Value) compares (subtracts) the current value of a series of future cash flows with an amount invested today.

NPV is useful to compare investment opportunities at a given discount (interest) rate. The discount rate (found with the Rate function) can be viewed as the rate of return you want out of your investment. If NPV is greater than or equal to 0, the investment equals or exceeds your interest (discount) rate requirement; if NPV is less than 0, the investment does not meet your interest rate requirement.

The IRR function returns Internal Rate of Return. IRR returns the discount rate at which NPV would return 0 (zero). For a given array of cash flow values, IRR can be thought of as an average interest rate (which compounds at each period). If IRR is lower than the interest rate you desire for this investment, then it is not a good investment.

The first element of the input cash-flow array should usually be negative, indicating your initial investment. A high (positive) income early in the value array will make IRR higher than if the same high income instead occurred later in the array. This is an example of the time value of money.

Please refer to an Accounting textbook for more information about these standard Accounting functions.

Step-by-Step Example with Code

  1. Start a new project in Visual Basic. Form1 is created by default.

  2. Add the following code to the General Declarations section of Form1:

    DefDbl A-Z

       ' Enter each Declare statement on one, single line:
       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 "Error " & (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 "Error " & (Str$(Err))
          Exit Function
       End Function
    
    

  3. Add the following code to the form Load event:

       Sub Form_Load ()
    
          form1.Show  ' Must Show form in Load event for Print to work.
          ' Array holds cash flow values, one value per period (such as year):
          ReDim valuearray(5) As Double
          guess = .05        ' Guess the IRR (use .1 if in doubt).
    
          valuearray(0) = -70000 ' 0. First value negative initial investment
          valuearray(1) = 22000  ' 1. Return on investment after 1 period.
          valuearray(2) = 25000  ' 2. (Pos value is return on investment.)
          valuearray(3) = 28000  ' 3. (Neg value is additional investment.)
          valuearray(4) = 31000  ' 4. Return on investment after 4 periods.
          ' For the above values, IRR returns 17.7% return per period
    
          irreturn = IRR(valuearray(), guess)
    
          discountrate = 0  ' If discountrate = 0,
                            ' NPV returns sum of valuearray().
          netpresval = NPV(discountrate, valuearray())
          ' Notes for NPV() function:
          ' If discountrate = value returned by IRR(), then NPV returns zero.
          ' If discountrate = zero, NPV returns sum of values in valuearray().
          ' If discountrate > zero, NPV returns an amount smaller than sum of
          ' values in valuearray() due to the discount effect at each period.
          ' If discountrate < zero, NPV returns an amount larger than the sum
          ' of the values in valuearray().
    
          Print "IRR (fractional return on investment per period) = "; irreturn
          Print "NPV = "; Format$(netpresval, "Standard")
    
       End Sub
    
    

  4. Press the F5 key to run the program.

For the above values, IRR returns .177 (17.7% return per period).

NOTE: By definition, IRR returns the discount rate at which NPV returns 0.

NPV, IRR, MIRR: Reference to Undefined Function or Array

For more information, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q101245
   TITLE     : BUG: Ref to NPV / IRR / MIRR Gives Undefined Functions Error

If you try to run an application that contains a reference to the NPV, IRR, or MIRR financial function, Visual Basic for Windows generates this error:

   Reference to undefined Function or Array

Visual Basic does not recognized these as Visual Basic functions because they were incorrectly referenced in the financial DLL file (MSAFINX.DLL) that ships with Visual Basic version 3.0.

To work around the problem, you can declare the NPVC, IRRC, and MIRRC functions located in MSAFINX.DLL and alias them as NPV, IRR, and MIRR respectively, as shown farther above.


Additional reference words: 3.00
KBCategory: kbprg kbcode
KBSubcategory: PrgOther


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 21, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.