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 functionNPV FunctionThe NPV Function is categorized under Financial functions. It will calculate the Net Present Value (NPV) for periodic cash flows. The NPV will be calculated for an investment by using a discount rate and series of future cash flow. In financial modeling, the NPV function is useful in determining the value of a business.
More learning: read CFI’s list of top Excel formulasExcel Formulas Cheat SheetCFI's Excel formulas cheat sheet will give you all the most important formulas to perform financial analysis and modeling in Excel spreadsheets..
What is the XNPV formula?
The XNPV formula is Excel requires the user to select a discount rate, a series of cash flowsValuationThe XNPV function in Excel should be used over the regular NPV function in financial modeling and valuation analysis to ensure precision and accuracy. The XNPV formula uses specific dates that correspond to each cash flow being discounted in the series. Learn step by step in this guide with examples and sceenshots, and a series of corresponding dates for each cash flow.
The Excel formula for XNPV is:
=XNPV(Rate, Cash Flows, Dates of Cash Flow)
The XNPV function uses the following three components:
Rate – The discount rate to be used over the length of the period (see hurdle rateHurdle Rate DefinitionA hurdle rate is the rate of return that must be achieved before accepting and funding an investment project. Hurdle rates are used in financial modeling to calculate NPV. If IRR > Hurdle Rate then the investment creates value. The rate is determined by assessing the cost of capital, risks involved, opportunity cost and WACCWACCWACC is a firm’s Weighted Average Cost of Capital and represents its blended cost of capital including equity and debt. The WACC formula is = (E/V x Re) + ((D/V x Rd) x (1-T)). This guide will provide an overview of what it is, why its used, how to calculate it, and also provides a downloadable WACC calculator articles to learn about what rate to use).
Values (Cash Flows) – This is an array of numeric values that represent the payments and income where:
Negative values are treated as outgoing payments (negative cash flow).
Positive values are treated as income (positive cash flow).
Dates (of Cash Flows) – It is an array of dates corresponding to an array of payments. The date array should be of the same length as the values array.
The function uses the following equation to calculate the Net Present Value of an investment:
Where:
d_{i}= the i‘th payment date d_{1} = the 0’th payment date P_{i} = the i‘th payment
Please see the example below for a detailed breakdown of how to use XNPV in Excel.
Example of XNPV in Excel
Below is a screenshot of an Example of the function being used in Excel to calculate the Net Present ValueNet Present Value (NPV)Net Present Value (NPV) is the value of all future cash flows (positive and negative) over the entire life of an investment discounted to the present. NPV analysis is a form of intrinsic valuation and is used extensively across finance and accounting for determining the value of a business, investment security, of a series of cash flows based on specific dates.
Key assumptions in the XNPV 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
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 the XNPV template
If you’d like to incorporate this function in your own financial modeling and valuation work, please feel free to Download CFI’s 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 modelingWhat is Financial ModelingFinancial modeling is performed in Excel to forecast a company's financial performance. Overview of what is financial modeling, how & why to build a model. A 3 statement model links income statement, balance sheet, and cash flow statement. More advanced types of financial models are built for valuation, plannnig, and 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)LBO ModelAn LBO model is built in excel to evaluate a leveraged buyout (LBO) transaction, which is the acquisition of a company that is funded using a significant amount of debt. The aim of the LBO model is to enable investors to properly assess the transaction earn the highest possible internal rate of return (IRR). 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.
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 IRRXIRR vs IRRWhy use XIRR vs IRR. XIRR assigns specific dates to each individual cash flow making it more accurate than IRR when building a financial model in Excel. The Internal Rate of Return is the discount rate which sets the Net Present Value of all future cash flow of an investment to zero. Use XIRR over 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 returnInternal Rate of Return (IRR)The Internal Rate of Return (IRR) is the discount rate that sets the net present value of an investment equal to zero. This guide to calculating IRR will give several examples and who why it's used in capital budgeting, private equity and other areas of finance and investing. If IRR is greater than cost of capital, on 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 how to incorporate this important Excel function in your financial modeling.
To keep expanding your knowledge base and advancing your career as a Professional Financial ModelerFMVA™ CertificationThe Financial Modeling & Valueation Analyst (FMVA)™ accreditation is a global standard for financial analysts that covers finance, accounting, financial modeling, valuation, budgeting, forecasting, presentations, and strategy. we believe these additional resources will be helpful as well:
Hurdle rate overviewHurdle Rate DefinitionA hurdle rate is the rate of return that must be achieved before accepting and funding an investment project. Hurdle rates are used in financial modeling to calculate NPV. If IRR > Hurdle Rate then the investment creates value. The rate is determined by assessing the cost of capital, risks involved, opportunity cost
WACC formulaWACCWACC is a firm’s Weighted Average Cost of Capital and represents its blended cost of capital including equity and debt. The WACC formula is = (E/V x Re) + ((D/V x Rd) x (1-T)). This guide will provide an overview of what it is, why its used, how to calculate it, and also provides a downloadable WACC calculator
Guide to financial modelingFree Financial Modeling GuideThis financial modeling guide covers Excel tips and best practices on assumptions, drivers, forecasting, linking the three statements, DCF analysis, Excel modeling and much more. Designed to be the best free modeling guide for analysts by using examples and step by step instructions. Investment banking, FP&A, research
Advanced Excel formulasAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
Free Excel Tutorial
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.