**NPV Formula in Excel (Table of Contents)**

## Introduction to NPV Formula in Excel

NPV function or formula comes under the FINANCIAL category of function in excel. It’s a financial calculation to determine or measure the value of investments made. It calculates the difference between the cash inflow (income) & cash outflow (cash you pay for the investment). NPV function helps out in analyzing the feasibility of a project & time value of money.

Most commonly used function by financial analysts in financial modeling & analysis, equity investment, investment banking, real estate project, project financial assessment & loans & payouts from insurance contracts. NPV function is used as both worksheet & VBA function

**Definition**

NPV (Net Present Value) function in excel calculates or determines the Net Present or actual Value of investment, based on the series of future payments (Negative value) & income (positive values) with reference to supplied discount rate & initial investments made.

**Formula for NPV function:**

Unfortunately, Excel does not define or calculate the NPV function correctly, to calculate NPV correctly in Excel, you should exclude the initial cash outflow (Investment) from your NPV formula and you should add that original investment amount at the end of NPV formula in order to find the actual NPV.

**=NPV (rate, value1, [value2],** **[value3] …) ****+ the initial investment**

or

**=NPV**** (Discount rate, range of values) + the initial investment**

4.5 (801 ratings)

View Course

The NPV function syntax or formula has below-mentioned arguments:

**Values:**It is a series of cash flow which indicates income & series of future cash payments.

**Note:**We should not include the initial investment in value argument of NPV formula, as NPV function or calculation is based on future cash flows.Here negative values are considered as outgoing payments whereas Positive values are treated as incoming payment or income.

With reference to the latest version of Excel, NPV can accept up to 254 value arguments.

**Rate**: It is a discount or interest rate for a specific period of time.

**Note:**Rate argument can be directly entered as 5% in percentage format or it can be entered as decimal value i.e. 0.05 for 5%In the output, Positive or higher NPV value indicates, investment is desirable, take a good decision and it adds more or better value for your investment (profitable project or investment), whereas negative NPV values indicate loss.

### How to Use NPV Formula in Excel?

Let’s look at how the NPV function works in Excel.

#### Example #1 – Finding the Net Present Value

In the below-mentioned example, the Table contains below-mentioned details.

The initial investment is 100000 (the initial investment is expressed in negative value) which is a cash outflow & interest rate is 10%

Series of cash flow: Year 0 -100000

Year 1 20000

Year 2 20000

Year 3 20000

Year 4 20000

Year 5 20000

Year 6 20000

Year 7 20000

Year 8 20000

Year 9 20000

Year 10 20000

I need to find out the NPV (NET PRESENT VALUE) by using NPV FUNCTION.

Now, let’s apply NPV function in cell **“**D16**”. **Select the cell **“**D16**” **where NPV function needs to be applied, click the insert function button (fx) under formula toolbar, the dialog box will appear, Type the keyword **“**NPV**”** in the search for a function box, NPV will appear in select a function box.

Double click on NPV function, A dialog box appears where arguments for NPV function needs to be filled or entered.

i.e. **=NPV (rate, value1, [value2],** **[value3] …) +**** the initial investment**

**Rate:**It is a discount or interest rate for a specific period of time.

**Note:**It can be entered as decimal value i.e. 0.10 for 10% here**Values Argument:**It is a series of cash flow excluding the initial investment with a negative sign at the start of the stream. To select an array or cell reference, click inside cell B6 and you’ll see the cell selected, then select the cells till B15 so that column range will get selected i.e. B6:B15

** **

Click ok, after entering the two arguments. **=**NPV(0.1,B6:B15)

To this formula, **=**NPV (0.1, B6:B15) you need to add the initial investment values to get the correct NPV.

In the above NPV calculation, it correctly excludes the 100000 initially invested cash outlay in the series of cash flows and then results in correct NPV value of 22,891

** **

#### Example #2 – **Investment Based on Highest Returns**

In the below-mentioned table, Suppose I want to select any one investment option from two given options with the same initial investment of 100000.

Here I need to compare NPV value, based on a 9% interest rate for both the project with an initial investment of 100000.

Let’s apply NPV function in cell **“**B12**” **for project 1 &** “**C12**” **for project 2.

Select cell **“**B12**” **where NPV function needs to be applied, Click the insert function button (fx) under formula toolbar, the dialog box will appear, Type the keyword **“**NPV**”** in the search for a function box, NPV function will appear in **Select a function** box

Above step is simultaneously applied in cell **“**C12**” **also.

Double click on NPV function, a dialog box appears where arguments for NPV function needs to be filled or entered

i.e. **=NPV (rate, value1, [value2],** **[value3] …) +**** the initial investment**

**R****ate**: It’s a discount or interest rate for a specific period of time.

**Note:**It can be entered as decimal value i.e. 0.09 for 9% or 9% directly.Similar procedure or steps is followed for project 2 also for entering rate argument.

**Value Argument:**It is a series of cash flow excluding the initial investment (Year 0).

Here the value argument will be B7:B11 (Cash flow from year 1 to year 5). Similar procedure or steps is followed for project 2 also for entering the value argument.

Click ok, after entering the two arguments. ‘**=**NPV(0.09,B7:B11).

To this formula, **=**NPV(0.09,B7:B11) you need to add the initial investment values to get the correct NPV.

**For Project 1**

**=NPV(0.09,B7:B11)+B6 i.e. returns the NPV value 4,045**

**Whereas **

**For project 2**

**=NPV(9%,C7:C11)+C6 i.e. returns the NPV value 4,438**

** **

**Inference:** Based on the NPV, Project 2 or investment is preferable, it has given better NPV compared to project 1.

**Things to Remember About NPV Function in Excel**

- Value Argument in NPV function, which are text value or empty cells or logical values, or representations of numbers or error values that cannot be translated into numbers are ignored.
- NPV function helps out in analyzing the feasibility of a project & time value of money.

### Recommended Articles

This is a guide to NPV Formula in Excel. Here we also discuss How to Use NPV Formula in Excel along with the examples and excel template. You can also go through our other suggested articles to learn more –