Difference Between NPV and XNPV

Net Present Value Equation

NPV vs. XNPV: Which Is More Appropriate?

The terms NPV and XNPV are familiar to any accountant or software spreadsheet aficionado. NPV and XNPV are both formulas used to derive cash flow. Computing for NPV or XNPV can be done two ways: by using a calculator or a pre-programmed spreadsheet. Even with the aid of a calculator, computing manually for NPV or XNPV is time-consuming and prone to mathematical error. Using a spreadsheet such as the one in Microsoft Excel is much easier; one simply has to input the formula for NPV or XNPV on the formula bar and enter values afterwards. Microsoft Excel can compute for either NPV or XNPV via its Financial Functions feature.

Most people who are not familiar with accounting or software spreadsheets would not know how to compute or differentiate between NPV and XNPV. It’s easy to mistake one for the other, since their inputs are similar. Both NPV and XNPV take into consideration values such as annual discount rate and periodic or monthly discount rate. However, they have a clear-cut difference. NPV stands for Net Present Value. This formula is used compute for investment returns between two payments. The NPV shows the present value of all future cash flows, both negative and positive, by using the discount rate as its basis. The NPV assumes that payments to be made in the future are made on a regular basis, with equal time intervals.

The XNPV is a modified version of the NPV. It is also used to arrive at a Net Present Value, but with a unique twist: the formula assumes that the cash flows do not come in equal time intervals. In order to effectively differentiate between the two formulas, a few examples might come in handy. If one were to compute the present value of future returns between two payments on a monthly basis, then NPV can be freely used, as long as each payment is made at a regular interval. If the interval between two payments is exactly one year, then NPV is able to determine the cash flow at a regular basis; for example, at the end of each month. Each investment return would have a one-month interval before the next. However, if the payments are not made regularly, then the XNPV formula has to be utilized instead of NPV.

XNPV on Microsoft Excel

Despite their similarities regarding input values, NPV and XNPV yield different results. How does one enter NPV or XNPV values in a spreadsheet? First, one should enter three values into the rows, or Y-axis, of the spreadsheet: year, cash flow, and discount rate. Next, one should indicate whether the interval between the two payments is to be months or years. The time intervals should be indicated in the columns, or X-axis, of the spreadsheet. Once the values are in the spreadsheet, one simply has to use Microsoft Excel’s Financial Functions feature do derive either the NPV or XNPV. For further information on how to use NPV or XNPV on Microsoft Excel, simply refer to the Help feature by pushing the F1 button, or refer to guides over the Internet.

Summary:

1. NPV and XNPV are used to derive the net present value of cash flow between two payments.
2. The NPV and XNPV can be computed via Microsoft Excel’s Financial Functions featured. They can also be computed using a calculator, although using a spreadsheet is easier and less prone to mathematical errors.
3. NPV assumes that payments to be made in the future will be made on a regular basis, with equal time intervals.
4. XNPV, on the other hand, assumes that payments are not made on a regular basis.
5. Computing for the NPV and XNPV yields different results even if the same input values are used.

Sharing is caring!