Excel functions / Financial
XNPV function
Net present value of cash flows that fall on any dates, not evenly spaced ones.
Syntax
=XNPV(rate, values, dates)
Arguments
rate: The yearly discount rate.values: The cash flows: money out is negative.dates: The date of each cash flow. The first is the starting date.
When to use it
XNPV gives the net present value of cash flows that fall on any dates, discounting each by its date. NPV assumes equal periods; XNPV is the one to use when payments are uneven. The first cash flow is treated as today and not discounted.
Examples
Uneven cash flows at 9%
A B C D 1 Date Cash flow 2 2008-01-01 -10000 2086.65 3 2008-03-01 2750 4 2008-10-30 4250 5 2009-02-15 3250 6 2009-04-01 2750 In D2:
=XNPV(9%, B2:B6, A2:A6)→ 2086.65
Common mistakes
- Dates as text. XNPV needs real dates. Text dates give #VALUE!.
- Different counts. There must be one date for each cash flow.
XNPV in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: XNPV
- Danish: NETTO.NUTIDSVÆRDI
- Dutch: NHW2
- Finnish: NNA.JAKSOTON
- French: VAN.PAIEMENTS
- German: XKAPITALWERT
- Hungarian: XNJÉ
- Italian: VAN.X
- Norwegian: XNNV
- Polish: XNPV
- Portuguese (Brazil): XVPL
- Portuguese (Portugal): XVAL
- Russian: ЧИСТНЗ
- Spanish: VNA.NO.PER
- Swedish: XNUVÄRDE
- Turkish: ANBD