The **NPV **Function is an Excel Financial function that calculates the **Net Present Value (NPV)** for a series of cash flows and a given discount rate. The **NPV **function is useful in financial modeling to determine the value of an investment or to understand the feasibility of a project.

The following image is an overview of the uses of the Excel **NPV **function.

Please go through the whole article to the end to learn more about the Excel **NPV **function. In this article, you will learn how to use the **NPV **function in practical life with 5 simple examples and proper illustrations.

**Download Practice Workbook**

You can download the free practice **Excel** workbook from the following button.

**What is the Net Present Value (NPV)?**

**The net present value (NPV)** method is a thorough methodology to determine whether or not a given project is financially viable. The study of numerous financial issues is required to calculate **NPV:** **cash flows**, **the time value of money**, **the discount rate over the project’s duration**, **terminal value**, and **salvage value**.

**In simple terms:**

**NPV = (Expected Future cash flows converted to today’s value) – (Cash invested today)**

We can calculate the future value from a present value using the following formula:

*Here, Future Value is expected net cash inflow or outflows, r is the discount rate, t*

*is the number of*

**time periods**.**As a simple example, $100 invested today (present value) at a rate of 10 percent (r) for 1 year (t) will increase to:

As we are trying to find the net present value of the future projected cash flows, the current formula can be rearranged as

Just think in a reverse way. How much should someone invest today to get $110 in 1 year with a 10 percent interest rate? Using the above formula:

This can be put in another way, $100 is the present value of $110 that is expected to be received in the future (one year later) considering 10 percent returns.

This basic strategy is used by **NPV** to bring all future cash flows to a single point in time. The expanded formula for **NPV** is as follows:

Here * FV_{n} , r_{n }, t_{n}* indicate the expected future value, applicable rates, and time periods for year n. Value of n=0 indicates initial investment.

**Introduction to Excel NPV Function**

**Function Objective:**

The **Excel NPV function** is a financial function that calculates the net present value **(NPV)** of an investment using a discount rate and a series of future cash flows.

**Syntax:**

**=NPV (rate, value1, [value2], …)**

**Arguments Explanation:**

Argument | Required/Optional | Explanation |
---|---|---|

rate |
Required | Discount rate over one period. |

value1 |
Required | First value(s) representing cash flows. |

value2 |
optional | Second value(s) representing cash flows |

**Return Parameter:**

Returns Net Present Value.

**How Does the NPV Function Work in Excel?**

**Excel NPV function **can be a bit tricky to use depending on the implementation of the function. Let’s compare the results of calculating **NPV** manually and with an **Excel NPV function**.

Calculating **NPV** using **Excel** formula and using manual calculation, we get the same results. Note that we calculate the **Present Value(PV)** of the initial investment too since the initial investment cost is after 1 year, so it is also discounted. But what if the initial investment occurs at the start of the first period?

In this case, the manual calculation and **Excel NPV function** yield different results:

Does this mean we cannot rely on the NPV formula in Excel and have to calculate net present value manually in this situation? Obviously, not! You will see how we can modify the **Excel NPV function** in the examples below.

**3 Easy Examples with NPV** Function** in Excel**

**Excel NPV function** is used to calculate the net present value of an Investment. Let’s see several examples of **Excel NPV function** and how we can use them according to our needs.

**Example 1: Basic Use of NPV Function in Excel for NPV > 0 with Initial Investment**

Assume that we have the following data on cash inflows and outflows:

The required rate of return is 10%. To calculate the NPV, follow the steps below:

**Steps:**

- Select
**Cell C14.** - Type the following formula in the formula bar and press
**Enter**.

`=NPV(C12,C6:C10)+C5`

We get the following result:

A positive net present value indicates that the project’s rate of return exceeds the discount rate. In other words, it’s better to invest your money in this project than to put your money in a savings account at an interest rate of 10%.

**Example 2: NPV < 0 with Initial Investment **

Let’s assume, the required rate of return for the following project is 15%.

Now, we have to calculate the NPV of this project and decide whether the investment is viable or not. Follow the steps below to find out:

**Steps:**

- Select
**Cell C14.** - Type the following formula in the formula bar and press
**Enter**.

`=NPV(C12,C6:C10)+C5`

We get the following result:

A negative net present value indicates that the project’s rate of return is less than the discount rate. So, for the above example, it’s better to invest your money in a savings account at an interest rate of 15% than to invest in this project.

**Example 3: Net Present Value with Investment after the First Year Using NPV Function**

In this example, we consider a case where the investment occurs at the end of period 1. Again, the required rate of return is 10%.

Follow the steps below to calculate the **NPV:**

** **

**Steps:**

- Select
**Cell C14.** - Type the following formula in the formula bar and press
**Enter**.

`=NPV(C12,C5:C10)`

We get the following result:

This positive net present value indicates that the project’s rate of return exceeds the return from a savings account at an interest rate of 10%.

**Note:**

The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, you must add the first value to the NPV result, not included in the values arguments.

**Errors that Occur While Using the Excel NPV Function**

Many mistakes can be made while calculating net present value in Excel due to a very particular construction of the NPV function. We will demonstrate some common types of error below:

**1. Irregular Interval Error**

All cash flow periods are supposed to be equal in the Excel NPV formula. If you provide various intervals such as years and quarters or months in the single formula, the net present value can be misleading.

For the above example, all the periods should be a year as shown below:

**2. Missing Periods Or Cash Flows**

**The NPV function** of Excel ignores blank spaces and omitted periods. It requires consecutive months, quarters, or years are required to compute properly.

Excel will ignore the blank cells as a cash flow.

If a period has no cash flow, zero value is essential in that cell to make the NPV formula consider no cash flow in that time period.

**3. Wrong Discounting Rate **

An appropriate rate of return should be provided depending on the time span.

It is the user’s responsibility to provide an annual rate of return in case of annual cash flow and a quarterly rate of return in case of quarterly cash flow and so on.

**4. Incorrect Rate Format**

The rate of return must be expressed as a percentage or decimal figure. If we provide the rate equals 10, excel counts it as 1000% which was not our intention.

The ten percent rate, for example, might be specified as 10% or 0.1.

**Things to remember**

- Arguments other than numerical, or function with a numerical output-input will result in an error.
- The input chronology matters for the series of cash flows. The
**NPV function**assumes that payments are spaced on equal periodic payments. - The
**NPV function**and**IRR function**(Internal Rate of Return) are closely related.**IRR**is the rate for which the**NPV**equals zero. - Arrays that are used as input will only have their numerical values evaluated. All other values in the array will be ignored.
- The
**NPV**investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The**NPV**calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the**NPV**result, not included in the values arguments.

## Conclusion

This article explained in detail how to use the **NPV function** in **Excel** with examples. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.