XNPV(rate, values, dates)

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

rateThe discount rate to apply to the cash flows.
valuesA series of cash flows.
datesA 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 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.
* In Excel 2007 the accuracy of this function was improved.
* In Excel 2003 this function was only available when you had the Analysis ToolPak add-in loaded.
* For the Microsoft documentation refer to support.office.com

 ABCD
1=XNPV(10%,{0,0,0,0,10000},{3000,3365,3730,4095,4460}) = 6830.13-1000.001-Jan-00=C1 = 36526
2=XNPV(10%,{0,0,0,10000},{36526,36892,37257,37622}) = 7511.191000.001-Jan-01=C2 = 36892
3=XNPV(10%,{0,0,0,10000},{3000,3365,3730,4095}) = 7513.15-2000.001-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

© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top