Description
XNPV returns a number specifying the net present value of an investment based on a series of cash flows (payments and receipts) and a discount rate.
Arguments
rate is a number specifying the annual discount rate.
values is a number or currency type array, that specifies cash flow values. The array must contain at least one negative value (a payment) and one positive value (a receipt). These transactions occur at the date specified in dates.
dates is a
Date or
DateTime type array specifying the dates of the cash flows specified in the values argument. The first date represents the start of the schedule. The dates may occur in any order as long as they are after the first date.
Action
XNPV returns a number specifying the net present value of an investment based on a series of cash flows (payments and receipts) and a discount rate.
Examples
Suppose on April 1, 1999, someone was offered $1000 immediately, $2500 on July 1, 1999, $2500 on October 1, 1999 and $5000 on December 31, 1999. If the discount rate (the time value of money) is 4.5 percent, the value of the offer is:
XNPV(0.045,[1000,2500,2500,5000], [DateValue(1999,4,1),DateValue(1999,7,31),DateValue(1999,10,1), DateValue(1999,12,31)])
Returns $10,746.70, which is less than the sum of the payments since the person has to wait for the money.
Comments
This function is designed to work like the Excel function of the same name.
The XNPV and XIRR functions are related since using the rate of return, calculated by XIRR, in the XNPV function, results in zero being returned by XNPV. In other words, the internal rate of return is the interest for which the net present value is zero.