NPV vs XNPV | Top Differences with Excel Examples

NPV vs XNPV

Net Present Value (NPV) is defined as the difference between the existing value of net cashNet Present Value (NPV) Is Defined As The Difference Between The Existing Value Of Net CashNet Cash represent the company's liquidity position and is calculated by deducting the current liabilities from the cash balance reported on the company’s financial statements at the end of a particular period. Analysts and investors examine it to have a better understanding of the company's financial and liquidity position.read more 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.

NPV vs XNPV

In this article, we look at NPV vs XNPV in detail –

Also, have a look at NPV vs IRRNPV Vs IRRThe Net Present Value (NPV) method calculates the dollar value of future cash flows which the project will produce during the particular period of time whereas the internal rate of return (IRR) calculates the profitability of the project.read more

What is NPV?

Net Present Value (NPV) is defined as the difference between the existing value of net cashNet CashNet Cash represent the company's liquidity position and is calculated by deducting the current liabilities from the cash balance reported on the company’s financial statements at the end of a particular period. Analysts and investors examine it to have a better understanding of the company's financial and liquidity position.read more arrivals and the existing value of total cash expenditures. NPV is generally used while preparing capital budgetingCapital BudgetingCapital budgeting is the planning process for the long-term investment that determines whether the projects are fruitful for the business and will provide the required returns in the future years or not. It is essential because capital expenditure requires a considerable amount of funds.read more 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):

NPVt=1 to T  = ∑  Xt/(1 + R)t – Xo

Where,

  • Xt = total cash inflow for period t
  • Xo = net initial investment expenditures
  • R = discount rate, finally
  • t = total time period count

The formula for determining NPV (when cash arrivals are uneven):

NPV = [Ci1/ (1+r)1 + Ci2/(1+r)2 + Ci3/(1+r)3 + …] – Xo

Where,

  • R is the specified return rate per period;
  • Ci1 is the consolidated cash arrival during the first period;
  • Ci2 is the consolidated cash arrival during the second period;
  • Ci3 is the consolidated cash arrival during the third period, etc…

Project Selection using NPV

For individual projects, take a project simply when its NPV is calculated as positive, discard it if project NPV is 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 earningsEstimated EarningsEarnings Estimate is the projection of earning of an entity for a given period. Future projects, cash flows, market conditions, and several other factors are considered in calculating this estimate.read more 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.

Besides the NPV formula, the net present value may even be calculated by 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 Formula - Excel

= NPV (Rate, Value1, Value2, Value3..)

  • The 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 periods 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 of $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 of 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 FlowsCash FlowsCash Flow is the amount of cash or cash equivalent generated & consumed by a Company over a given period. It proves to be a prerequisite for analyzing the business’s strength, profitability, & scope for betterment. read more in one row.

In this example, we are provided with a discount rate of a 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 FormulaWe Use NPV FormulaThe NPV (Net Present Value) of an investment is calculated as the difference between the present cash inflow and cash outflow. It is an Excel function and a financial formula that takes rate value for inflow and outflow as input.read more, 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)

NPV Example 1a

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.

NPV Example 1b

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 the 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 –

  1. In this example, we are provided with monthly cash inflows, whereas the discount rate provided is that of the full year.
  2. In the NPV formula, we need to ensure that the discount rate and the cash inflows are in the same frequency, meaning if we have monthly cash flows, then we should have a monthly discount rate.
  3. In our example, we will work around the Discount Rate and convert this yearly discount rate into a monthly discount rate.
  4. Yearly Discount Rate = 24%. Monthly Discount Rate = 24% / 12 = 2%. We will use a 2% discount rate in our calculations

NPV Example 2a

Using these monthly cash inflows and a monthly discount rate of 2%, we calculate the present value of the future cash flows.

NPV Example 2bb

We get the present value of monthly cash inflows as $423,013.65

NPV Example 2cc

Cash Invested or Cash outflow in Month 0 was $245,000.

With this, we get the Net Present ValueNet Present ValueNet Present Value (NPV) estimates the profitability of a project and is the difference between the present value of cash inflows and the present value of cash outflows over the project’s time period. If the difference is positive, the project is profitable; otherwise, it is not.read more 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.

XNPVt=1 to N  = ∑  Ci/[(1 + R)d x do/365]

Where,

  • dx = the x’th expense date
  • do = the date for 0’th expense
  • Ci = 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 Excel Formula

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 of $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.

XNPV Example 1a

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.

XNPV Example 1

We get the Present value using XNPV as $16,065.7.

With NPV, we got this Present Value as $15,105.3

The 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 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 the 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 the 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 the 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.

XNPV Example 2a

The next step is to use the discount rate; cash flows range, and date range in the formula. Please note that we have also included cash outflows that we did today in the formula.

XNPV Example 2b

The present Value using the 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.

NPV vs XNPV - Example

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 the 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 the Cost of Capital increases, Net Present value decreases.

This can be seen graphically in the graph below.

NPV vs XNPV - Example a

Using XNPV

Let us now run the same example with the XNPV formula.

NPV vs XNPV - Example b

We note that Net Present Value is positive using XNPV for the 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%.

The below graph depicts the Net Present Value of the Project using XNPV at the various cost of capital.

XNPV as Function of discount rate

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 below-mentioned categories:

Common Errors                                                                                                 
#NUM! Error

  • The dates and values arrays having different lengths
  • The entered dates might be earlier than the initial date
  • In some versions of Excel, I also got #NUM errors when the discount rate was 0%. If you change this discount rate to any number other than 0%, the errors go off. For example, while I worked in the above examples of NPV vs XNPV, I used 0.000001% (instead of 0%) for calculating XNPV.
#VALUE! Error

  • Any mentioned values or rate arguments could be non-numeric;
  • Any dates provided might not be identified as dates in the Excel sheet.

NPV vs XNPV – Video

Recommended Articles

This has been a guide to NPV vs XNPV. Here we discuss the top difference between NPV and XNPV along with excel calculation, examples, common errors. You may also have a look at the following articles –

Reader Interactions

Comments

  1. chaithanya says

    Hii,

    Dheeraj,

    Really I appreciate your knowledge sharing. I learned many things from your articles. It helped so much for my professional carrier. Hope you will continue the same and educate us more with your esteem knowledge in finance.

    • Dheeraj Vaidya says

      Thanks for your kind words!

  2. 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.

  3. 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 self-development. 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!

  4. 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!

Leave a Reply

Your email address will not be published. Required fields are marked *