Financial Modeling Tutorials
 Financial Modeling Basics
 What is Financial Modeling?
 Financial Modeling in Excel
 Types of Financial Models
 Financial Modeling Interview Questions
 Alibaba IPO Financial and Valuation Model
 Box IPO Modeling Details
 Box IPO Valuation Model
 Download Alibaba IPO Financial Model
 Financial Modeling Books
 Financial Modeling Templates
 Financial Modeling Course
 Excel Modeling
 Financial Functions in Excel
 Sensitivity Analysis in Excel
 Time Value of Money
 Future Value Formula
 Present Value Factor
 Perpetuity Formula
 Annuity vs Perpetuity
 Internal Rate of Return (IRR)
 NPV vs XNPV
 NPV vs IRR
 NPV Formula
 IRR vs ROI
 Break Even Point
 Payback Period & Discounted Payback Period
 Payback period Formula
 Discounted Payback Period Formula
 Profitability Index
 Cash Burn Rate
 Simple Interest
 Effective Interest Rate
 Loan Amortization Schedule
 Rule of 72
 Geometric Mean Return
 Real Rate of Return Formula
 Continuous compounding Formula
 Weighted average Formula
 Holding Period Return Formula
 Cost Benefit Analysis
 Mortgage APR vs Interest Rate
NPV vs XNPV – Net Present Value (NPV) is defined as the difference between the existing value of net cash arrivals and the existing value of total cash expenditures. While NPV is most helpful in the case of periodic cash flows, XNPV, on the other hand, determines the Net Present Value for a range of cash payments that need not be essentially periodic.
In this article, we look at NPV vs XNPV in detail –
 What is NPV?
 Project Selection using NPV
 Using NPV in Excel
 NPV Example #1 – with specified predefined cash inflow
 NPV Example #2 – with uniform cash inflow
 What is XNPV?
 Using XNPV in Excel
 XNPV Example 1
 XNPV Example 2
 NPV vs XNPV Example
 Common Errors for XNPV Function
Recommended Courses
Also, have a look at NPV vs IRR
What is NPV?
Net Present Value (NPV) is defined as the difference between the existing value of net cash arrivals and the existing value of total cash expenditures. NPV is generally used while preparing capital budgeting estimates for accurately determining the viability of any new project or a prospective investment opportunity.
The formula for determining NPV (when cash arrivals are even):
NPV_{t=1 to T } = ∑ Xt/(1 + R)^{t} – Xo
Where,
 X_{t }= total cash inflow for period t
 X_{o }= net initial investment expenditures
 R = discount rate, finally
 t = total time period count
The formula for determining NPV (when cash arrivals are uneven):
NPV = [C_{i1}/ (1+r)^{1} + C_{i2}/(1+r)^{2} + C_{i3}/(1+r)^{3} + …] – X_{o}
Where,
 R is the specified return rate per period;
 C_{i1} is the consolidated cash arrival during the first period;
 C_{i2} is the consolidated cash arrival during the second period;
 C_{i3} is the consolidated cash arrival during the third period, etc…
Project Selection using NPV
For individual projects, take a project simply when its NPV calculated as positive, discard it if project NPV calculated as negative and remain indifferent towards considering or discarding if project NPV arrived at zero.
For completely different projects or competing projects, consider the project having greater NPV.
Net present value with a positive sign signifies that the estimated earnings delivered by any investment opportunity or a project (in existing dollar denominations) surpass the projected expenditures (also in existing dollar values). Usually, any investment having positive NPV results is bound to be a lucrative one while one having negative NPV results would lead to an overall loss. This idea particularly defines the Net Present Value Rule, indicating that only those investments must be considered that have positive NPV results.
In addition, suppose the investment opportunity is related to a merger or an acquisition, one may even employ the Discounted Cash Flow (DCF) technique.
Besides the NPV formula, the net present value may even be calculated leveraging spreadsheets, tables like Microsoft Excel as well as the NPV calculator.
Using NPV in Excel
Using NPV in the excel sheet is very easy.
= NPV (Rate, Value1, Value2, Value3..)
 Rate in the formula is the discount rate that is used in one period
 Value 1, Value 2, Value 3 etc are the cash inflows or outflows at the end of period 1, 2, 3, respectively.
NPV Example #1 – with specified predefined cash inflow
Suppose a company is keen on analyzing the estimated viability of a key project that demands an early outflow $20,000. Over the three years period, the project seems to deliver revenues of $4000, $14,000 and $22,000, respectively. The projected discount rate expects to be 5.5%. At initial glance, it appears that the investment returns are almost double the initial investment. But, the amount earned over three years remains not of the same value as the net amount earned today, hence the accountant of the company determines the NPV in a unique way for identifying the overall profitability whereas, calculating the reduced time value of estimated revenues:
NPV Example #1 – Solution using Manual Calculation
To calculate Net Present Value one should remember the following points:
 Addition of the Present Value received
 Deduction of the Present Value being paid
NPV = {$4,000/(1+.055)^1} + {$14,000/(1+.055)^2} + {$22,000/(1+.055)^3} – $20,000
= $3,791.5 + $12,578.6 + $18,739.4 – $20,000
= $15,105.3
NPV Example #1 – Solution using Excel
Solving NPV problems in Excel are very easy. First, we need to put the variables in the standard format as given below with Cash Flows in one row.
In this example, we are provided with the discount rate of yearly discount rate of 5.5%. When we use NPV Formula, we start with $4000 (cash inflows at the end of year 1) and choose the range until $22,000 (
When we use NPV Formula, we start with $4000 (cash inflows at the end of year 1) and choose the range until $22,000 (corresponding to the cash inflows of year 3)
The Present Value of Cash Flows (year 1, 2 and 3) is $35,105.3
Cash invested or the Cash outflow in Year 0 is $20,000.
When we deduct the cash outflow from the present value, we get the Net Present Value as $15,105.3
NPV Example #2 – with uniform cash inflow
Determine a project’s net present value that needs an early investment worth $245,000 while it is estimated to deliver a cash arrival of $40,000 every month for forthcoming 12 months. The remaining project value is assumed to be zero. The expected return rate is 24% per annum.
NPV Example #2 – Solution using Manual Calculation
Given,
Early investment = $245,000
Overall cash arrival per period = $40,000
Period count = 12
Discount Rate for each period = 24%/12 = 2%
NPV Calculation:
= $40,000*(1(1+2%) ^12)/2% – $245,000
= $178,013.65
NPV Example #2 – Solution using Excel
Like we did in our earlier example, the first thing we will do is to put the cash inflows and cash outflows in the standard format as per given below.
There are some important things to note in this example –
 In this example, we are provided with monthly cash inflows whereas the discount rate provided is that of the full year.
 In the NPV formula, we need to ensure that the discount rate and the cash inflows are in same frequency, meaning if we have monthly cash flows then we should have a monthly discount rate.
 In our example, we will work around the Discount Rate and convert this yearly discount rate into a monthly discount rate.
 Yearly Discount Rate = 24%. Monthly Discount Rate = 24% / 12 = 2%. We will use 2% discount rate in our calculations
Using this monthly cash inflows and monthly discount rate of 2%, we calculate the present value of the future cash flows.
We get the present value of monthly cash inflows as $423,013.65
Cash Invested or Cash outflow in Month 0 was $245,000.
With this, we get the Net Present Value of $178,013.65
What is XNPV?
The XNPV function in excel primarily determines the Net Present Value (NPV) for a range of cash payments that need not be essentially periodic.
XNPV_{t=1 to N } = ∑ Ci/[(1 + R)^{d} x d_{o}/365]
Where,
 d_{x} = the x’th expense date
 d_{o }= the date for 0’th expense
 C_{i }= the i’th expense
Using XNPV in Excel
The XNPV function in Excel employs the following formula for calculating the net present value of any investment opportunity:
XNPV(R, Value Range, Date Range)
Where,
R = discount rate for cash flows
Value Range = A set of numeric data, depicting income and payments, where:
 Positive figures are identified as income;
 Negative figures are identified as payments.
The first disbursement is discretionary and signifies a payment or expense at the start of an investment.
Date Range = A range of dates equivalent to a series of expenditures. This payment array should match with the array of supplied values.
XNPV Example 1
We will take the same example that we took earlier with NPV and see if there is any difference between the two approaches of NPV vs XNPV.
Suppose a company is keen on analyzing the estimated viability of a key project that demands an early outflow $20,000. Over the three years period, the project seems to deliver revenues of $4000, $14,000 and $22,000, respectively. The projected discount rate expects to be 5.5%.
First, we will put the cash inflows and outflows in the standard format. Please note here that we have also put the corresponding dates along with the Cash Inflows and Outflows.
The second step is to calculate by providing all the necessary inputs for XNPV – Discount Rate, Value Range and Date Range. You will note that in this XNPV formula, we have also included the cash outflows done today.
We get the Present value using XNPV as $16,065.7.
With NPV, we got this Present Value as $15,105.3
Present Value using XNPV is higher than that of NPV. Can you guess why we get different present values under NPV vs XNPV?
The answer is simple. NPV assumes that the future cash inflows happen at the end of the year (from today). Let’s assume that today is 3rd July 2017, then the first cash inflow of $4000 is expected to come after one year from this date. This means you get $4,000 on 3rd July 2018, $14,000 on 3rd July 2019 and $22,000 on 3rd July 2020.
However, when we calculated the present value using XNPV, the cash inflow dates were the actual year end dates. When we use XNPV, we are discounting the first cash flow for a period that is less than one year. Likewise, for others. This results in the Present Value using XNPV formula to be greater than that NPV formula.
XNPV Example 2
We will take the same NPV Example 2 to solve using XNPV.
Determine a project’s net present value that needs an early investment worth $245,000 while it is estimated to deliver a cash arrival of $40,000 every month for forthcoming 12 months. The remaining project value is assumed to be zero. The expected return rate is 24% per annum.
The first step is to put the cash inflow and outflows in the standard format shown below.
In NPV example, we converted our yearly discount rate into the monthly discount rate. For XNPV, we are not required to do this extra step. We can directly use the yearly discount rate
The next step is to use the discount rate, cash flows range and date range in the formula. Please note that we have also include cash outflows that we did today in the formula.
Present Value using XNPV formula is $183,598.2
Contrasting this with that of NPV Formula, the present value using NPV is $178,013.65
Why there XNPV formula yields present value higher than that of NPV? The answer is simple and I leave it to you to contrast NPV vs XNPV in this case.
NPV vs XNPV Example
Now let us take another example with NPV vs XNPV head to head. Let us assume that we have the following cash flow profile
Cash Outflow year – $20,000
Cash Inflow
 1st Year – $4000
 2nd Year – $14,000
 3rd Year – $22,000
The objective here is to find out whether you will accept this project or reject this project given a series of Cost of Capital or Discount Rates.
Using NPV
The cost of Capital is in the leftmost column starting from 0% and goes to 110% with a step of 10%.
We will accept the project if NPV is greater than 0, else we reject the project.
We note from the above graph that NPV is positive when the Cost of Capital is 0%, 10%, 20% and 30%. This means that we accept the Project when Cost of Capital is from 0% to 30%.
However, when the cost of Capital increases to 40%, we note that the Net Present value is negative. There we reject this project. We note that as Cost of Capital increases, Net Present value decreases.
This can be seen graphically in the graph below.
Using XNPV
Let us now run the same example with XNPV formula.
We note that Net Present Value is positive using XNPV for cost of capital of 0%, 10%, 20%, 30% as well as 40%. This means that we accept the project when the cost of capital is between 0% and 40%. Please note that this answer is different from the one that we got using NPV where we rejected the project when the cost of capital reached 40%.
Below graph depicts the Net Present Value of the Project using XNPV at the various cost of capital.
Common Errors for XNPV Function
If the user gets an error while using the XNPV function in excel this could fall in either of the belowmentioned categories:
Common Errors 
#NUM! Error

#VALUE! Error

Mark Holocher says
Thanks for the summary. The NPV does take into account the time value of money but it is important to use a discount rate that is closely tied to a project or industry with similar risk. This type of comparison can be tricky and has a big impact on the final NPV figure. The IRR (internal rate of return) is another capital budgeting technique that also takes into consideration the time value of money but focuses on the actual rate of return of the project and then compares that to the discount rate. Either technique can be used but some prefer one over the other. The payback period is not a good technique to use because it does not consider the time value of money as well as a big cash inflow could occur soon after the payback period has ended.
Antonio Sendi says
Thank you so much Dheeraj for continuous sharing/display of information and researching results, this are valuable information for our growth as professional committed with selfdevelopment. In my case is the very first time i hear about XNPV, far before this article i was considering only NPV as the robust metric to evaluate a project, now i’m aware and better prepared to deal with feasibiity particularly if the series of cash flows estimated within a certain financial model were yield different periods of time.
Your sincerely
Antonio Sendi
Dheeraj Vaidya says
thanks Antonio!
B Vijaya Kumar says
A very good and useful analysis. XNPV is a refined tool discounting on the basis of dates instead of just years. Thus in a way NPV is fine tuned with XNPV tool.
Thanks for the article.
Dheeraj Vaidya says
Thanks B Vijaya!