The XNPV function calculates the net present value of an investment for irregular cash flows.
This function is used to calculate the net present value of an investment project with uneven intervals between cash flows.
Syntax
XNPV(discount, cashflow_amounts, dates)
Argument |
Description |
Permitted values |
|---|---|---|
discount |
Discount rate for one period |
Decimal number (for example, 0.1 for 10%) or a reference to a cell containing a number |
cashflow_amounts |
Series of cash flows corresponding to dates |
Range of cells or array of numbers |
dates |
Cash flow dates |
Range of cells or array of dates |
Examples of use
Calculation of XNPV for an investment project with irregular cash flows
=ЧИСТНЗ(10%; {-100000; 20000; 50000; 90000}; {"01.01.2023"; "15.03.2023"; "10.07.2023"; "31.12.2023"})
This formula calculates the net present value for a project with an initial investment of 100,000 rubles on January 1, 2023, and subsequent income:
[20,000 rubles on March 15, 2023], [50,000 rubles on July 10, 2023], and [90,000 rubles on December 31, 2023] at a discount rate of 10% per annum.
Use with reference to cells
=XNPV(B1, C3:C7, D3:D7)
Where cell B1 contains the discount rate, C3:C7 contains the cash flow range, and D3:D7 contains the corresponding date range.
Notes
–The first cash flow is usually a negative number (initial investment).
–The number of values in the cash_flows and dates arguments must match.
–A positive result indicates the potential profitability of the project.
–If there are errors in the date format, the function will return the #VALUE! error.