XNPV(rate, values, dates)

Returns the net present value of 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.

* 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

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

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