XNPV Function

How to use XNPV in Excel

Why use the XNPV function 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.

 

What is the XNPV formula?

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.

 

Example of XNPV in Excel

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:

  • The discount rate is 10%
  • The start date is June 30, 2018 (date we are discounting the cash flows back to)
  • Cash flows are received on the exact date they correspond to
  • The time between the start date and the first cash flow is only 6 months

 

XNPV function 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.

 

Download XNPV template

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.

 

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, 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!

 

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 for an investment.

If you’re considering a career in investment banking or private equity 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 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: