What is Net Present Value (NPV)?

Net Present Value (NPV) is a financial calculation used to determine the value of an investment or project in today’s dollars by taking into account the expected future cash flows and the time value of money (the concept that a sum of money is worth more now than the same sum will be at a future date due to its earnings potential right now). It is essentially a way to determine whether an investment is likely to be profitable or not by comparing the present value of the expected cash inflows to the present value of the expected cash outflows. If the net present value is positive, the investment is considered to be potentially profitable, while a negative NPV suggests that the investment is not likely to be profitable.

When is net present value used?

Net Present Value is used in a variety of financial contexts to evaluate the profitability of an investment or project. It is commonly used in real estate as the initial investment and monthly returns tend to be more stable than other business investments. However, net present value is also used in capital budgeting, project management and any business valuation. 

Overall, NPV is a useful tool for making investment decisions by providing a way to compare the expected benefits and costs of different investment options in a way that takes into account the time value of money.

What’s the difference between net present value and internal rate of return (IRR)?

Net present value and internal rate of return are two methods used to evaluate investment projects. So what exactly are the differences? NPV calculates the present value of expected future cash flows associated with an investment project, discounted back to the present at a specified rate, and produces a dollar amount indicating potential profitability. In simple terms, it’s a dollar amount that shows how much profit or loss the investment will generate, by estimating the present value of future cash flows at a specified discount rate

IRR, on the other hand, is the discount rate that makes the net present value of the investment equal to zero, and represents the expected rate of return of the project over its life. In simple terms, it’s a percentage that shows the expected rate of return of the investment. 

While both methods are commonly used to evaluate investment opportunities, they differ in their approach and what they measure. NPV measures the absolute value of profitability in dollars, while IRR measures the relative profitability of the investment in terms of percentage return. NPV is preferred when comparing mutually exclusive investment opportunities, while IRR is preferred when evaluating investments with irregular cash flows.

Calculated in:What does it measure?What is it best for?
NPVDollar amountIf investment will turn a profit or lossComparing mutually exclusive investment opportunities
IRRPercentageExpected rate of return on investmentEvaluating investments with irregular cash flows

What is the net present value formula?

The formula for calculating the Net Present Value of an investment is:

NPV = -Initial Investment + (Cash flow / (1 + Discount Rate) ^ Time)

  • “Initial Investment” is the amount of money invested in the project or investment at the beginning.
  • “Cash flow” is the expected cash flow for each period.
  • “Discount Rate” is the rate of return that the investor could earn on an alternative investment with similar risk.
  • “Time” is the period of time in which the cash flow occurs (usually measured in years).

The formula calculates the present value of each expected cash flow for the investment by discounting it back to its current value using the discount rate. It then subtracts the initial investment from the sum of the present values of all expected cash flows to give the net present value.

Example of net present value

Let’s say a company is considering investing in a new project that will cost $100,000 upfront and is expected to generate $30,000 per year for the next 5 years. The company’s cost of capital is 8%.

To calculate the net present value of the investment, we need to calculate the present value of the expected cash inflows and outflows. We can use the formula:

NPV = – Initial Investment + Present Value of Future Cash Flows

Where: Present Value of Future Cash Flows = Cash Flow / (1 + Discount Rate)^Number of Years

Using this formula, we can calculate the present value of the expected cash inflows for each year:

Year 1: $30,000 / (1 + 0.08)^1 = $27,778.85

Year 2: $30,000 / (1 + 0.08)^2 = $25,694.44

Year 3: $30,000 / (1 + 0.08)^3 = $23,749.72

Year 4: $30,000 / (1 + 0.08)^4 = $21,936.28

Year 5: $30,000 / (1 + 0.08)^5 = $20,247.90

Adding up the present values of the expected cash inflows, we get:

Present Value of Future Cash Flows = $27,778.85 + $25,694.44 + $23,749.72 + $21,936.28 + $20,247.90 = $119,407.18

Finally, we can calculate the net present value of the investment:

NPV = -$100,000 + $119,407.18 = $19,407.18

Since the NPV is positive, this investment is potentially profitable and the company should consider investing in the project.

How to calculate Net Present Value in Excel

In Excel, there is an NPV function that can be used to easily calculate the net present value of a series of cash flows. The NPV function in Excel is simply NPV, and the full formula requirement is:

=NPV(discount rate, future cash flow) + initial investment

Here are the steps to calculate net present value in Excel:

  1. Enter the expected cash flows for each period in a column. The first row should be the initial investment, and the remaining rows should represent the expected cash inflows or outflows for each period.
  2. In a cell, enter the discount rate to be used for the calculation. This is typically the cost of capital for the company or the desired rate of return for the investor.
  3. Select a cell where you want the NPV result to appear.
  4. Type the formula “=NPV” followed by the discount rate and a comma.
  5. Select the range of cash flows by clicking and dragging from the first cash flow to the last.
  6. Close the parentheses and press Enter to calculate the NPV.

Here is an example of the formula to calculate the NPV of an investment with cash flows of $100 in year 1, $200 in year 2, and $300 in year 3, with a discount rate of 10%:

=NPV(10%, -100, 200, 300)

In this example, the initial investment is -$100, so it is entered with a negative sign. The remaining cash flows are positive, so they are entered as they are. The NPV result will be displayed in the cell where the formula is entered.

The importance of the discount rate in Net Present Value

The discount rate is a critical component in the net present value calculation as it represents the opportunity cost of investing in a project. The discount rate reflects the basic concept of the time value of money, which means that money received in the future is worth less than money received today because it can be invested and earn a return. In other words, $100 today is worth more than $100 in a year from now, both due to inflation and the fact that the money can be worth more through investing. 

Therefore when making a business decision, not only does the NPV tell you whether the investment is worth it, it also tells you whether you are better off investing in something else with a similar risk. That is thanks to the discount rate in the formula of NPV. 

When calculating NPV, the expected cash flows are discounted back to their present value using the discount rate. The higher the discount rate, the lower the present value of future cash flows, and vice versa. This means that if the discount rate is high, it is more difficult for an investment to generate a positive NPV, as the present value of future cash flows will be lower.

The choice of discount rate is critical, as it directly impacts the NPV result and can affect the decision to invest or not. In general, a company or investor will use their cost of capital or the desired rate of return as the discount rate. The cost of capital represents the cost of financing a project, while the desired rate of return represents the minimum acceptable return for an investment.

Therefore, the discount rate is an essential factor to consider when evaluating investment opportunities as it helps to assess the potential profitability of the project and determine whether it meets the required minimum return or cost of capital.

Net Present Value downsides

  • The biggest downside of NPV is the fact that it doesn’t calculate ROI. By using the NPV formula alone, an investment of $1 million and $100 can get to the same number, but obviously each of these investments are very different. Though the NPV formula estimates how much value a project will produce, it doesn’t tell you whether it is an efficient use of your investment dollars, or whether you are better off putting your money somewhere else. 
  • Another downside is that NPV is driven by quantitative inputs only and does not consider nonfinancial metrics and other outside factors. 
  • Lastly, it relies heavily on estimates and long-term projections, meaning if any of the estimates change even slightly, it can throw off predictions in the long run. 

Using Datarails for calculating NPV and other financial calculations

Datarails’ FP&A software replaces spreadsheets with real-time data and integrates fragmented workbooks and data sources into one centralized location. This allows users to work in the comfort of Microsoft Excel with the support of a much more sophisticated data management system at their disposal. 

Datarails’ native Excel platform will allow your finance team to continue using Excel for calculating financial ratios (such as NPV), while providing an enhanced data management tool that can help your team create and monitor cash flow against budgets faster and more accurately than ever before.

Read more about Datarails’ success stories here.