How to Use the NPV Formula in Excel

The Net Present Value (NPV) formula in Excel is used to determine the present value of future cash flows on investment and thereby how much money it will return over the term for decision-making purposes.

Formula Explanation

Rate: The discount rate to be used in the calculation to discount future cash flows

Value: The amount of cash flows that will be returned by the investment

Using simple math, let’s understand how he NPV formula works:

NPV = – (initial cash outflow) + (sum of discounted future cash flows)

NPV = – (initial cash outflow) + ( (cash inflow year 1) / (1 + discount rate) ) + ( (cash inflow year 2) / (1 + discount rate) )…etc, depending on number of years of future cash inflows

Example:

An investment firm is contemplating investing in a high tech company with its capital of $120,000. Profits for the next five years are expected to be the below amounts.

It wants a return of 1% per month and therefore this should be used as the discount rate. What is the NPV?

Solution:

The Analyst at the investment firm calculates NPV as follows:

Another way to calculate this manually, one month at a time to break down the future cash flows, is to discount each month individually as follows. The below table takes the future cash flow of the specific month and discounts it by the discount rate of 1% for month one. A discount rate of 1% is then applied twice to the second month.

The sum of the discounted future cash flows is $281,377. The initial investment was $120,000. Therefore, the NPV is $161.377.

Let’s illustrate how discounting works so that it’s clear how to determine the present value of future cash flows. If each period has a discount rate of 1%, i.e. a 1% return is expected each month, then the value of money received in month 2 is not as valuable as money received in month 1. Similarly, money received in month 12 is not as valuable as the money received in months 1 through 11. The present value of 1% in month 0 is 1% but in month 1 it is 0.99%, month 2 it is 0.98% and month 12 it is worth 0.89%. We can see the effect this has on the future cash flows per month below.

Summary

• Overall this lesson brings into light how the Net Present Value (NPV) works and discounting of future cash flows works while taking into consideration the time value of money.
• The goal is to have a positive NPV while assuming an appropriate discount rate. In our case, it is going to achieve $161,377. Hence, the company can process whether or not to pursue the project.
• It is greater than zero which is a good sign because that takes into consideration the discount rate they want to achieve and the upfront cost of $120,000 that they have to put into the project to get it going.

I hope that helps. Please leave a comment below with any questions or suggestions. For more in-depth Excel training, checkout our Ultimate Excel Training Course here. Thank you!