Microsoft KB Archive/268159

= ACC2000: Parameter Arrays Do Not Work with NPV Function =

Article ID: 268159

Article Last Modified on 12/12/2002

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q268159



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
The NPV (Net Present Value) function is only available in Visual Basic for Applications. However, you can write a custom function so that the NPV function is accessible from Access objects such as forms, reports, and queries. This article demonstrates how to do this.



MORE INFORMATION
Because the NPV function allows only arrays of type Double, you cannot implement it with a parameter array because parameter arrays must always be a variant data type. The solution is to first pass the parameters to the custom function, and then to feed them into a second array that is a double data type. The following steps demonstrate an example of this:  In a new Access database, create a new Visual Basic for Applications module.  In the module, type or paste the following function: Function myNpv(RetRate As Double, ParamArray arValues As Variant) Dim intI As Integer ' Use UBound function to determine upper limit ' of array and set arLocValues to same size. ReDim arLocValues(UBound(arValues)) As Double ' Cycle through and put arValues into ' the Double Type arLocValues For intI = 0 To UBound(arValues) arLocValues(intI) = arValues(intI) Next intI 'Find the NPV using the required Double type array. myNpv = NPV(RetRate, arLocValues) End Function  Save the module, and then close the Visual Basic Editor. Create a new form not based on any table or query named Test. Add a text box named Text0 to the Test form. In the property sheet for the text box, click the Data tab, and then set the ControlSource property as follows:

=myNPV(0.0625,-70000,22000,25000,28000,31000)

In this example, 0.0625 is the fixed internal rate of return, -70000 is the business start-up cost, and the rest are positive cash flows reflecting income for four successive years.

NOTE: You can enter whatever number of cash flows that you want; they will be fed into the array as needed.</li> In the property sheet, click the Format tab, and then set the Format property to Currency.</li> Close the property sheet, and then view the form in Form view. Note the Net Present Value of $19,312.57.</li></ol>

<div class="references_section">