NPV in Excel

NPV in Excel thumbnail

Net Present Value can be used as an indicator of profitability in shareholder wealth derived from a project. Quite often, analysts use the NPV to rank different ventures in the capital budgeting process.

What is NPV?

Net Present Value represents the discounted future cash inflows minus discounted future cash outflows, or simply the discounted future net cash flows. The formula to calculate NPV is as follows:

NPV=-Outlay +\displaystyle\sum_{t=0}^N \frac{CF_{t}}{(1+r)^t}

The term CF_{t} is the expected net cash flow at time t, N is the projected life of the investment, and r is the discount rate (also known as the opportunity cost of capital). The NPV rule to accept or reject a project is:

  • Accept, if NPV > 0
  • Reject, if NPV < 0

Simply said, a venture adds shareholder value when NPV is above 0 and destroys value when NPV turns out to be a negative figure.

How Do You Calculate the NPV?

First, we have to select the appropriate discount rate (r). After that, we take the initial investment amount (outlay) which is usually a negative figure. The next step is to discount each net cash flow separately, and then sum it all up.

We could take a pen, a piece of paper, and a calculator to deal with NPV the old-fashioned way. However, this may be a rather time-consuming and prone-to-errors task, especially when we have a project with numerous periods and a limited time. That’s why most people prefer using MS Excel to calculate the NPV.

How Do You Calculate NPV in MS Excel?

Suppose that you need to calculate the NPV of a 10-year project with an initial outlay of $280 million. You have already forecast the expected net annual after-tax cash flows for each period as presented below. So, what’s next?

NPV in Excel image1

To derive the NPV figure, we will consider two common approaches performed in Excel.

Method #1

Here, we will demonstrate two approaches to deriving the Net Present Value. The first method follows the standard NPV formula presented above. We roughly break down the process into three steps:

Step 1: Pick the appropriate discount rate for this project.

Let’s say that 6% is chosen as the appropriate discount rate for the venture. We recommend placing it in a separate cell, so you could easily refer to the discount rate when using the formulae.

Step 2: Discount each cash flow separately.

Add a column next to the cash flow table and use the space to discount these cash flows. We do that in column D (as shown below):

NPV in Excel image2

Note that the absolute value of each future cash flow was reduced when we applied the discounting technique. The only exception to this is the $280 million initial outlay that we pay at present.

Step 3: Sum up the discounted cash flows.

By summing up the net discounted cash flows (see column D below), we obtain a Net Present Value of 14.7 $ million. According to the NPV rule, the project should be accepted because 14.7 > 0, and thus it adds shareholder wealth.

NPV in Excel image3

Method #2

The second way to calculate NPV offers a more direct approach, as opposed to the first method. It focuses on the NPV formula embedded in MS Excel. We follow four simple steps:

Step 1: Choose an appropriate discount rate.

We already chose 6% as the discount rate at which the cash flows should be discounted.

Step 2: Find the NPV formula in Excel.

Use the Insert Function to find the NPV formula (circled in blue).

NPV in Excel image4

Next, you click on fx and type “NPV” in the “Search for a function” section. Hit go and double-click on NPV:

NPV in Excel image5

Step 3: Input the right figures.

First, you are asked to enter the discount rate, so you might refer directly to cell C4 (6%). On a side note, you should remember not to discount the value for the null period. So, starting from Year 1, you then input each net annual cash flow in chronological order and press OK.

NPV in Excel image6

Once we’ve inserted the data, we arrive at the present value of all cash flows for periods 1-10, discounted at 6%.

NPV in Excel image7

However, we have not accounted for the initial investment of $280 million yet. This is what we do in the last step.

Step 4: Add the initial outlay.

We simply add the value from cell C8 (-280 $million) to the calculation. Ultimately, both methods yield the same result:

NPV in Excel image8

Beyond Net Present Value

When analysts make investment decisions, they use a comprehensive set of measures to evaluate a venture. Along with IRR, NPV is a widely known technique to estimate the value that a project adds to a given company. Using MS Excel in calculating the Net Present Value makes analysts’ work more efficient, accurate, and less prone to errors. We presented two different methods for obtaining it, but one could easily come up with another arrangement.

As complementary decision criteria, financiers often perform four additional measures:

Make sure you keep reading our articles to find out more on how to apply MS Excel in the capital budgeting process!


If you want an example of NPV in Excel, take a look at our NPV Excel template.

Financial Auditing: Ultimate Guide

Financial auditing entails producing an opinion on the truth and fairness of the financial statements of a company. Only

Financial Auditing: Ultimate Guide

Financial auditing entails producing an opinion on the truth and fairness of the financial statements of a company. Only licensed accounting professionals (auditors) authorized by the relevant governing bodies for the territories they operate in can carry out the auditing of financial statements. Audits were first regulated with the...
Learn More

Clearinghouses and Custodians: Financial Intermediaries 101

Intermediaries are vital for a well-functioning financial system and allow their clients to solve the problems they face more

Clearinghouses and Custodians: Financial Intermediaries 101

Intermediaries are vital for a well-functioning financial system and allow their clients to solve the problems they face more efficiently than they could by themselves. We find several types: Brokers, Exchanges, and Alternative trading systems Dealers and Securitizers Depository Institutions Insurance Companies Arbitrageurs, and Clearinghouses and Custodians We shall...
Learn More

Long and Short Positions: Guide to Wall Street Lingo

Rather than an instance of duration, long and short positions are a reference to haves and have-nots. They touch

Long and Short Positions: Guide to Wall Street Lingo

Rather than an instance of duration, long and short positions are a reference to haves and have-nots. They touch on securities an investor really owns versus securities an investor owes. Basically, the terms “short” and “long” denote what “selling” and “buying” do respectively. Featured: What is a Long Position?...
Learn More