Net Present Value (NPV)

Net Present Value (NPV): Definition in Finance

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 represents the discounted values of future cash inflows and outflows related to a specific investment or project.

NPV Formula: How to Calculate NPV in Excel (Step-by-Step)

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.

The Excel formula for XNPV is as follows:

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

How to Interpret NPV: Capital Budgeting Rules

In terms of capital budgeting, here are the 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 net present value 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, and relationship-building) that help rationalize the decision.

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

Step 1. Discount Rate, Initial Investment and Cash Flow Assumptions

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 net present value 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).

Step 2. NPV Calculation Example in Excel (XNPV Function)

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

Upon doing so, we get $17.3m as the net present value.

Step 3. Manual Net Present Value Calculation Example (NPV)

Alternatively, we can also 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.

• Accept or Reject Project?: “Accept”