XNPV Function Examples – Excel, VBA, & Google Sheets

This tutorial demonstrates how to use the Excel XNPV Function in Excel to calculate the net present value of an investment

XNPV Function Overview

The XNPV Function Calculates the net present value.

To use the XNPV Excel Worksheet Function, select a cell and type:

 

 

(Notice how the formula inputs appear)

XNPV Function Syntax and Inputs:

=XNPV(rate,values,dates)

rate – The interest rate for each period.

nper – The discounted rate or the rate of return of the investment.

values– The values argument refers to the series of cash flows of an investment. The first payment is optional and represents the cost of the investment that occurs at the start of the investment.

dates – This argument refers to the array of dates at which the cash flows occur. Its length should be the same as the length of the values array. Cash Flows values must be entered with dates in chronological order.

 

What is the Excel XNPV Function?

Excel XNPV Function calculates the Net Present Value of an investment based on the discounted rate or the rate of return on investment and a series of cash flows that occur at irregular periods. On the other hand, the NPV excel function assumes that all the series of cash flows occur at regular intervals. That’s why the XNPV excel function is far more precise in calculating the Net Present Value.

The Excel XNPV function uses the following equation for the calculation of the Net Present Value:

Where:

n = number of time periods

i = period number

rate = discount rate or interest rate

di = the ith payment or the last payment

d1 = the 0^th payment

Pi = the ith payment or the last payment

NPV Calculation on the returns of an investment

Let’s assume we invest $ 30,000 in a business. And after some time we start getting returns from the business. The returns along with their corresponding dates are mentioned in the image above.

For this example, we discounted the cash flows at a rate of 5%.

The Formula used for the calculation of the net present value of an investment with irregular cash flows is:

=XNPV(B4,B5:B10,C5:C10)

Based on the above values, the XNPV function produces the following net present value of the business

XNPV = $7,865.21

NPV Calculation of a Project with irregular cash flows 

Here, the XNPV function is used to calculate the Net Present Value of a series of cash flows that occur at irregular periods. The series of cash flows and their corresponding dates are mentioned above in the image.

The discounted rate is assumed to be 10% and the start date is April 28, 2019.

The formula used for calculation is:

=XNPV(B4,B5:B9,C5:C9)

Based on the above values and dates, the XNPV function calculates the Net Present Value of the project

XNPV = $57,172.46

Additional Notes

In the Excel Financial Functions the cash outflows, such as deposits, are represented by negative numbers and the cash inflows, such as dividends, are represented by positive numbers.

#NUM! Error occurs if any of the other dates are earlier than the first date.

#VALUE! Error occurs if the rates and values arguments are non-numeric.

Return to the List of all Functions in Excel

 

XNPV in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

XNPV Examples in VBA

You can also use the XNPV function in VBA. Type:

application.worksheetfunction.xnpv(rate,values,dates)

For the function arguments (rate, etc.), you can either enter them directly into the function or define variables to use instead.