The NPV (Net Present Value) function in Excel is used to calculate the net present value of a series of cash flows given a discount rate. Here’s how you can use the NPV function in Excel:
Syntax
NPV(rate, value1, [value2], ...)
- rate: This is the discount rate for each period. It is required.
- value1, value2, …: These are the cash flows for each period. You can enter these as individual arguments or as an array/range of cells. The initial investment (or cash flow at time zero) should be added separately, not as part of the values in the NPV function. This is because NPV calculates the present value of cash flows starting from the end of the first period.
Steps to Use the NPV Function
- Enter the Discount Rate: Decide on and input the discount rate you plan to use for your cash flows.
- Input Cash Flows: List your series of future cash flows (excluding the initial investment) in consecutive cells. These could represent expected revenues or cost savings.
- Calculate NPV:
- Click on the cell where you want the NPV to appear.
- Input the NPV formula, referencing the discount rate and the range of cash flows, like this: `=NPV(rate, B2:B6)`, where `B2:B6` is the range with your future cash flows.
- Include Initial Investment: Subtract the initial investment from the NPV result.
- Suppose your initial investment is in cell `B1`, the final formula you’d use is: `=NPV(rate, B2:B6) – B1`.
Example
Let’s assume you have the following data:
Cell | Value
----------------
A1 | Discount Rate
B1 | 0.10
A2 | Initial Investment
B2 | -1000
A3 | Year 1 Cash Flow
B3 | 300
A4 | Year 2 Cash Flow
B4 | 400
A5 | Year 3 Cash Flow
B5 | 500
A6 | Year 4 Cash Flow
B6 | 600
- Discount rate: 10%
- Initial investment: $1000
- Cash flows over 4 periods: $300, $400, $500, $600
To calculate NPV:
- In the cell where you want the NPV result, enter: `=NPV(B1, B3:B6) + B2`
- The `+ B2` accounts for the initial cash flow at time zero, which is often an investment outlay.
This will give you the net present value of your cash flows when discounted at 10%.
By following these steps, you can effectively use the NPV function in Excel to evaluate investment opportunities based on their expected cash flows.
Unlock Your Potential
Excel
Access
Power BI
