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 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. SyntaxParameters
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:
ExamplesExample 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
Refer to the below image: Step 2: Manually calculate the Net Present value
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
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
Refer to the below image: Step 2: Manually calculate the Net Present value
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
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
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 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:
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 noncoherent 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.
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.
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
