Formulas > =XNPV()

How To Use XNPV() Function in Google Sheets


Calculates the net present value of an investment based on a specified series of potentially irregularly spaced cash flows and a discount rate.

Common Questions about the XNPV Formula
- What is XNPV?
- What elements are needed to use the XNPV formula in Google Sheets?
- How do you calculate XNPV?

How can the XNPV Formula be used appropriately?
- The XNPV formula should be used to calculate the net present value of an investment, factoring in a given discount rate and the length of the investment’s life.
- Each cash flow should be entered with the corresponding date in the following format: [date], [cash flow]
- If a cash flow occurs on a non-business day, the formula should be adjusted so that the cash flow is entered on the preceding business day.

How can the XNPV Formula be commonly mistyped?
- It is common to mistake the order of the two arguments within the XNPV formula. The first argument should always be the discount rate, and the second argument should always be the two-column range, with the first column date and the second column cash flows.
- This formula can also be mistyped as XPV, XPN, XPNV, XNVP.

What are some common ways the XNPV Formula is used inappropriately?
- Entering the cash flow values incorrectly, such as entering a positive cash flow as a negative or vice versa
- Entering cash flows on the wrong dates
- Using a discount rate that does not accurately reflect the investment’s risk

What are some common pitfalls when using the XNPV Formula?
- Forgetting to adjust for non-business days
- Ignoring the fact that the discount rate must reflect the investment’s risk over the life of the investment
- Entering cash flows with the wrong dates

What are common mistakes when using the XNPV Formula?
- Entering invalid arguments, i.e. negative numbers, non-numeric characters, or omitting arguments
- Using the wrong order of arguments, as the discount rate should always come first
- Forgetting to subtract the initial investment from the XNPV calculation

What are common misconceptions people might have with the XNPV Formula?
- Misunderstanding the difference between XNPV and NPV, as the XNPV formula takes into account the timing of the cash flows 
- Misinterpreting the labeling of the second argument in the XNPV Formula as a single column, when it is really a two-column range consisting of both dates and cash flows 
- Not understanding that the discount rate must reflect the risk of the investment over its life period.

How To Actually Use XNPV() in Sheets

XNPV(discount, cashflow_amounts, cashflow_dates)

Looking for a video tutorial? Members can email me anytime! Check out the blogs below for more info on this formula. Or generate your formula. Find more formulas here at

Learn more about the XNPV() formula:

Generate a XNPV() formula for your needs with AI

Google Sheets Formula Generator

Whatever you need to do in sheets, you can generate a formula. Use the Better Sheets Formula generator to create a formula for any need. Completely free for members.


Looking for more help inside sheets get the free Add-on: Asa. Ask Sheets Anything. Go ahead, ask it any problem you migth have. Bring your own APIKEY and generate formulas inside of Google Sheets.