In this tutorial, we will walk you through the steps of using the NPV function and how to use it to calculate the present value of future cash flows. By the end of this guide, you will have a solid understanding of how to use the NPV function in Excel and be able to use it to make informed financial decisions.
Net Present Value (NPV) is a financial metric used to determine the present value of a series of future cash flows. It is used to evaluate the profitability of an investment or project. The NPV is calculated by taking the sum of the present values of all future cash flows and subtracting the initial investment.
To calculate NPV, you need to know the initial investment, the expected cash flows over time, and the discount rate.
In this example, we want to invest $300,000 into a business, and with this investment, we expect a 10% discount on the future cash flow. For the first year, we expect no return. However, for the succeeding years, we expect $120,000, $150,000, $210,000, $290,000, and $350,000 in future cash flows for the next 5 years, respectively. With this information, we want to know if pursuing the investment will yield a profit based on the current present value.
To find the NPV, we will use the NPV function. The syntax for the NPV function is as follows:
NPV(discount_rate, cash_flow1, cash_flow2, … cash_flow_n)
- “discount_rate” is the rate at which future cash flows are discounted to their present value
- “cash_flow1” is the first cash flow in the series, “cash_flow2” is the second cash flow, and so on.
We can then construct the following formula: =NPV(B2, B6, and B11)
Now that we have the NPV of the investment, $732,937, we want to know if it will generate an income. To do this, simply subtract the investment from the NPV. If the number is positive, we want to continue the investment; otherwise, if the number is negative, we don’t want to continue with the investment.
Note: Since in cell A5, the investment is already a negative number, we will add it to the NPV.
Conclusion
The NPV function in Excel allows you to quickly and easily calculate the net present value of an investment based on a series of cash flows and a discount rate. With this knowledge, you can make more informed financial decisions.
