NPV In Excel: Avoid This 1 Major Mistake

Calculation of NPV in Excel is convenient and confusing at the same time. Convenient because the calculations are easier in a spreadsheet such as Excel. Confusing because the inbuilt formula for NPV is not very intuitive (as of December 2023).

Let’s see how we can set up the worksheet to calculate NPV in Excel.

There are 2 ways of computing NPV in Excel – Method 1: Without using Excel’s NPV function and Method 2: Using Excel’s NPV function (while avoiding a major mistake)

Method 1: NPV in Excel Without Using Excel’s NPV Function

Conceptually we can break down calculation of NPV in 2 steps.

Step 1: Calculate the Present Value of all future cash flows

Present Value = Future Cash Flow / (1 + Discount Rate) ^ Year

Step 2: Deduct the initial investment from the Total Present Value to get the NPV

NPV = Sum of Present Values – Initial Investment

Let’s get started.

Step 1a. Enter discount rate in a cell.

Let’s say the discount rate is 10%.

Pro tip: in Excel you can rename a cell by clicking on the top left section. Here I have renamed the cell C2 as ‘discount’ for easier reference in all formulas.

Step 1b. Create the columns for years and cash flows.

Let’s assume we are forecasting the cash flows for 10 years.

In two columns we can enter the values for years and cash flows.

Year 0, 1, 2, 3…

Cash Flow in Year 0, 1, 2, 3….

Step 1c. Set up the formula for present values in another column

To compute the present value of a future cash flow, we can set up the formula in the cell. In my case, the formula looks like this. You will realize that renaming the cell discount rate will come in handy while dragging and copying the formula all along the column.

=C6/(1+discount)^B6

Once formula is entered, copy and paste the same formula in all cells or click and drag to copy the formula in the entire column.

Now we have the present values of all future cash flows in the next 10 years.

Step 1d. Calculate the Sum of all Present Values

To take the sum of all the present values, set up a new cell with the ‘sum’ formula by selecting all the cells with the present values of cash flows. In my case the present values are in cells D6 to D15, so my formula looks like this:

=sum(D6:D15)

I get the total present value = $32,941.99 or approximately $32,942

Step 2. Deduct the Initial Investment From Total PV

Let’s say the initial investment for this project is $20,000.

To calculate the NPV in Excel, we subtract the initial investment of $20,000 from the total PV of $32,942

NPV = Total PV – initial investment

NPV = 32,942 – 20,000 = 12,942

NPV in Excel: Problems With Excel’s NPV Function/Formula

Many people make the mistake of setting up the yearly cash flows like this: initial investment in year 0 with a negative sign in the first cell, and then all the cash flows for year 1, 2, 3 and so on..

Then they use Excel’s inbuilt NPV formula to calculate the NPV.

= NPV(discount rate, cash flow 1, cash flow 2, cash flow 3…..)

While intuitively it looks correct, the NPV formula does not assume that the initial investment happens at time t=0 or in year 0, it assumes the investment happens at the end of the first year.

So, if you this is not your case, it is better to not use the NPV formula directly.

In addition, there’s another major flaw in the formula, see this article on why NPV in Excel is wrong!

However, you can still use the NPV formula in Excel to simplify your workflow. See below.

Method 2: NPV in Excel- How To Use the NPV Function in Excel Correctly

We can use the NPV formula to calculate the sum of all present values.

Yes, we can use the NPV formula to calculate Total PV like this:

Input all the yearly cash flows in a column, starting from year 1.

Then use the NPV formula to compute the total PV

=npv(discount rate, cash flow 1, cash flow 2….)

To confirm, compare the total PV achieved here = 32,942 is the same as the total PV we computed in the Method 1, step 1d.

Now that we have the total PV, we can subtract the initial investment to get the NPV.

npv = total pv – initial investment

Here, we get the NPV = 12,941.99 or approximately 12,942.

To confirm, this is the same answer we got in Method 1, step 2.

Conclusion

There are 2 methods to compute NPV in Excel as shown above. However, the users must be aware of the limitations and assumptions of Excel’s inbuilt NPV() function as shown above. However, the inbuilt NPV() function can be used to compute the total PV, and that can speed up the calculation process.