This guide will explain how to find the net present value of an investment by using a specific discount rate and a series of future payments and income.
Table of Contents
Net present value (NPV) is a financial metric that you can use to assess the profitability of an investment. It uses expected cash flows and a specific discount rate to calculate the present value.
NPV is a valuable metric since it considers the fact that the value of money could change over time due to factors such as inflation. A positive NPV value indicates that the investment is likely to outperform alternative investment options.
If you wish to calculate the NPV of an investment, you can use Excel’s built-in
NPV function. In this guide, we will provide a step-by-step tutorial on how to find the net present value in Excel.
The Anatomy of the NPV Function
The syntax of the
NPV function is as follows:
Let’s look at each argument to understand how to use the NPV function.
- NPV() refers to our
NPVfunction. This function calculates the present value of a series of cash flows given a specific discount rate.
- rate refers to the rate of discount over the length of a single period.
- Value1 is a required argument that determines the first cash flow to consider in the calculation.
- Users can add additional arguments to represent additional cash flows. Do note that the NPV function must follow the correct sequence.
- When using the
NPVfunction, you should be aware of how it interprets each cash flow argument. The investment begins one period before the date of the value1 cash flow and ends with the last cash flow argument. 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
A Real Example of Calculating Net Present Value in Excel
To calculate the net present value, we’ll need to know two values:
- Discount rate – This rate is used to discount future cash flows into their present value.
- Cash Flow Values – These values refer to the inflows and outflows of a project or investment.
Once we have these values, we can use the
NPV function in Excel to find the net present value of our investment.
Let’s explore a simple example where we’ll need to calculate the net present value of a proposed project.
In the table above, we have the expected cash flows if we were to proceed with a certain project. This includes the initial cost of the investment and the yearly income of $30,000 generated over the next four years.
We’ve also indicated a discount rate of 10%. This discount rate assumes that income is worth 10% less after each succeeding year.
Using this table, we can use the following formula:
After evaluating the formula, we determined that the net present value of our project is $1,854.
A positive NPV indicates that the present value of expected cash inflows exceeds the present value of cash outflows. This suggests that the investment is expected to generate a profit, and the project is potentially financially viable.
Meanwhile, a negative NPV implies that the investment may not be financially sound and the project may not generate sufficient returns to cover costs.
Click on the link below to create your own copy of our sample NPV template.
Head to the next section to read our step-by-step tutorial on how to calculate net present value in Excel.
How to Calculate Net Present Value (NPV) in Excel
- First, identify the discount rate you would like to use for calculating the net present value.
For this example, we’ll set our discount rate to 6%.
- Next, list down all the inflows and outflows related to your project or investment. Outflows must be written as a negative value.
- Select an empty cell and type the
NPVfunction. For the function’s first argument, enter the cell reference containing the discount rate you would like to use.
Provide the cash inflows and outflows of your investment or project for the remaining arguments.
- Hit the Enter key to evaluate the function. The
NPVfunction should now return the net present value of your investment.
These are all the steps you need to know to start calculating the net present value in Excel.
To learn more about using Excel for investing and budgeting, you can read our post on how to forecast cash flow in Excel.
That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!