Formulas and Functions Help
- Welcome
-
- ACCRINT
- ACCRINTM
- BONDDURATION
- BONDMDURATION
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNUM
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENCYCODE
- CURRENCYCONVERT
- CURRENCYH
- DB
- DDB
- DISC
- EFFECT
- FV
- INTRATE
- IPMT
- IRR
- ISPMT
- MIRR
- NOMINAL
- NPER
- NPV
- PMT
- PPMT
- PRICE
- PRICEDISC
- PRICEMAT
- PV
- RATE
- RECEIVED
- SLN
- STOCK
- STOCKH
- SYD
- VDB
- XIRR
- XNPV
- YIELD
- YIELDDISC
- YIELDMAT
-
- AVEDEV
- AVERAGE
- AVERAGEA
- AVERAGEIF
- AVERAGEIFS
- BETADIST
- BETAINV
- BINOMDIST
- CHIDIST
- CHIINV
- CHITEST
- CONFIDENCE
- CORREL
- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS
- COVAR
- CRITBINOM
- DEVSQ
- EXPONDIST
- FDIST
- FINV
- FORECAST
- FREQUENCY
- GAMMADIST
- GAMMAINV
- GAMMALN
- GEOMEAN
- HARMEAN
- INTERCEPT
- LARGE
- LINEST
- LOGINV
- LOGNORMDIST
- MAX
- MAXA
- MAXIFS
- MEDIAN
- MIN
- MINA
- MINIFS
- MODE
- NEGBINOMDIST
- NORMDIST
- NORMINV
- NORMSDIST
- NORMSINV
- PERCENTILE
- PERCENTRANK
- PERMUT
- POISSON
- PROB
- QUARTILE
- RANK
- SLOPE
- SMALL
- STANDARDIZE
- STDEV
- STDEVA
- STDEVP
- STDEVPA
- TDIST
- TINV
- TTEST
- VAR
- VARA
- VARP
- VARPA
- WEIBULL
- ZTEST
- Copyright
XNPV
The XNPV function returns the present value of an investment or annuity based on a series of irregularly spaced cash flows and at a discount interest rate.
XNPV(discount, payments, dates)
discount: The discount rate to apply to the cash flows.
payments: A range of cells that contain the payments for the investment or annuity. Payments must contain at least one positive payment and one negative payment, and if the first value is a payment, it must be negative.
dates: A range of cells that contain dates that correspond to the cash flows in payments. The first date corresponds with the beginning of the payments; all other dates must come after this date, otherwise an error occurs.
Notes
If any values in discount, payments, or dates don’t contain numeric values, XNPV returns an error.
If the numbers used in dates don’t correspond with a valid date, XNPV returns an error.
If payments and dates don’t contain the same number of values, XNPV returns an error.
The currency shown in this function result depends on your Language & Region settings (in System Preferences in macOS and in Settings in iOS and iPadOS), or on your Time Zone & Region settings in iCloud Settings.
Example |
|---|
Given the following table: |
Cash flow | Date | |
|---|---|---|
A | B | |
1 | -7,000 | 01/01/2019 |
2 | 3,000 | 03/02/2019 |
3 | 2,500 | 05/17/2019 |
4 | 1,000 | 08/26/2019 |
5 | 2,000 | 10/27/2019 |
=XNPV(.08,A1:A5,B1:B5) returns approximately $1220.67. |