## Excel NPV FunctionWhile 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
## 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 ## 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.
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
. But if the initial cash flow (first investment) occurs at the beginning of the first period, incorporate any of the following formulas.*function works with cash flow values that occur at the end of each period*- 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:
- 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:
- 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.
- 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:
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:
- 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:
- 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.
- 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:
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
Follow the below steps to determine and compare the results of net present value when calculated manually and using the NPV formula:
- 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:
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 ExcelThe 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 NPVIn 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:
Next TopicDate to month in excel |