ExcelInsider Team
In order to calculate net present value, Microsoft Excel provides two functions called XNPV and NPV. In general, XNPV and NPV are complex calculations that involve numerous steps. The functions provided by Excel make it easier to calculate XNPV and NPV without having to do it manually. In this article, we will learn the application of XNPV and NPV functions in Excel and do a comparison so that you can decide which function is better for what kind of data.
➤ The following formula can be used to calculate XNPV:
=XNPV(E1,B2:B13,A2:A13)
➤ Replace E1 with the discount rate, B2:B13 with the series of cash flows including the investment, and A2:A13 with the payment dates.
➤ To calculate NPV, enter the following formula in the target cell:
=NPV(E1,B3:B13)+B2
➤ E1 needs to be replaced with the discount rate, B3:B13 with the cash flow, and B2 with the investment in a negative format.
Calculating the net present value is important to decide whether to carry on the investment project. In this article, we will learn the functions that Microsoft Excel gives us to do so.
What is XNPV in Excel?
The XNPV (Extended Net Present Value) function in Excel calculates the net present value from a list of cash flows and the list of dates those cash amounts were paid. The function has three parameters that must be filled in order to make the function work. Here is the syntax of the function:
=XNPV(rate, values, dates)
The first parameter is the discount rate of the cash flow. This can be the cost of capital or the inflation rate, depending on the project you are handling. The second parameter is the range of the values. This one should contain one positive and one negative value for the function to work. The third and final parameter contains the dates of the cash flow payments. The first date must be the oldest one, and the later ones can use any order.
What is NPV in Excel?
The NPV (Net Present Value) function calculates the net present value of a project using the discount rate and the cash flows. The cash flows might contain both future payments and incomes as negative and positive values. The syntax of the function is as follows:
=NPV(rate,value1,[value2],...)
The first parameter is the annual discount rate, as NPV assumes that the cash flows are annual. The second parameter can be the payment or the income. We can also insert a range of payments/incomes here. Instead of using a range, we can insert the values and cell references one by one from the second parameter and keep adding more parameters to the functions. A total of 254 parameters of cash flow can be added.
NPV assumes that the investment began one period before the first value and will end with the last value. If the first cash flow occurs at the beginning of the investment and not before, the first value needs to be added to the NPV value after completing the calculation, and should not be included in the function in the first place.
The following table contains a detailed comparison between XNPV and NPV functions in Excel:
| Criteria | XNPV | NPV |
|---|---|---|
| Full Form | Extended Net Present Value | Net Present Value |
| Syntax | =XNPV(rate, values, dates) | =NPV(rate,value1,[value2],…) |
| Time Period | Considers irregular time intervals and requires the dates of the cash flows | Does not require cash flow dates and assumes that the time intervals are equally one year |
| First Cash Flow | Assumes that it happens on the date provided in the parameters | Presumes that the first cash flow given as a parameter happened at the end of the first period |
| Usefulness | More useful for project-like investment | Used for annuities in general |
Calculating XNPV in Excel
We have a dataset with some uneven cash flows and different dates. The discount rate is 10%, and we must calculate the XNPV from the table. Here is how we can do that:
➤ In E2 cell, write the following formula, and press Enter:
=XNPV(E1,B2:B13,A2:A13)
Measuring NPV in Excel
We have another dataset that we are going to use to calculate NPV. For this time, we receive the amounts at the beginning of each year. The discount rate is 5%. Here is how to calculate NPV in Excel:
➤ Insert the following formula to calculate the NPV:
=NPV(E1,B3:B13)+B2
➤ Press Enter to complete the calculation.
Frequently Asked Questions
What is the difference between NPV and PV in Excel?
The PV function cannot consider variable payments in the time period, but the NPV can. Otherwise, PV and NPV functions basically give the same output if you have constant cash flows.
What are XNPV and XIRR?
XNPV is the extended version of NPV (net present value), and XIRR is the extended version of IRR (internal rate of return). XNPV calculates the net present value from a series of cash flows containing future values from the project. It requires the cash flows, the rate of return, and the dates of payments. XIRR, on the other hand, calculates the rate of return from the cash flows and the dates of payments.
What do PV and FV mean in Excel?
The PV function calculates the present value from the future value of an investment. The FV function does the exact opposite and calculates the future value from the present amount that you are investing. Both of the functions take three parameters: the discount rate, how many times you will receive the amount of money, and the present/future values.
Is XNPV accurate?
The XNPV function is more accurate than the NPV function in Excel. In the real world, most investments don’t provide profit at the same interval. Using the exact dates for each payment paints a more accurate picture of the investment.
Why is NPV different in Excel?
When calculating NPV manually, people assume that the first cash flow occurs at the beginning of the first period, while Excel assumes the opposite. As a result, we need to add the investment to the NPV later, after completing the function-based calculation in Excel.
Wrapping Up
In this article, we have learned how to use the XNPV and NPV functions in Excel. We have explored the differences between those functions and learned when the use of the functions would be appropriate for determining the valuation of an investment. The example Excel file used in this article can be downloaded for your convenience. We will see you next time in another article.
