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.
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:
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], …)
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.
How to Use NPV Function in Excel: 3 Easy Examples
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.
Download Practice Workbook
You can download the free practice Excel workbook from the following button.
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.
<< Go Back to Excel Functions | Learn Excel
