Syntax

Npv(Rate,ValueArray())

Description

Returns the net present value of an annuity based on periodic payments and receipts, and a discount rate.

Comments

The Npv function requires the following parameters:

Parameter

Description

Rate

Double that represents the interest rate over the length of the period. If the values are monthly, annual rates must be divided by 12 to normalize them to monthly rates.

ValueArray()

Array of Double numbers representing the payments and receipts. Positive values are payments, and negative values are receipts.

There must be at least one positive and one negative value.

Positive numbers represent cash received, whereas negative numbers represent cash paid out.

For accurate results, be sure to enter your payments and receipts in the correct order because Npv uses the order of the array values to interpret the order of the payments and receipts.

If your first cash flow occurs at the beginning of the first period, that value must be added to the return value of the Npv function. It should not be included in the array of cash flows.

Npv differs from the Pv function in that the payments are due at the end of the period and the cash flows are variable. Pv's cash flows are constant, and payment may be made at either the beginning or end of the period.

Example

This example illustrates the purchase of a lemonade stand for $800 financed with money borrowed at 10%. The returns are estimated to accelerate as the stand gains popularity. The incomes are estimated (generated) over 12 months. This program first generates the income stream array in two For...Next loops, and then the net present value (Npv) is calculated and displayed. Note normalization of the annual 10% rate.

Const crlf = Chr$(13) + Chr$(10)

Sub Main()
  Dim valu#(12)
  valu(1) = -800                       'Initial investment
  msg1 = valu(1) & ", "
  For x = 2 To 5                       'Months 2-5
    valu(x) = 100 + (x * 2)
    msg1 = msg1 1& valu(x) & ", "
  Next x
  For x = 6 To 12                       'Months 6-12
    valu(x) = 100 + (x * 10)            'Accelerated income
    msg1 = msg1 & valu(x) & ", "
  Next x
  NetVal# = NPV((.10/12),valu)
  msg1 = "The values:" & crlf & msg1 & crlf & crlf
  MsgBox msg1 & "Net present value: " & Format(NetVal#,"Currency")
End Sub

See Also

Fv (function); IRR (function); MIRR (function); Pv (function).

N