XNPV
TheXNPV
function calculates the net present value of a series of cash flows that occur at irregular intervals. It discounts each cash flow by a discount rate that is supplied by the user. It is commonly used in financial analysis to determine the value of investments or to evaluate the profitability of projects.
 How to use
XNPV
formula?  Examples of using
XNPV
formula XNPV
formula not working? Similar formulas to
XNPV
Usage
Use the XNPV
formula with the syntax shown below, it has 3 required parameters:
 discount (required):
The discount rate to apply to the cash flows. It must be greater than 0.  cashflow_amounts (required):
An array or range containing the cash flows. Positive values represent cash inflows and negative values represent cash outflows. The first cash flow must occur at the beginning of the period.  cashflow_dates (required):
An array or range containing the dates of the cash flows. The dates must be in chronological order and must correspond to the cash flows in cashflow_amounts.
Examples
Here are a few example use cases that explain how to use theXNPV
formula in Google Sheets.
Calculate the net present value of an investment
Suppose you are considering an investment that requires an initial outlay of $10,000 and is expected to generate cash flows of $5,000, $8,000, and $12,000 in years 1, 2, and 3, respectively. If the discount rate is 10%, what is the net present value of the investment? Use the XNPV
function to calculate this value.
Evaluate the profitability of a project
Suppose you are evaluating a project that requires an initial investment of $100,000 and is expected to generate cash flows of $50,000, $70,000, $90,000, and $120,000 in years 1, 2, 3, and 4, respectively. If the required rate of return is 12%, what is the net present value of the project? Use the XNPV
function to calculate this value.
Calculate the present value of a lease agreement
Suppose you are leasing a property for 5 years and are required to make annual payments of $10,000 at the end of each year. If the discount rate is 8%, what is the present value of the lease agreement? Use the XNPV
function to calculate this value.
Common Mistakes
XNPV
not working? Here are some common mistakes people make when using the XNPV
Google Sheets Formula:
Incorrect data types
The XNPV formula requires the discount rate and cashflow amounts to be expressed as numbers, and the cashflow dates to be expressed as dates. Make sure your data is formatted correctly.
Missing or incorrect arguments
The XNPV formula requires three arguments: the discount rate, an array of cashflow amounts, and an array of cashflow dates. Make sure you have provided all three arguments and that they are in the correct order.
Incorrect order of cashflow amounts
The cashflow amounts must be listed in chronological order, with the earliest cashflow first and the latest cashflow last. If your cashflow amounts are not ordered correctly, XNPV will return an inaccurate result.
Missing or incomplete data
XNPV requires both cashflow amounts and cashflow dates to be provided. If you are missing data, or if there are gaps in your data, XNPV will return an inaccurate result.
Incorrect discount rate
Make sure you are using the correct discount rate for your analysis. If your discount rate is incorrect, XNPV will return an inaccurate result.
Related Formulas
The following functions are similar to XNPV
or are often used with it in a formula:

NPV
The
NPV
function calculates the net present value of a series of cash flows, discounted by a specified rate. It is commonly used to determine the present value of an investment, where the cash flows represent incoming and outgoing payments. The function takes a discount rate and one or more cash flow values as input. 
IRR
The
IRR
function is used to calculate the Internal Rate of Return for a series of cash flows. The IRR is the interest rate at which the Net Present Value of the cash flows equals zero. It is commonly used to evaluate potential investments or projects. 
XIRR
The
XIRR
function in Google Sheets calculates the internal rate of return (IRR) for a series of cash flows that occur at irregular intervals. It returns the annualized percentage rate earned by an investment, taking into account the dates on which each payment was made. The function is most commonly used in financial analysis to compare the returns on different investment opportunities. 
PV
The
PV
function in Google Sheets calculates the present value of a regular payment stream or a lump sum amount, based on a constant interest rate. It is commonly used in financial analysis to determine the value of investments or loans. This function returns a negative value, as it represents money flowing out from the user. 
FV
The
FV
function calculates the future value of an investment based on periodic constant payments and a constant interest rate. It takes into account the present value of the investment, the number of periods in which the payments are made, and the compounding frequency. This formula is commonly used in financial planning and investment analysis.
Learn More
You can learn more about the XNPV
Google Sheets function on Google Support.