Investment Studio > Expressions > Functions > Financial > XNPV
float xnpv(float discount_rate, float array cash_flows, date array dates)
Returns the Net Present Value of an investment given a discount rate and a series of future cash flows which need not be periodic. For periodic cash flows, use the npv function.
discount_rate is the annual discount rate. All cash flows are discounted based on a 365-day year.
cash_flows contains the series of inflows (positive values) and outflows (negative values).
dates contains the dates for each flow listed in the corresponding position of the cash_flows array. The two arrays must have the same number of elements.
Dates may be listed in any order (as long as they match up with the corresponding cash flow), with one exception: no date may precede the date in the first position, which marks the beginning of the schedule of payments.
NPV is related to IRR (Internal Rate of Return): the IRR of an investment is the interest rate for which NPV vanishes: ideally, xnpv(xirr(cash_flows, dates), cash_flows, dates) = 0.
Example
On July 8, 2001, you invest USD 10 000 to start a new business with irregular returns of USD 1400 (October 15, 2001), 3000 (December 3, 2001), 2500 (March 24, 2002), and 5000 (May 15, 2002).
Assuming standard US date format settings and a 2% annual discount rate, the NPV of your investment is
=npv(2%, {-10000, 1400, 3000, 2500, 5000}, {"7/8/2001", "10/15/2001", "12/3/2001", "3/24/2002", "5/15/2002"})
» USD 1749.97.