Wall Street Prep

Net Present Value (NPV): Formula and Excel Calculation

NPV Formula & Step-by-Step Calculation Example Using XNPV Excel Function

Learn Online Now

Net Present Value (NPV): Formula and Excel Calculation

Key Takeaways
  • 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

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.

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

XNPV Excel Formula

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

NPV Manual

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.

XNPV Calculation

Comments
guest
0 Comments
Inline Feedbacks
View all comments
Learn Financial Modeling Online

Everything you need to master financial and valuation modeling: 3-Statement Modeling, DCF, Comps, M&A and LBO.

Learn More
X

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.