Using NPV (Net PresentValue in Access)

[smck]

I am tring to use the NPV function without success. How can one generate NPV
in Access without VBA coding?

Thanks

 

[John W. Vinson]

How can one generate NPV
in Access without VBA coding?

You can't. It's not included in the builtin functions.

You can set a reference to the Excel functions, but that's a bit more
difficult than doing VBA coding.

John W. Vinson [MVP]

 

[smck]

Hi John,
Thanks for your quick response. Is there a link I can access that would give
me more information?

I notice that in doing a Query by Design, in the Expression Builder,
Built-In Functions there is a reverence to NPV.

The argument is NPV(rate,ValueArray). How would one use that option? or,
does it still lead back to VBA Coding or Referencing Excel?

 

[John W. Vinson]

Hi John,
Thanks for your quick response. Is there a link I can access that would give
me more information?

I notice that in doing a Query by Design, in the Expression Builder,
Built-In Functions there is a reverence to NPV.

The argument is NPV(rate,ValueArray). How would one use that option? or,
does it still lead back to VBA Coding or Referencing Excel?

The Help is infuriatingly misleading - it contains references to functions
which exist in Excel and Word but are in fact not directly available in
Access. I haven't actually worked with Excel functions myself; it may be
easier than my sour assessment last night. Try typing Ctrl-G to open the VBA
editor; on the menu select Tools... References; scroll down the list and check
the reference for Excel functions. Then see if NPV will work in your query.

John W. Vinson [MVP]

 

[James A. Fortune]

Hi John,
Thanks for your quick response. Is there a link I can access that would give
me more information?

I notice that in doing a Query by Design, in the Expression Builder,
Built-In Functions there is a reverence to NPV.

The argument is NPV(rate,ValueArray). How would one use that option? or,
does it still lead back to VBA Coding or Referencing Excel?

I can think of no way to use it in a query without some VBA coding or
referencing Excel. Access HAS a built-in NPV function, but Access
queries don't recognize it. Access' built-in functions can be called
from queries by placing them in a public function in a module. There
was another hurdle with the double array required by NPV so I had to get
creative.

'-----Begin Module Code-----
Public Function MyNPV(dblRate As Double, strCashFlow As String) As Double
Dim dblCashFlow() As Double
Dim varArray As Variant
Dim I As Integer
Dim UB As Integer
Dim arr() As String

varArray = Split(arr(), strCashFlow, ",")
UB = UBound(arr()) + 1
ReDim dblCashFlow(UB)
For I = 1 To UB
dblCashFlow(I) = CDbl(arr(I - 1))
Next I
MyNPV = NPV(dblRate, dblCashFlow())
End Function
-----End Module Code-----

I was using A97, which doesn't have a Split function. I used the
following Split function (I tried to find the name of the author, but
Google Search can't locate the post anymore. It is not the same as the
Split function in later versions of Access so a very slight modification
to the calling code is in order.):

-----Begin More Module Code-----
Public Function Split(arr() As String, strFld As String, strDelimiter As
String) As Variant

On Error GoTo Err_Split

Dim j As Integer, iPos As Integer
Dim iTmp As Integer
j = 0
ReDim arr(j)

iPos = InStr(strFld, strDelimiter)
If (iPos = 0 Or IsNull(iPos) = True) Then
arr(j) = Trim(strFld)
Else
arr(j) = Trim(Left(strFld, iPos - 1))
Do Until (iPos = 0 Or IsNull(iPos) = True)
j = j + 1
ReDim Preserve arr(j)
iTmp = InStr(iPos + 1, strFld, strDelimiter)
If (iTmp = 0 Or IsNull(iTmp) = True) Then
arr(j) = Trim(Mid(strFld, iPos + 1))
iPos = 0
Else
arr(j) = Trim(Mid(strFld, iPos + 1, iTmp - iPos - 1))
iPos = iTmp
End If
Loop
End If
Exit_split:
Exit Function
Err_Split:
MsgBox "Error: " & Err.Number & " " & Err.Description, vbCritical,
"Function: split()"
Resume Exit_split
End Function
-----End More Module Code-----

Example:

MyTable is a table with one record.

qryNPV:
SELECT MyNPV(0.06, "-5, 1, 2, 3") As NPV FROM MyTable;

!qryNPV:
NPV
0.215598928035314

I didn't test to see if the value is correct. Perhaps the loop needs to
start at 0 instead of 1 and go up to UBound(arr()). Unlike the Excel
version, the cash flow values aren't limited to 29 values. I'm not sure
that the calling convention I used helps in a practical way. Maybe I'll
test it and fix it up later if necessary.

James A. Fortune
(e-mail address removed)

 

[John W. Vinson]

There
was another hurdle with the double array required by NPV so I had to get
creative.

Thanks so much, James! I have no taste for accounting and wouldn't have known
where to start.

John W. Vinson [MVP]

 

[James A. Fortune]

Thanks so much, James! I have no taste for accounting and wouldn't have known
where to start.

John W. Vinson [MVP]

John,

I'm glad to help. With a name like Fortune I'm naturally drawn to
accounting functions . After thinking about this problem further I'm
leaning toward using a table or a query to retrieve the intervals and
the payments. Even better might be to allow the payments to post on
arbitrary dates stored in a field in the record and use DateDiff to get
the elapsed time from the starting time. Either method would force me
to use the definition of PV in the query SQL itself instead of using the
NPV function. Each payment would make its own contribution to the NPV
that SUM can aggregate. That seems a lot more useful. I'll try to post
something further by Monday night.

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Either method would force me
to use the definition of PV in the query SQL itself instead of using the
NPV function. Each payment would make its own contribution to the NPV
that SUM can aggregate. That seems a lot more useful. I'll try to post
something further by Monday night.

From:

Engineering Economy, Sixth Edition
DeGarmo, Ernest Paul
Macmillan Publishing Co., Inc. 1979
ISBN 0-02-328160-X

p. 99:

To find the present value, P, given a future value, F, the
multiplication factor (to multiply times F) is:

e^[-rN] continuous compounding

(1 + r) ^ -N discrete compounding (p. 73)

where:

e = Exp(1)

r = discrete/continuous interest rate

N = number of periods

Note: That matches the formula given in the Excel 97 help file for NPV.

tblCashPayments
CPID AutoNumber
PaymentAmount Double, Format: Currency
PaymentPeriod
CPID PaymentAmount PaymentPeriod
1 ($10,000.00) 0
2 $300.00 1
3 $325.00 2
4 $333.00 3
5 $200.00 4
6 $800.00 5

Continuous compounding:

qryCalcNPV:
SELECT 0.09 AS r, Sum(PaymentAmount * Exp(-r * PaymentPeriod)) AS NPV
FROM tblCashPayments;

!qryCalcNPV:
r NPV
0.09 -8550.51466758417

Discrete compounding:

qryCalcNPV:
SELECT 0.09 AS r, Sum(PaymentAmount / ((1 + r) ^ [PaymentPeriod])) AS
NPV FROM tblCashPayments;

!qryCalcNPV:
r NPV
0.09 -8532.45739427815

To see if at least one negative value exists in the table:

SELECT IIf(Sum(Abs([PaymentAmount] < 0)) > 0, "It's True", "It's False")
AS NegativeExists FROM tblCashPayments;

To see if exactly one negative value exists in the table:

SELECT IIf(Sum(Abs([PaymentAmount] < 0)) = 1, "It's True", "It's False")
AS ExactlyOneNegativeExists FROM tblCashPayments;

Use Nz(PaymentAmount, 0) instead of PaymentAmount if some records are
allowed to contain Null PaymentAmount's.

James A. Fortune
(e-mail address removed)

 

[Jamie Collins]

To see if at least one negative value exists in the table:

SELECT IIf(Sum(Abs([PaymentAmount] < 0)) > 0, "It's True", "It's False")
AS NegativeExists FROM tblCashPayments;

For interest:

SELECT MIN(ABS(PaymentAmount)) <> MIN(PaymentAmount)
AS NegativeExists
FROM tblCashPayments;

Jamie.

--

 

[James A. Fortune]

To see if at least one negative value exists in the table:

SELECT IIf(Sum(Abs([PaymentAmount] < 0)) > 0, "It's True", "It's False")
AS NegativeExists FROM tblCashPayments;

For interest:

SELECT MIN(ABS(PaymentAmount)) <> MIN(PaymentAmount)
AS NegativeExists
FROM tblCashPayments;

Jamie.

--

What if the minimum PaymentAmount value in the table is 0 (Note: this
situation shouldn't be allowed anyway)?

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

To see if at least one negative value exists in the table:

SELECT IIf(Sum(Abs([PaymentAmount] < 0)) > 0, "It's True", "It's False")
AS NegativeExists FROM tblCashPayments;

For interest:

SELECT MIN(ABS(PaymentAmount)) <> MIN(PaymentAmount)
AS NegativeExists
FROM tblCashPayments;

Jamie.

--

What if the minimum PaymentAmount value in the table is 0 (Note: this
situation shouldn't be allowed anyway)?

Nevermind.

James A. Fortune
(e-mail address removed)