The XNPV Function in Excel uses specific dates that correspond to each cash flow being discounted in the series, whereas the regular NPV function automatically assumes all the time periods are equal. For this reason, the XNPV function is far more precise and should be used instead of the regular NPV function.
It may be a good idea to experiment with changing the dates around and seeing the impact on valuation, or the relative difference between XNPV vs NPV in the Excel model.
XNPV vs NPV implications
The results of the comparisons of XNPV vs NPV formulas produces an interesting result and some very important implications for a financial analyst. Imagine if the analyst were valuing a security and didn’t use the proper time periods that XNPV takes into account. They would be undervaluing the security by a meaningful amount!
Use in financial modeling
XNPV is used routinely in financial modeling as a means of calculating the net present value (NPV) of an investment opportunity.
For all types of financial models, XNPV and XIRR are highly recommended over their date-less counterparts NPV and IRR.
While the added precision will have you feeling more confident about your analysis, the only downside is that you have to pay careful attention to the dates in your spreadsheet and make sure the start date always reflects what it should.
For a transaction such as a Leveraged Buyout (LBO) or an acquisition, it’s important to be precise about the closing date of the deal. For example, you may be building the model now, but the closing date will likely be several months in the future.
XNPV doesn’t discount the initial cash flow (it brings all cash flows back to the date of the first cash flow). Subsequent payments are discounted based on a 365-day year.
#NUM! error – Occurs when either:
The values and dates arrays are of different lengths; or
Any of the other dates are earlier than the start date.
#VALUE! error – Occurs when either:
The values or rates arguments are non-numeric; or
The given dates are not recognized by Excel as valid dates.
XIRR vs IRR
To learn more about XIRR vs IRR we have created a similar guide, which we highly recommend you check out to further solidify the concept. For the same reasons as noted above in this guide, it’s equally important to use specific dates when calculating the internal rate of return on an investment.
If you’re considering a career in investment banking or private equity, then you will be required to use these functions extensively.
More Resources from CFI
We hope this has been a helpful guide to help you understand how to incorporate the important XNPV Excel function in your financial modeling.
To keep expanding your knowledge base and advancing your career as a Professional Financial Modeler we believe these additional CFI resources will be helpful as well: