What Is XNPV Function in Google Sheets?
The XNPV function in Google Sheets calculates the net present value of a series of cash flows that occur at irregular intervals. To calculate the same for periodic cash flows, we use the NPV function. XNPV in Google Sheets is useful as it helps one understand the profitability of an investment. It does so by discounting the future cash flows to their present value, after considering each time flow.
Let us see how it is helpful. In case you are looking to make an investment that has returns that are irregular like some in six months, some in a year, etc. In such a scenario, we use the XNPV to determine the present value of these future cash flows. The function takes the following arguments – the discount rate, a range of cash flow amounts, and the corresponding dates for each cash flow. It is helpful in financial planning and analysis. In the example below, we calculate the net present value for an investment with the given costs and returns at a discount of 8%.
=XNPV(.08, A2:A5, B2:B5).
Key Takeaways
- XNPV in Google Sheets calculates the net present value of a series of cash flows each with a specific date and not at regular intervals.
- The function is used to assess the profitability of investments or projects.
- The syntax of the function is the syntax for XNPV is:
=XNPV(rate, cashflows, dates)
- The XNPV, unlike NPV which assumes periodic cash flows, calculates for cash flows that occur at non-regular intervals.
- It is very useful in investment analysis and calculating the present values of lease and loan agreements.
Syntax
Let us look at the function’s syntax to find the net present value for irregular cash flows.
XPNV formula in Google Sheets is as follows:
=XNPV(rate, values, dates)
- rate: It is the expected rate of return you’re expecting and is expressed as a decimal. For example, 8% will be 0.08.
- values: the range of cash flow amounts. We specify inflows as positive and outflows as negative.
- dates: A range of dates corresponding to each cash flow.
How To Use XNPV Function in Google Sheets?
Most financial formulas in Google Sheets can be used directly with a few parameters. Thus, we use XNPV in two ways.
- Enter XNPV manually
- Enter through the Google menu
To calculate XNPV, follow these steps:
Step 1: Enter the required values of cashflows and their corresponding dates in a sheet. Now, click on the cell where the XNPV result will appear.
Step 2: Now, type =XNPV( into the cell.
Step 3: Enter the first argument as the discount rate. Here, we have already entered it in the sheet. So, we enter its cell reference.
Step 4: Now, click and drag over the cashflow range for the second argument, and then over the dates range, separated by a comma, as shown below. Close the parentheses.
Step 5: Press Enter. You find the result of the calculated XNPV value in the selected cell.
Note that the XNPV result can be either positive or negative. A positive value shows that the investment could be profitable, while a negative value will make you reconsider your investment decision.
Entering the XNPV Through the Menu Bar.
- Place the cursor where you want the formula to be entered.
- Go to the Insert tab in Google Sheets.
- Select the option Function and then Financial.
- From the list of functions, select the XNPV function.
Examples
As seen before, XNPV in Google Sheets finds the net present value of a series of cash flows, at a given interest rate with irregular cash flows. Let us use the same in some interesting examples.
Example #1 – Calculate the Net Present Value of an Investment
In this example, we have the details of an investment where payments are irregular and are displayed as shown below:
Since the dates are irregular and not monthly or semi-annual, the use of NPV is out of the question! Hence, to determine the net present value, we use the XNPV function. We determine the net present value with the amounts and their corresponding dates and a rate of 15%.
Step 1: Enter the details as shown below.
Step 2: Enter the formula as shown below.
=XNPV(0.15, A2:A6,B2:B6)
The net present value considering the payments, the dates of those payments, and a rate of 15% is $31,908.
Example #2 – Evaluate the Profitability of a Project
To evaluate the profitability of a project, we can use the XNPV as an example with cashflows and dates. Here, we demonstrate how to calculate the NPV of a project, with specific dates, cashflows, and a given interest rate.
Step 1: Let us create the cashflow and date table in a spreadsheet as shown below.
In Column B, we enter the cash flow and in Column C, we enter their dates.
Step 2: Now, let us choose an empty cell to display the XNPV result. Here, we choose cell B7.
Enter the formula to calculate XNPV as follows:
=XNPV(0.11, B2:B5, C2:C5)
Here,
- 0.11 is the discount rate of 11%.
- B2:B5 is the range of cashflows.
- C2:C5 is the range of dates.
Press Enter. You get the result in the selected cell.
The XNPV of $150,476.10 indicates that for the above cashflows and dates, the NPV for the project at an 11% discount rate is $150,476.10
As the NPV is positive, we can conclude that the project will be profitable, and the project may generate more value than the cost of capital.
Example #3 – Calculate the Present Value of a Lease Agreement
Let us look at an example where we make annual payments of $10,000 at the start of every year. The lease term is for a period of 7 years starting 1/1/2025 while the interest rate is 9%.
The payment dates are at the start of each year. Enter the details in a table as shown below.
Step 1: Payments for the lease are done at the start of each year and hence the cash flows are negative.
Step 2: Let us now use the XNPV formula for the present value of these lease payments. Enter the following formula:
=XNPV(0.09, A2:A8, B2:B8)
Here,
- 0.09 is the annual interest rate.
- A2:A8 is the range of cashflows.
- B2:B8 is the range of dates for each payment.
Step 3: Press Enter. We get the present value of these lease payments.
The negative sign means that lease payments of $54.854.57 must be paid over seven years. The payer must account for this present value as the total cost of the lease agreement in current terms.
Important Things To Note
- The number of dates and cashflows in the table should match for the function to work properly.
- Google Sheets stores dates internally as decimal numbers allowing for calculations and comparisons since December 30, 1899. Hence, the XNPV function can be used with ease.
- If one of the parameters is nonnumeric, the XNPV function returns the #VALUE! error value.
- We also get an error when the dates are not in a valid date format.
- None of the other dates should precede the starting date, else we get an error.
Frequently Asked Questions (FAQs)
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. It assumes that all cash flows occur at regular intervals. The XNPV function allows us to specify a different date for the start of the investment period. Hence, it is also more flexible as it allows us to specify the exact dates for each cash flow, rather than assuming equal intervals.
If you enter cash flow dates in the wrong order, XNPV will return an error because the dates must be in chronological order. It is essential that the dates are correctly arranged from the earliest to the latest for accurate results.
1) Ensure that you include the initial investment as a negative cash flow in the cash flow range.
2) Check if all the dates are formatted correctly to avoid errors.
3) The accuracy of the result depends on how accurate the cash flow projections are.
Download Template
This article must help understand XPNV Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is XPNV Function in Google Sheets. We learn its syntax & how to use it to calculate the net present with step-wise examples. You can learn more from the following articles.
Leave a Reply