Excel XNPV function
The XNPV function calculates the net present value (NPV) for a series of cash flows that may or may not be periodic.
XNPV(rate, values, dates)
- Rate (required): The discount rate to apply to the cash flows.
- Values(required): An array or a range of cells that represents the series of cash flows.
The series of values must contain at least one positive and one negative value:
-- The negative value: represents the cost or payment;
-- The positive value: represents the income.
- Dates (required): A series of dates that corresponds to the values.
- The date of the initial investment must be first in the array;
- All other dates must be later than the initial date, and can be in any order;
- Dates should be entered:
-- By using the DATE function;
-- As references to cells containing dates;
-- As results returned from other formulas.
1. In Excel, dates can be used in calculations as they are stored as sequential numbers. By default, 1/1/1900 is serial number 1, so 12/1/2021 is serial number 40877 because it is 40876 days after 1/1/1900;
2. Dates are truncated to integers if containing time;
3. The #NUM! error occurs when either of the following conditions is met:
-- The arguments “values” and “dates” arrays have different dimensions;
-- Any of the given “dates” precedes the starting date.
4. The #VALUE! error occurs when either of the following condition is met:
-- Any of the supplied dates is an invalid date;
-- Any of the supplied “rate”, “values” and “dates” is non-numeric.
5. The XNPV is calculated by the following equation:
-- Pi is the ith or last cash flow (payment);
-- di is the ith or last payment date;
-- d1 is the 0th payment date.
It returns a numeric value.
As shown in the screenshot below, there is a 1-year investment project, which is expected to generate the following cash flows based on a discount rate of 8%. To calculate the net present value using the XNPV function, you can do as follows.
1. Select a blank cell, copy or enter the formula below and press the Enter key to get the result.
Then you can change the cell format to currency if you need.
Note: If the function returns a positive number, the investment is feasible. Otherwise, the investment is infeasible.
Excel NPV function
The NPV function returns the net present value of an investment based on a discount or interest rate and a series of future cash flows: payments (negative values) and income (positive values).
The Best Office Productivity Tools
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
Please leave your comments in English