Lesson 173: How to use the NPV function in Excel

NPV function in Excel is used to calculate the present value of a series of future cash flows and subtracts the initial investment.

Current net worth

For example, project X requires an initial investment of $100 (cell B5).

1. We expect a profit of $0 at the end of the first period, a profit of $50 at the end of the second period, and a profit of $150 at the end of the third period.

2. The discount rate is 15%.

Explanation: this is the rate of return of the investment. For example, you can also deposit your money in a savings account with 15% interest.

3. The NPV formula below calculates the net present value of project X.

Explanation: net present value indicates that the project’s rate of return exceeds the discount rate. In other words, you are better off investing your money in project X than putting it in a savings account with 15% interest.

4. The NPV formula below calculates the net present value of project Y.

Explanation: the net present value of project Y is higher than the net present value of project X. Therefore, project Y is a better investment.

Learn more about the NPV . function

The NPV function simply calculates the present value of a series of future cash flows.

1. For example, project A requires an initial investment of $100 (cell B5). We expect $0 profit at the end of the first period, $0 profit at the end of the second period and $152.09 profit at the end of the third period.

Explanation: a net present value of zero indicates that the project generates a rate of return equal to the discount rate. In other words, both options, investing your money in project A or depositing your money in a savings account with 15% interest, yield the same return.

2. We can test this. Let’s say you deposit $100 in the bank. Your investment after 3 years with 15% annual interest will be worth $152.09.

Note: Project A’s internal rate of return is 15%. The internal rate of return is the discount rate that makes the net present value zero.

3. The NPV function simply calculates the present value of a series of future cash flows.

4. We can test this. First, we calculate the present value (pv) of each cash flow. Next, we sum these values.

5. The NPV formula below calculates the net present value of project B.

Explanation: project B is a good investment because its present net worth ($159.55 – $100) is greater than zero.

In addition, you can also view many other excel articles here.