XNPV

XNPV(rate, values, dates)

Returns the net present value of a series of unequal cash flows at irregular intervals.

rate	The discount rate to apply to the cash flows.
values	A series of cash flows.
dates	A series of payment dates that correspond to the cash flows.

REMARKS

* This function assumes all payments are at the end of each period.
* A negative number represents any cash you pay out.
* A positive number represents any cash you receive (start with or end with).
* The "rate" must be a positive number and can be thought of as the effective rate ?
* All succeeding payments are discounted based on a 365-day year.
* The "values" series must contain at least one positive value and one negative value.
* If "values" and "dates" contain a different number of values, then #NUM! is returned.
* The "dates" must all be later than the first date, but they may occur in any order.
* The "dates" series are truncated to integers.
* If "dates" contains any invalid date strings, then #NUM! is returned.
* If "dates" contain any dates before the first date, then #NUM! is returned.
* If any argument is not numeric, then #VALUE! is returned.
* You can use the PV function to return the present value of a series of equal cash flows at regular intervals.
* You can use the NPV function to return the net present value of a series of unequal cash flows at regular intervals.
* You can use the XIRR function to return the interest rate for a series of unequal cash flows at irregular intervals (implicit reinvestment rate).
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C	D
1	=XNPV(10%, {0, 0, 0, 0, 10000}, {3000, 3365, 3730, 4095, 4460}) = 6830.13	-1000	1-Jan-00	=C1 = 36526
2	=XNPV(10%, {0, 0, 0, 10000}, {36526, 36892, 37257, 37622}) = 7511.19	1000	1-Jan-01	=C2 = 36892
3	=XNPV(10%, {0, 0, 0, 10000}, {3000, 3365, 3730, 4095}) = 7513.15	-2000	1-Jan-02	=C3 = 37257
4	=XNPV(0.1, {-1000, 1000}, C1:C2) = -91.15		1-Jan-03	=C4 = 37622
5	=XNPV(0.1, {-1000, 1000}, {39448, 39629}) = -46.16
6	=XNPV(0.1, B1:B2, C1:C2) = -91.15
7	=XNPV(0.1, {1000, -1000}, {39448, 39629}) = 46.16
8	=XNPV(0.1, B1:B3, C1:C3) = -1743.61
9	=XNPV(0.1, {-1000, 1000, -2000}, {39448, 39629, 39814}) = -1863.87
10	=XNPV(0.1, {-1000, 1000, -2000}, {39448, 39629, 39449}) = -2045.64
11	=XNPV(0.1, {-1000, 1000, -2000}, {35796, 35855, 36098}) = -1863.63
12	=XNPV(10, {-10000, 2750, 4250, 3250}, {35796, 35855, 36098, 36206}) = -7329.35
13	=XNPV(0.1, {-10000, 1000}, C1:C3) = #NUM!
14	=XNPV(-10, {-10000, 2750, 4250, 3250}, {35796, 35855, 36098, 36206}) = #NUM!
15	=XNPV(0.1, {-1000, 1000}, {"1 Jan 2008", "30 Jun 2008"}) = #VALUE!

1 - What is the present value of receiving £10,000 in 4 years time if the discount rate is 10% (compounded annually) with payments at the end of each period.
10 - The number of dates is not the same as the number of cash flows
11 - The rate is a negative number
12 - The dates are not entered as serial numbers