XNPV

• 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:

=XNPV(discount, cashflow_amounts, cashflow_dates)

1. discount (required):
   The discount rate to apply to the cash flows. It must be greater than 0.
2. 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.
3. 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

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

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.