Updated August 10, 2023
NPV Function (Table of contents)
- Introduction to Excel NPV Function
- NPV Formula in Excel
- Use of NPV Formula in Excel
- Issues with NPV
- How to Use NPV in Excel?
Introduction to Excel NPV Function
For calculating Net Present Value, use Excel’s NPV function, which involves the series of cash flows at a special discount rate. Mostly NPV function is used in financial analysis work where we need to calculate the investment done in any project or assignment by any company. For example, if a company invests in a product for 3 years, we can find the net present value at any rate for each year to know the worth of the total invested amount.
NPV Formula in Excel
The Formula for the NPV function is as follows:
Rate (required) – It is the discount rate suitable for the investment which is used to discount the projected net cash flows to time 0.
Values (required) – Refers to the stream of cash flows that the project is generating. The stream of cash flows must be compatible and equidistant, i.e. the time should be equal between two cash flows.
The Math Behind NPV Formula:
Below is the mathematical formula for calculating an individual cash flow for the present value.
NPV = F / [ (1 + i)^n ]
Where,
- PV – Present Value
- F – Future payment (cash flow)
- i – Discount rate (or interest rate)
- n – the number of periods in the future the cash flow is
Use of NPV Formula in Excel
Most financial analysts don’t calculate the net present value with a calculator; instead, they use the NPV function.
=NPV(discount rate%, series of cash flow)
Step 1: Enter the discount rate in a cell.
Step 2: Enter the cash flows in the series (In consecutive cells).
Step 3: Type “=NPV (“ select the discount rate “,” select the cash flow cells “)”.
The NPV function returns the result as $34.4.
Issues with NPV
First of all, what’s the issue with NPV in Excel? Why do so many people face problems while using it? Well, contradictory to popular belief, the NPV function does not compute the Net Present Value. Instead, it computes the present value of a series of cash flows, but it doesn’t net out the actual cash outflow at zero time period. They must manually subtract this actual cash outflow using the NPV function.
Before we get into an example, let’s get some concepts and understand what NPV means in finance. NPV is directly the difference between value and cost. It implies finding NPV, and we take the present value of a series of future cash flows at a specific discount rate, then simply subtract out our original cost to obtain that stream of cash flows.
How to Use NPV Function in Excel?
This NPV function is very simple and easy to use. Let us now see how to use the NPV Function with the help of some examples.
Let’s take a few examples. Suppose we have the following series of cash flows as shown in the below examples:
Example #1
Suppose we are working on the below dataset on cash inflows and outflows:
The below spreadsheet gives a simple illustration of how the NPV function works in Excel.
The rate arguments applied to the function are stored in cell C11, and the value arguments are stored in cells C5-C9 of the spreadsheet.
The NPV is entered in cell C12.
The NPV formula is applied in cell C12.
When we apply the NPV function on a dataset in the spreadsheet, it gives the result as $1128.45.
In this example, the initial investment of $200 (shown in cell C5) occurred at the end of the first period. This value is considered the first argument (i.e., value1) to the NPV Function.
The above NPV calculation of $1128.45 considers the -$200.00 initial cash outlay in the series of cash flows. The formula used in cell C12 to calculate NPV is:
=NPV(C11,C5:C9)
Example #2
The below spreadsheet shows another illustration of where the first payment is made initially during the first instance and why this payment should be considered in the NPV Function.
The rate is again 10% and is in cell C11, and value arguments like the transactions’ cash flow are between the cells ranging from C5-C9 of the spreadsheet. The NPV function is entered in cell C12.
This time, when we apply the NPV function on the dataset in the spreadsheet, it gives the result as $1241.29.
We notice that the initial investment of $200 (shown in cell C5) was made during the investment at the start of the first instance; this value gets excluded in the arguments of the NPV Function. Adding the first cash flow separately to the NPV result is the appropriate approach instead.
The above NPV calculation of $1241.29 correctly excludes the $200 initially invested cash outlay in the series of cash flows and then netted it out from the result of the NPV Formula. To correctly calculate NPV, we use the following formula in cell C12:
=NPV(C11,C6:C9)+C5
As described in the above example, the NPV (Net Present Value) formula in Excel is based on cash flows in the future. To exclude the first cash flow value from the values arguments and add it to the NPV result separately, they should do so if the first cash flow occurs at the initial stage of the first instance.
Things to Remember
- The NPV investment begins one period ahead of the date of the value1 cash flow and ends with the last cash flow in the list. The future cash flows derive the NPV computation in Excel. If they make the first cash flow at the beginning of the first period, they must add the first value directly to the NPV result, thus eliminating the value arguments. Check the examples below for more information.
Let’s take n as the number of cash flows in the list of values; To calculate the NPV (Net Present Value) formula in Excel, they would use the following:
- If the arguments are provided in a range, and all of the non-numeric values in the range are ignored.
- When providing arguments individually, Excel considers numbers, logical values, blank cells, and text representations of numbers as numeric values. This means that Excel will include these values in the NPV calculation. At the same time, the function ignores other values of a cell that are in text and error form.
- The key difference between PV and NPV functions is that the PV function allows the cash flow to begin at the start or at the end of the period.
- We need to type in transactions, payments, and income values in a correct sequence as NPV functions use the order of the 2nd argument, i.e., value1, value2, value3 … to check the order of cash flows.
- The NVP (Net Present Value) function in the latest versions of Excel can accept up to 254 value arguments, but with Excel 2003, only up to 29 values can be supplied to the function.
Recommended Articles
This has been a guide to NPV Function. Here we discuss the NPV Formula and how to use NPV along with practical examples and downloadable Excel templates-
