How to Use XNPV Function in Google Sheets

The XNPV function in Google Sheets is a useful tool to help determine the net present value of an investment, based on a series of irregularly spaced cash flows and a discount rate. When studying an investment, you should consider how you can benefit from it in the future against certain scenarios – the keyword here will be irregular cash flows. The XNPV function can help you do that.

The XNPV function is used to find the net present value of an investment. The net present value is a term in finance that refers to the difference between the current value of cash inflows and outflows. This tool is usually used when encountering investment due diligence and capital budgeting, in order to make good decisions on the value of a project versus other options.

Let’s look at an example.

Your company is deciding which project to pursue. You have the information about each project’s inflows and outflows, as well as the discount rate. You note that the cash flows are irregular, and thus you have a detailed schedule of cash flows on hand to study the investment.

How should you approach this problem?

The XNPV function needs you to set up the cash flows, as well as know your rates in order to study each project and make a sound financial decision. From there, you can compare the two results and decide which project is best for the company.

The Anatomy of the NPV Function in Google Sheets

The syntax of the XNPV function is as follows::

=XNPV(discount, cashflow_amounts, cashflow_dates)

Let’s have a look at each part of the function to understand what is going on here:

• = is the equals sign that starts off any function in Google Sheets.
• NPV is the name of our function.
• discount is the discount rate of an investment over one period.
• cashflow_amounts is the range of cells containing the income or payments of the investment.
• cashflow_dates is the range of cells containing the dates according to the detailed payments in cashflow_amounts

Note that XNPV is similar to PVand NPV, except that the dates of the cash flows matter in XNPV. It also allows variable-value cash flows. If the dates are regular, use NPV instead.

You should also note that the perspective you are solving the problem from also matters.

If you are the owner of the investment, the cashflow_amountswill represent income, so they should be positive.

If you are the perspective of someone making a loan repayment, the cashflow_amounts  will represent payments, so they should be negative.

When the net present value is zero, the internal rate of return under the same conditions is the discount rate.

A Real Example of Using the XNPV Function

Let’s look at the example below to see how to use XNPV function in Google Sheets.

Calculating the Net Present Value in Google Sheets

This is a simple problem. We want to find the net present for a certain project. Here in the example, the cash flow schedule is laid out, as well as the discount rate.

In this example, the function XNPV will take 3 arguments. So in the equation, it will look like:

=XNPV(G4,C3:C7,D3:D7)

Note that we included the initial payment of the investment in the range. As a result, we get $96.55.

This simple problem can be practiced to perfection. Use the link below to get a copy of this problem set:

How to Use the XNPV Function in Google Sheets

In this section, we will show you a step-by-step process on how to use the XNPV function in Google Sheets.

In this problem, we will be comparing two different projects. The information that you have is laid out over two different cases. They are all projects that are not easy to compare at first glance because they have different costs and different cash flows to offer the company.

It’s up to you, as the project manager, to decide which project will benefit the company the most. You have decided to use the NPV to compare these projects and evaluate them after you have calculated for their NPVs. Since they have irregular cash flows, you will use XNPV.

Calculating and Comparing NPV in Google Sheets

1. To begin, click on a cell to make active, which you would like to display the NPV. For the guide. The NPV will be in Cell G6. Start to type the name of our function, which is XNPV.
   
2. The auto-suggest box will create a drop-down menu. Select the XNPV function by clicking it. It is the first to pop up on the list, but take care to choose the correct function.
   
3. After the opening bracket ‘(‘, you will add the discount rate attribute. You can either type down the discount rate, or use a cell reference. In this example, we used the cell reference by clicking on G4.
   
4. Now you will add the cash flow range attribute. You can either type down each cash flow, or use a cell reference. In this example, we used the cell reference by clicking and dragging on C3:C7.
   
5. Now you will add the date range attribute. In this example, we used the cell reference by clicking and dragging on D3:D7.
   
6. Close off with another bracket and hit enter.
   
7. Go through the steps for the next example.
   
8. You should be able to compare the two projects and the project with a greater Net Present Value should be your choice.

And there you have it – you can now use the XNPV function in Google Sheets together with the other numerous Google Sheets formulas to create even more effective formulas.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content. There will be no spam and you can unsubscribe at any time.

Subscribe