Excel 2000 in a Nutshell

Name

XNPV

Synopsis

You can use XNPV (an Analysis ToolPak function) to determine the present value for series of cash flows that are not periodic. If you have a series of cash flows that are periodic (occur on a regular schedule, such as monthly) you should use NPV.

To Calculate

=XNPV(Rate, Values, Dates)

All of the arguments are required for this function.

Rate

Specifies a decimal value that indicates the discount rate that is applied to cash flows.

Values

Contains a series of numbers that specify the cash flows that correspond to the dates specified for the Dates argument. If desired, you can specify the first payment, typically the cost of the investment, as the first value in the series. For example, if you invested $4,000 you could specify an initial value of -4000, which represents the initial cost. All remaining cash flow amounts would by positive to show interest earned on the investment.

The series of values must be enclosed in brackets ({}) and the values must be separated with commas.

Dates

Contains a series of dates that correspond to the cash flow values specified for the Values argument. Keep in mind that if you specify the actual dates, and not cell references, you must enclose the dates within quotes. The series of values must be enclosed in brackets ({}) and the values must be separated with commas.

Example

Figure 12-27 illustrates how XNPV can be used to determine the value of a series of cash flows that do not occur at regular intervals.

Figure 12-27. Use XNPV with ...