**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 & the 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.

### The formula for NPV function

Below is the formula:

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 the 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**

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

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

**Note:**We should not include the initial investment in the value argument of the NPV formula, as the 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 payments 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 a decimal value, i.e. 0.05 for 5%

In the output, a Positive or higher NPV value indicates that investment is desirable, takes a good decision, and adds more or better value for your investment (profitable project or investment), whereas negative NPV values indicate a 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 the 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 the NPV function in cell **“**D16**”. **Select the cell **“**D16**” **where the NPV function needs to be applied, click the insert function button (fx) under the 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 a decimal value, i.e. 0.10 for 10% here

**Values Argument:**It is a series of cash flows 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 the 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 the 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.

The 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 a decimal value, i.e. 0.09 for 9% or 9% directly.

A similar procedure or steps is followed for project 2 also for entering rate argument.

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

Here the value argument will be B7:B11 (Cash flow from year 1 to year 5). A 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

- 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 & the 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 –

- Excel Evaluate Formula
- Advanced Formulas in Excel
- NPV Function in Excel
- Normal Distribution Formula in Excel

250+ Online Courses | 1000+ Hours | Verifiable Certificates | Lifetime Access

4.9

View Course

Related Courses