Net Present Value (NPV)

In This Article

• In the context of corporate finance, how is the net present value (NPV) defined?
• How can the NPV of a potential project or investment be interpreted?
• What are the inputs for the XNPV function in Excel?
• How does using XNPV in Excel differ from the NPV function?

Net Present Value (NPV) Formula

The present value (PV) of a stream of cash flows represents how much the future cash flows are worth as of the current date.

Since a dollar received today is worth more than a dollar received on a later date (i.e. the “time value of money”), the cash flows must be discounted to the present date using the appropriate rate of return, which is known as the discount rate.

The net present value (NPV) represents the discounted values of future cash inflows and outflows related to a specific investment or project.

Interpreting the Net Present Value (NPV)

As some general rules of thumb to follow:

• If NPV > 0: Accept (Profitable)
• If NPV = 0: Indifferent (Break-Even Point)
• If NPV < 0: Reject (Unprofitable)

If the NPV is positive, the likelihood of accepting the project is greater. But note that the following guidelines mentioned earlier are generalizations and are not meant to be rigid rules.

For example, a project could be unprofitable yet still be accepted by management if there are other non-monetary considerations (e.g. intangible factors such as marketing/publicity, relationship-building) that help rationalize the decision.

Net Present Value (NPV) Calculator – Excel Template

We’ll now move to a modeling exercise, which you can access by filling out the form below.

Submit

Email provided

Your Download is Ready

Submitting ...

XNPV Excel Formula

To calculate the net present value (NPV) in Excel, the XNPV function can be used.

Unlike the NPV function, which assumes the time periods are equal, XNPV takes into account the specific dates that correspond to each cash flow.

Therefore, XNPV is a more practical measure of NPV, considering cash flows are usually generated at irregular intervals.

XNPV Excel Function

The Excel formula for XNPV is as follows:

• “=XNPV(Rate, Values, Dates)”

Where:

• Rate = The appropriate discount rate based on the riskiness and potential returns of the cash flows
• Values = The array of cash flows, with all cash outflows and inflows accounted for
• Dates = The corresponding dates for the series of cash flows that were selected in the “values” array

Calculating NPV with XNPX Function

Let’s suppose that we’re attempting to decide whether to accept or decline a project.

The initial investment of the project in Year 0 amounts to $100m, while the cash flows generated by the project will begin at $20m in Year 1 and increase by $5m each year until Year 5.

The discount rate, date, and cash flow assumptions for calculating the NPV are listed below:

• Discount Rate: 10%
• Year 0 (8/31/21): -$100m
• Year 1 (12/31/21): $20m
• Year 2 (12/31/22): $25m
• Year 3 (12/31/23): $30m
• Year 4 (12/31/24): $35m
• Year 5 (12/31/25): $40m

The period from Year 0 to Year 1 is where the timing irregularity occurs (and why the XNPV is recommended over the NPV function).

Since we have all of the necessary inputs, we can enter them into the formula presented earlier.

Upon doing so, we get $17.3m as the NPV.

NPV Manual Excel Calculation

Alternatively, we can manually discount each of the cash flows by dividing the cash flow by (1 + discount rate) ^ the number of periods.

• Year 0: -$100m / (1+10%)^0.0 = -$100.0m
• Year 1: $20m / (1+10%)^0.3 = $19.4m
• Year 2: $25m / (1+10%)^1.3 = $22.0m
• Year 3: $30m / (1+10%)^2.3 = $24.0m
• Year 4: $35m / (1+10%)^3.3 = $25.5m
• Year 5: $40m / (1+10%)^4.3 = $26.5m

In Excel, the number of periods can be calculated using the “YEARFRAC” function and selecting the two dates (i.e. beginning and ending dates).

If we calculate the sum of all cash inflows and outflows, we get $17.3m once again for our NPV.

In closing, the project in our example exercise would likely be accepted given its positive calculated NPV.