NPV in Excel

Net present value, or NPV, is a commonly-used tool in finance. In essence, it helps you calculate the present value of future payments. It’s often utilized for budgeting, planning, and project analysis studies. The calculations are tricky to perform manually, but spreadsheets make it easy. Let’s learn how to find NPV in Microsoft Excel.

How to Find NPV in Excel

Analyzing the value of future cash flows is an essential part of any budgeting or forecasting process. It determines the viability of investments, and their potential value. It helps you determine whether to proceed with a given investment, or adopt an alternative. For example, you may be able to invest in a project with a projected 15% annual return, or another instrument with a guaranteed 10% return. Which is better? NPV helps you find out.

With a few steps, you can have Excel do the work for you. Consider option A, the 15% projected annual return. Initially, you’ll have a cash outflow that constitutes your investment. We’ll use $100. At the end of year 1, you’d expect to have $115.00. At the end of year 2, $132.25, and at the end of year 3, $152.09.

Outline these in a pair of Excel columns as you see below.

Then, you’ll need to input the discount rate. Simply put, this is option B, your alternative investment, which here has the guaranteed 10% rate of return. Type 10% into a cell separate from the table you built.

The NPV calculation itself has two outcomes: positive and negative. You’re hoping to see a positive value here. A positive NPV tells you that the 15%-return project should be more valuable than the alternative investment. This really helps you capture risk and make smarter financial decisions.

Now, it’s time to start building the NPV formula. To get started, click into an empty cell. Begin by typing an = sign. This is standard practice in Excel. It indicates that you are inputting a formula into a given cell. Continue by typing NPV, then (. Your formula, so far, looks like this:

=NPV(

Excel then asks for multiple inputs. The first is your discount rate. Since you’ve already inputted this rate into cell E1, click on cell E1. Next, add a comma. Excel now needs your range of cash flows. These are contained in cells B3:B5, so click and drag to select that range. Be sure to leave out the initial investment in cell B2. 

Then, close the parentheses. Your formula reads:

=NPV(E1,B3:B5)

Finally, you’ll need to add your initial investment back into the formula. Type +, and then click on the cell containing the initial amount. Here, that’s cell B2.

Your complete NPV formula reads:

=NPV(E1,B3:B5)+B2

Hit Enter on your keyboard, and Excel returns the net present value of the cash flows: $228.11. 

Now, it’s worth calculating the NPV for the alternative investment with the 10% guaranteed return. For the same $100 initial investment, you’d expect $110.00 at the end of year 1, $121.00 at the end of year 2, and $133.10 at the end of year 3. Swap out those numbers in your spreadsheet, leaving your formula intact.

Then, don’t forget that 15% now becomes your discount rate. You’ll see the value in cell B7 update automatically to find the NPV of the 10%-return investment. This value is $174.66, compared to $228.11 for the 15% rate. Of course, you’ll want to choose the investment with the higher NPV. Option A at 15% annually is your best option here.

As you can see, Excel makes complex financial calculations like NPV a breeze. It only takes a few clicks, and you’re on the way to making better and smarter financial decisions.