How to Use NPV Function in Excel (3 Easy Examples)

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.

Preview of NPV Function in Excel

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  FVn , rn , tn 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], …)

Excel NPV Function Syntax

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.

Excel NPV How Does the NPV Function Work in Excel?

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:

How Does the NPV Function Work in Excel: NPV vs Manual

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:

Basic Use of NPV Function in Excel for NPV > 0 with Initial Investment

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

Basic Use of NPV Function in Excel for NPV > 0 with Initial Investment

We get the following result:

Basic Use of NPV Function in Excel for NPV > 0 with Initial Investment: Output

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%.

Read More: How to Use PV Function in Excel (3 Examples)


Similar Readings


Example 2: NPV < 0 with Initial Investment

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

NPV < 0 with Initial Investment: Excel NPV Function

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.

Read More: How to Use RATE Function in Excel (3 Examples)


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:

Net Present Value with Investment After the First Year

Steps:

  • Select Cell C14.
  • Type the following formula in the formula bar and press Enter.
=NPV(C12,C5:C10)

We get the following result:

Net Present Value with Investment After the First Year

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.

Read More: Excel Financial Function Tips – NPV vs XNPV


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.

Use of Excel NPV Function: Irregular Interval Error

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

Irregular Interval Error


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.

Use of Excel NPV Function: Missing Periods or Cash Flows

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.

Use of Excel NPV Function: Wrong Discounting Rate

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.

Use of Excel NPV Function: Incorrect Rate Format

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

Incorrect Rate Format


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.


Related Articles

Robiul Hossain

Robiul Hossain

Hello, I am Robiul. I’ve completed my BSc in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology (BUET). I have a keen interest in Programming and Data science. I really love to solve problems that help lots of people in their day to day life. Currently, I am working with a highly skilled and motivated Technical Writing team of ExcelDemy. We are committed to bringing you the best possible results of your Microsoft Excel problems. Stay with us and enjoy the best Excel experience.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo