How to use XNPV in Excel
How to use XNPV in Excel
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 over the regular NPV function.
More learning: read our list of top Excel formulas.
The XNPV formula is Excel requires the user to select a discount rate, a series of cash flows, and a series of corresponding dates for each cash flow.
The formula for XNPV is:
=XNPV(Discount Rate, Cash Flows, Dates of Cash Flow)
Please see the example below for a detailed breakdown of how to use XNPV in Excel.
To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Browse all of CFI’s Excel courses to take your career to the next level!
Below is a screen shot of an Example of XNPV being used in Excel to calculate the Net Present Value of a series of cash flows based on specific dates.
Key assumptions in the XNP example:
Based on the above, the XNPV formula produces a value of $772,830.7 while the regular NPV formula produces a value of $670,316.4.
The reason for this difference is that XNPV recognizes that time period between the start date and first cash flow is only 6 months, while the NPV function treats it as a full-time period.
If you’d like to incorporate this function in your own financial modeling and valuation work, please feel free to Download our XNPV function template and use it as you see it.
It could 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.
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!
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, like with 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 by several months in the future.
To see XNPV in action, check out our financial modeling courses!
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 for an investment.
If you’re considering a career in investment banking or private equity you will be required to use these functions extensively.
We hope this has been a helpful guide to help you understand the important differences of XNPV vs NPV in financial modeling.
To keep expanding your knowledge base and advancing your career we believe these additional resources will be helpful for you as well: