Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More...Less
This article describes the formula syntax and usage of the NPV function in Microsoft Excel.
Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
NPV(rate,value1,[value2],...)
The NPV function syntax has the following arguments:
-
Rate Required. The rate of discount over the length of one period.
-
Value1, value2, ... Value1 is required, subsequent values are optional. 1 to 254 arguments representing the payments and income.
-
Value1, value2, ... must be equally spaced in time and occur at the end of each period.
-
NPV uses the order of value1, value2, ... to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence.
-
Arguments that are empty cells, logical values, or text representations of numbers, error values, or text that cannot be translated into numbers are ignored.
-
If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are 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. For more information, see the examples below.
-
If n is the number of cash flows in the list of values, the formula for NPV is:
-
NPV is similar to the PV function (present value). The primary difference between PV and NPV is that PV allows cash flows to begin either at the end or at the beginning of the period. Unlike the variable NPV cash flow values, PV cash flows must be constant throughout the investment. For information about annuities and financial functions, see PV.
-
NPV is also related to the IRR function (internal rate of return). IRR is the rate for which NPV equals zero: NPV(IRR(...), ...) = 0.
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
| 0.1 | Annual discount rate | |
| -10000 | Initial cost of investment one year from today | |
| 3000 | Return from first year | |
| 4200 | Return from second year | |
| 6800 | Return from third year | |
| Formula | Description | Result |
| =NPV(A2, A3, A4, A5, A6) | Net present value of this investment | $1,188.44 |
| 0.08 | Annual discount rate. This might represent the rate of inflation or the interest rate of a competing investment. | |
| -40000 | Initial cost of investment | |
| 8000 | Return from first year | |
| 9200 | Return from second year | |
| 10000 | Return from third year | |
| 12000 | Return from fourth year | |
| 14500 | Return from fifth year | |
| Formula | Description | Result |
| =NPV(A2, A4:A8)+A3 | Net present value of this investment | $1,922.06 |
| =NPV(A2, A4:A8, -9000)+A3 | Net present value of this investment, with a loss in the sixth year of 9000 | ($3,749.47) |
Top of Page
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
View Course
()
Net present value (NPV) profile of the company refers to the graph which shows the net present value of the project under consideration with respect to the corresponding various different rate of the discount where net present value of the project is plotted on the Y-axis of the graph and the rate of the discount is plotted on the X-axis of the graph.
The relationship between the discount rate and NPV is inverse. When the discount rate is 0%, the NPV profile cuts the vertical axis. NPV profile is sensitive to discount rates. Higher discount rates indicate the cash flows occurring sooner, which are influential to NPV. The initial investment is an outflow as it is the investment in the project.
You are free to use this image on your website, templates, etc, Please provide us with an attribution link
Article Link to be HyperlinkedFor eg:
Source: NPV Profile (wallstreetmojo.com)
Components
The following are components of NPV Profile
- Internal Rate of Return (IRR): The rate of return, which does the projects NPV is zero, is called as IRR. It is one of the important factors while considering a profitable project.
- Crossover Rate: When two projects have the same NPV, i.e., when the NPV of two projects intersects each other, it is called a crossover rate.
If two projects are mutually exclusive, the discount rate is considered as the deciding factor to differentiate between the projects.
Steps to Prepare the NPV Profile
Consider there are two projects. To build an NPV profile, these steps have to be considered
You are free to use this image on your website, templates, etc, Please provide us with an attribution link
Article Link to be HyperlinkedFor eg:
Source: NPV Profile (wallstreetmojo.com)
- Step 1 – Find the NPV of both projects at 0%.
- Find the NPV for project A
- Find the NPV for project B
- Step 2 – Find the Internal Rate of Return (IRR) for both projects.
- Find the IRR for Project A
- Find the IRR for Project B
- Step 3 – Find the crossover point
- If the NPV is greater than zero, than accept the investment
- If the NPV is lower than zero, than reject the investment
- Of the NPV is equal to the investment, then it is marginal
These rules are applicable when it is assumed that the company has unlimited cash and time to accept all projects that come in their way. However, it is not true in the real world. The companies usually have limited resources and have to select a few of the many projects.
Examples
Let us understand this better by looking at an example.
Consider project A which requires an initial investment of $400 million. This project is expected to generate cash flows of $160 million for the next four years.
Consider another project B, which requires an initial investment of $400 million and no cash flows in the next three years and $800 million in the last year.
To understand how sensitive these cash flows are to the cash flows, let us consider multiple discount rates – 0%, 5%, 10%, 15%, 18.92%, and 20%
The net present value of these cash flows can be determined using these rates. This is shown below in a tabular format below.
| 0% | $240 | $400 |
| 5% | $167.35 | $258.16 |
| 10% | $107.17 | $146.41 |
| 15% | $56.79 | $57.40 |
| 18.92% | $22.80 | 0 |
| 20% | $14.19 | $14.19 |
Another important point to be considered is that if Project Y is taken up at higher rates, than the project will have a negative NPV and therefore be unprofitable
(Please note that there are various ways to calculate NPV(Net Present Value) Profile like the formula method, Financial calculator, and excel. The most popular method is the excel method)
Plotting this NPV Profile on a graph will show us the relationship between these projects. Using these points, we can also calculate the crossover rate, i.e., the rate at which the NPV of both projects is equal.
The following graph is the NPV profile of project A and Project B.
As discussed above, somewhere around 15% is the crossover rate. This is depicted in the graph where the two lines of Project A and Project B meet.
For Project B, 18.92% is the rate that makes the NPV of the project zero. This rate is known as the internal rate of return. As in the graph, this is where the line crosses the X-axis.
Looking at the different NPV (Net Present Value) profile values, it is conveyed that Project A performs better at 18.92% and 20%. On the other hand, Project Y performs better at 5%, 10% as well as 15% as the discount rate increases the NPV declines. This is also true in the real world when the discount rate increases, the business has to put more money into the project; this increases the cost of the project. The steeper the curve, the more the project is sensitive to interest rates.
Consider a scenario where there are two projects which are mutually exclusiveMutually Exclusive Projects is a term that is commonly used in the capital budgeting process where companies choose a single project based on certain parameters from a set of projects where acceptance of one project results in rejection of the other projects.read more. In this case, the discount rate becomes the deciding factor. In our above example, when the rates are lower, project B performs better. Lower rates are to the left of the crossover rate.
On the other hand, project A performs better at higher rates. That is on the right side of the cross over-rate
Where are NPV Profiles Used?
NPV(Net Present Value) profiles are used by the companies for capital budgeting. Capital budgetingCapital budgeting is the planning process for the long-term investment that determines whether the projects are fruitful for the business and will provide the required returns in the future years or not. It is essential because capital expenditure requires a considerable amount of funds.read more is the process that the business uses to decide which investments are profitable. The motive of these businesses is to make profits for their investors, creditors, and others. This is only possible when the investment decisions they make results in increasing the equity. Other tools used are IRR, profitability indexThe profitability index shows the relationship between the company projects future cash flows and initial investment by calculating the ratio and analyzing the project viability. One plus dividing the present value of cash flows by initial investment is estimated. It is also known as the profit investment ratio as it analyses the project's profit.read more, payback period, discounted payback periodThe discounted payback period is when the investment cash flow paybacks the initial investment, based on the time value of money. It determines the expected return from a proposed capital investment opportunity. It adds discounting to the primary payback period determination, significantly enhancing the result accuracy.read more, and accounting rate of return.
The net present valueNet Present Value (NPV) estimates the profitability of a project and is the difference between the present value of cash inflows and the present value of cash outflows over the project’s time period. If the difference is positive, the project is profitable; otherwise, it is not.read more mainly measures the net increase in the company’s equity by working on a project. It is essentially the difference between the present value of cash flows and the initial investment based on the discount rate. The discount rate is mainly decided on the basis of the debt and equity mix used to finance the investment and to pay for the debt. It also incorporates the risk factor, which is inherent in the investment. Projects with positive NPV profile are considered the ones which maximize the NPV and are the ones selected for investment.
Recommended Articles
This has been a guide to NPV Profile. Here we discuss the NPV Profile components, rules for acceptance, and rejection along with practical examples and uses. You can learn more about financing from the following articles –