Net present value (NPV) helps gauge the profitability of an investment. This guide explains how to calculate net present value by using a discount rate along with future income and payments.
NPV is a helpful metric because it acknowledges that money’s value can change over time because of things like inflation. A positive NPV suggests an investment may do better than other options. Excel’s built-in NPV function can calculate an investment’s NPV. This guide offers a step-by-step walkthrough on how to find the net present value in Excel.
Calculating Net Present Value in Excel
The most straightforward way to calculate NPV in Excel involves directly using the built-in NPV function. This method is efficient when you have a series of cash flows at regular intervals.
Step 1: Identify the discount rate for calculating the net present value.
Step 2: List all inflows and outflows pertaining to your project or investment. Write outflows as negative values.
Step 3: Select an empty cell and enter the NPV function. For the first argument, use the cell reference for the discount rate.
=NPV(rate, value1, [value2], ...)
Provide the cash inflows and outflows for the remaining arguments.
Step 4: Press Enter to evaluate the function. The NPV function will display the net present value of your investment.
Using the Formula Manually
Alternatively, you can calculate the NPV manually using the formula within Excel if you want greater control over each cash flow’s discounting.
NPV Function Anatomy
The NPV function’s syntax is:
=NPV(rate,value1,[value2],...)
-
NPV()is the function that calculates the present value of cash flows at a given discount rate. -
rateis the discount rate over a single period. -
Value1is the first cash flow value. It is a required argument. -
Additional arguments can represent more cash flows, entered in the correct sequence. The investment begins one period before the
value1cash flow and ends with the last cash flow argument. If the first cash flow occurs at the start of the first period, the first value must be added to the NPV result and not included in the values arguments.
Example
To calculate net present value, the following values are needed:
- Discount rate – The rate used to discount future cash flows to their present value.
- Cash Flow Values – The inflows and outflows of a project or investment.
Consider a project with these expected cash flows, including the initial investment cost and a yearly income of $30,000 over four years.
| Year | Cash Flow |
|---|---|
| 0 | -$80,000 |
| 1 | $30,000 |
| 2 | $30,000 |
| 3 | $30,000 |
| 4 | $30,000 |
With a discount rate of 10%, assuming income is worth 10% less each year, the formula is:
=NPV(B1, B3,B4,B5,B6,B7)
Where B1 contains the discount rate and B3:B7 contain the cash flows.
The result, $1,854, is the net present value of the project. A positive NPV suggests the investment is viable because the present value of inflows exceeds outflows. Conversely, a negative NPV suggests the investment may not be financially sound.
By using the NPV function in Excel, you can quickly assess the profitability of potential investments.