Skip to content
NPV Calculator

NPV Calculator

net present value calculator

  • NPV Calculator
  • IRR Calculator
  • Blog
  • Toggle search form
NPV IN EXCEL

NPV In Excel: Avoid This 1 Major Mistake

Posted on December 19, 2023December 21, 2023 By NPVCalculator

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

NPV

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.

NPV In Excel

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

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

NPV In Excel

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

npv in excel

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.

npv in excel

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)

NPV in Excel

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

NPV in Excel

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 in Excel

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

NPV in Excel

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 in Excel

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

NPV formula in Excel

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.

NPV in Excel

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

NPV in Excel

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.

NPV in Excel

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

npv = total pv – initial investment

NPV in Excel
NPV in Excel

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.

NPV IN EXCEL

NPV Calculator | IRR Calculator
CAGR Calculator | Reverse CAGR Calculator
Learn Personal Finance

Check out our new venture multipl !

More From The Blog

NPV Calculation In Excel Is Wrong? Why The Numbers Don’t Match!
NPV

NPV Calculation In Excel Is Wrong? Why The Numbers Don’t Match!

NPV of Project: Decide Whether To Do Or Not To Do A Project
NPV

NPV of Project: Decide Whether To Do Or Not To Do A Project

NPV of Perpetuity: Net Present Value of Perpetuity
NPV

NPV of Perpetuity: Net Present Value of Perpetuity

NPV vs IRR: Net Present Value vs Internal Rate of Return
NPV

NPV vs IRR: Net Present Value vs Internal Rate of Return

NPV In Excel: Avoid This 1 Major Mistake
NPV

NPV In Excel: Avoid This 1 Major Mistake

Our Financial Calculator Apps

download mortgage refinance calculator for ios and android
download mortgage refinance calculator for ios and android
download mortgage refinance calculator for ios and android
download cagr calculator for ios and android
download cagr calculator for ios and android
download cagr calculator for ios and android
download reverse cagr calculator for ios and android
download reverse cagr calculator for ios and android
download reverse cagr calculator for ios and android
download irr calculator for ios
download irr calculator for ios
download npv calculator for ios
download npv calculator for ios

Table of Contents

Toggle
  • Method 1: NPV in Excel Without Using Excel’s NPV Function
    • Step 1a. Enter discount rate in a cell.
    • Step 1b. Create the columns for years and cash flows.
    • Step 1c. Set up the formula for present values in another column
    • Step 1d. Calculate the Sum of all Present Values
  • Step 2. Deduct the Initial Investment From Total PV
  • NPV in Excel: Problems With Excel’s NPV Function/Formula
  • Method 2: NPV in Excel- How To Use the NPV Function in Excel Correctly
  • Conclusion
  • More From The Blog
  • NPV Calculation In Excel Is Wrong? Why The Numbers Don’t Match!
  • NPV of Perpetuity: Net Present Value of Perpetuity
  • NPV In Excel: Avoid This 1 Major Mistake
  • NPV of Project: Decide Whether To Do Or Not To Do A Project
  • NPV vs IRR: Net Present Value vs Internal Rate of Return
  • Our Financial Calculator Apps
NPV

Post navigation

Previous Post: NPV of Project: Decide Whether To Do Or Not To Do A Project
Next Post: NPV Calculation In Excel Is Wrong? Why The Numbers Don’t Match!
  • NPV Calculator: Calculate Net Present Value Online
  • IRR Calculator: Calculate Internal Rate of Return Online
  • Blog
  • NPV vs IRR: Net Present Value vs Internal Rate of Return
  • NPV Calculation In Excel Is Wrong? Why The Numbers Don’t Match!
  • NPV In Excel: Avoid This 1 Major Mistake
  • NPV of Project: Decide Whether To Do Or Not To Do A Project
  • NPV of Perpetuity: Net Present Value of Perpetuity

Reach Out To Us

contact@finlightened.com

With ❤️ From Luxembourg

Sumeet Sinha

  • Sitemap Index
  • Post Sitemap
  • Page Sitemap

Navigation

  • NPV Calculator: Calculate Net Present Value Online
  • IRR Calculator: Calculate Internal Rate of Return Online
  • Blog

Recent Posts

  • NPV vs IRR: Net Present Value vs Internal Rate of Return
  • NPV Calculation In Excel Is Wrong? Why The Numbers Don’t Match!
  • NPV In Excel: Avoid This 1 Major Mistake
  • NPV of Project: Decide Whether To Do Or Not To Do A Project
  • NPV of Perpetuity: Net Present Value of Perpetuity

Copyright © 2025 NPV Calculator.

Powered by PressBook WordPress theme