Net Present Value (NPV): Formula and Excel Calculation

Net Present Value (NPV) Formula

As review, 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.

As some general rules of thumb:

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

Excel File Download

Now, let’s move onto an example calculation of the net present value (NPV) in Excel. Just fill out the form below to access the file.

Submit

Email provided

Your Download is Ready

Submitting ...

XNPV Excel Function

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 Formula

The Excel formula for XNPV is as follows:

=XNPV(Rate, Values, Dates)

• 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

NPV Example Calculation Steps

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. Alternatively, we can manually discount each of the cash flows by dividing the cash flow by (1 + discount rate) ^ 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.