Arrow left
Back to guides
Google Sheets Formulas

XNPV Function in Google Sheets: Explained

In this article, you will learn how to use the XNPV formula in Google Sheets.

What is the XNPV function in Google Sheets?

The XNPV function in Google Sheets calculates the net present value (NPV) of a series of cash flows, given a specific interest rate. This function is similar to the NPV function but allows you to specify a different date for the start of the investment period.

How to use the XNPV formula in Google Sheets

The syntax for the XNPV function is as follows:

=XNPV(rate, values, dates)

rate is the interest rate per period.

values is a range of cells that contain the cash flows for each period.

dates is a range of cells that contain the dates for each cash flow.

The cash flows in the values range must be in the same order as the dates in the dates range. The rate and values arguments must be the same length.

For example, if you wanted to calculate the NPV of a series of cash flows starting on January 1, 2022, with an interest rate of 5%, you could use the following formula:

=XNPV(5%, B1:B5, A1:A5)

This would calculate the NPV of the cash flows in cells B1:B5, using the corresponding dates in cells A1:A5, and a 5% interest rate.

How to use the XNPV function in Google Sheets with an example

What is the difference between NPV and XNPV in Google Sheets?

The NPV and XNPV functions in Google Sheets are similar, but there is one key difference between them: the NPV function assumes that the first cash flow occurs at the beginning of the investment period, while the XNPV function allows you to specify a different date for the start of the investment period.

Learn how to do this step-by-step in the video below 👇

Automate financial reporting with LiveFlow

Want to eliminate manual updates of your Excel & Google Sheets models?

Yes, show me how

Need help?

Our team is here to help you any time between 9am and 10pm EST.
Check Icon
Email us at: help@liveflow.io

Liked this article? Then you'll love the ones below