Excel NPV Function
While making any investing or signing off a financial deal everyone prefers to do analysis that indicates whether the deal is going to be profitable or not. To calculate this, financial analysts use the Net present value. It is an important financial that compares and notifies you whether the cash flow potentially obtained in the future is worth less than the same amount of money present with you now. Net present value discounts the amount of money predicted in the future back to the present to display their current worth.
Microsoft Excel has inbuilt function for calculating NPV, but its implementation can be challenging especially for people who have little experience in financial modeling. In this tutorial, we will cover the brief insight of NPV function, including its definition, the steps to calculate the number of periods to return the loan, or the number of investments needed to reach the target amount, various examples, situations where NPV may not work in Excel!
What is Excel NPV?
The Excel Net present value or NPV function is used to determine the value of a series of cash flows over the complete life of a project discounted to the present.
In other words terms, NPV can be defined as the present value of future cash flows less the initial investment cost. The Excel NPV function is tricky for many users as its implementation is not as normal. The NPV formula in its pure form only returns the correct output of you providing the initial cost one period from now, not today. To find the present value of uneven cash flows for today, you need to adjust the formula (we will discuss that later in this tutorial). One easy technique is excluding the initial investment cost from the values argument and further deducting the NPV formula.
Syntax
Parameters
- Rate (required) – This parameter represents the discount amount or rate of interest over one period. It must be provided as percentage or a corresponding decimal number.
- Value1, [value2], …- This parameter represents the numeric values signifying a series of regular cash flows. The first value is required whereas the lateral values are optional. In the latest versions of Excel 2007 to 2019, this argument accepts up to 254 numeric values. If you are working in Excel 2003 or much older version, you can only supply up to 30 arguments.
Points to Remember about NPV Function
Kindly have a look at the below given point to efficiently utilize the NPV function in your worksheets and bypass common mistakes in Excel:
- The Excel NPV function works with cash flow values that occur at the end of each period. But if the initial cash flow (first investment) occurs at the beginning of the first period, incorporate any of the following formulas.
- NPV(rate, values) + initial cost
- NPV(rate, values) * (1+rate)
- The supplied values must be in chronological order and equally spaced in time.
- Money that you pay (outflow of the amount) is represented by negative numbers, and the amount you receive unlike your earnings is represented by positive numbers.
- Make sure to provide only numerical values in its parameters. It throws a #VALUE! error if any of the arguments specified in your PV formula is non-numeric. To fix this error in Excel, always ensure that all the parameters are numeric and all the specified numbers are not formatted as text.
Examples
Example 1: Calculate the net present value manually and later using the Excel NPV formula for the data given in the below table.
Follow the below steps to determine and compare the results of net present value when calculated manually and using the NPV formula:
Step 1: Add two helper row at the bottom of table
- Put your cursor below the table and add your first helper row, i.e., “NPV Manual”. In this row, we will manually find the Net Present value by applying PV and later calculating the Sum for the given data.
- Add the second helper row, i.e., “NPV Excel”. We will calculate the Net Present value using the NPV function in this row.
Refer to the below image:
Step 2: Manually calculate the Net Present value
- Firstly, to manually calculate the Net Present Value, we will apply the PV formula. So, move to the next cell of your helper row (beneath Cash row) and start typing the formula shown below:
- Drag the formula to the subsequent rows. It will automatically copy the formula to the following cells. It will return the PV value for the given data.
Refer to the below image:
- In the helper row, we will calculate the sum of the fetched PV values. For this, we will use the SUM formula.
- As a result, it will return the Net Present Value for the given data values.
Note: Remember this is the manual way. This is only for understanding. If you want, you can skip this step.
STEP 3: Insert the NPV formula and add its parameter in Excel
- Move to next cell of your helper row and start typing the formula = NPV(
- In the first parameter, we will specify the interest rate per period. In the above table, the rate is mentioned in cell C2. So our formula becomes: =NPV (C2,
- Next, this function will ask for the number2 parameter. This parameter represents the series of regular cash flows. So our formula becomes: =NPV (C2, C4:H4)
The overall formula will look similar to the below image:
STEP 4: NPV will return the output
Press the enter button as soon as you are done typing the formula. Excel will return the output for your NPV formula. As shown below it will return $338.72 as your Net Present Value. As you notice the output for NPV manual and NPV Excel formula is same.
Example 2: What will be the net present value for the given data if you supply the initial investment cost for today wherein no discount applies?
Because you supplied the initial outlay today, no discounting will be applied to it. Therefore in this case the NPV formula becomes little tricky.
Let’s compare the results of net present value when calculated manually and using the NPV formula and yield the result:
Step 1: Add two helper row at the bottom of table
- Put your cursor below the table and add your first helper row, i.e., “NPV Manual”. In this row, we will manually find the Net Present value by applying PV and later calculating the Sum for the given data.
- Add the second helper row, i.e., “NPV Excel”. We will calculate the Net Present value using the NPV function in this row.
Refer to the below image:
Step 2: Manually calculate the Net Present value
- Firstly, we will manually apply the PV formula to calculate the Net Present Value. Since the first investment is done today (as mentioned in the question), no discount will apply. So we won’t be calculating the present value for the initial investment, and it will be added with the sum (of PV values).
- Therefore move to the second cell of your helper row (beneath Cash row) and start typing the formula shown below:
- Drag the formula to the subsequent rows. It will automatically copy the formula to the following cells. It will return the PV value for the given data.
Refer to the below image:
- In the helper row, we simply add all the calculated present values using the SUM formula.
- We will add the initial value (it will get subtracted since it’s a negative value) with the fetched sum.
- As a result, it will return the Net Present Value for the given data values.
Note: Remember this is the manual way. This is only for understanding. If you want, you can skip this step.
STEP 3: Insert the NPV formula and add its parameter
- Move to next cell of your helper row and start typing the formula = NPV(
- In the first parameter, we will specify the interest rate per period. In the above table, the rate is mentioned in cell C2. So our formula becomes: =NPV (C2,
- Next, this function will ask for the number2 parameter. This parameter represents the series of regular cash flows. So our formula becomes: =NPV (C2, C4:H4)
The overall formula will look similar to the below image:
STEP 4: NPV will return the output
Press the enter button as soon as you are done typing the formula. Excel will return the output for your NPV formula. As shown below it will return $338.72 as your Net Present Value.
You will notice the outputs for the NPV manual and NPV Excel are different.
Does this mean we cannot depend on the Excel NPV formula, and we have to calculate net present value manually when the initial investment is made at the start of the first period? Of course, not! The NPV formula will work under this situation but with a little adjustment, as explained in the following example.
Example 3: Calculate the net present value when the initial investment is made at the start of the first period.
Follow the below steps to determine and compare the results of net present value when calculated manually and using the NPV formula:
STEP 1: Insert the customized NPV formula and add its parameter
- Delete the earlier formula in your helper and start typing the function = NPV(
- Add the initial investment in the range of values and multiply the output by (1 + interest rate).
- Therefore the formula becomes: = NPV (rate, values) * (1 + rate)
The overall formula will look similar to the below image:
STEP 2: NPV will return the output
Press the enter button as soon as you are done typing the formula. Excel will return the output for your NPV formula. As shown below it will return $372.59 as your Net Present Value. As you notice the output for NPV manual and NPV Excel formula is same.
Common errors when calculating NPV in Excel
The Excel NPV function have some quite specific implementation and because of this sometimes while working with the NPV function, there are possibilities you might run into an error or a wrong result, and it mostly occurs because of one of the following reasons:
- Irregular or different intervals
By default, the NPV function in Excel considers that all cash flow periods are equal. But if you provide distinct intervals, unlike years and quarters or months, the output for NPV will be wrong because of non-coherent periods.
- Missing cashflows or periods
The Excel NPV function fails to recognize the omitted periods and ignores the empty cells supplied in the arguments. To make this function work correctly, please ensure to supply consecutive months, quarters, or years and provide zero values for time periods with null cash flows.
- Discounting interest rate does not match with given periods
The NPV function in Excel cannot automatically adjust the given interest rate to the supplied time frequencies, for instance, the annual discounting interest rate to monthly cash flows. The user has to supply a correct rate per period manually.
- Incorrect rate format
You can specify the rate parameter as a percentage or decimal number, for instance 13% (13 percent) can also be supplied as .13. If you provide the interest rate as a number 13 (without the % symbol), Microsoft Excel will consider it as 1300%, and it will return an incorrect NPV output.
Difference between PV and NPV
In financial department, both PV (present value) and NPV (net present value) are used to calculate the current value of future cash flows by discounting future amounts to the present. But still they differ from one another in Excel:
| S.NO | PV | NPV |
|---|---|---|
| 1 | PV or Present value represents all future cash inflows for a specified time period. | NPV or Net present value (NPV) signifies the difference between the current value of money inflows and the current value of money outflows. |
| 2 | The PV function in Excel only calculate the cash inflows | The NPV function in Excel counts the cash inflows and also accounts for the initial investment. |
| 3 | The PV function demands cash flows to be consistent over the whole time period of an investment. | The NPV function can calculate uneven (variable) cash flows |
| 4 | The Excel PV excel can work with cash flows that occur at the end or at the beginning of a period. | The Excel NPV function works with cash flow values that occur at the end of each period. |
