What is NPV? How to calculate NPV? Detailed guide on how to calculate NPV in Excel

NPV is a fundamental financial metric that tracks the value of cash flow over time for a project. The NPV function (Net Present Value) in Excel is also widely used. In this article, let's explore how to calculate NPV in Excel!

By
Administrator
-
0
170

The NPV function is one of the most popular functions used to track the financial situation of projects. In this article, let’s explore what NPV is and how to calculate the NPV function in Excel with FPT Shop!

What is NPV?

NPV (Net Present Value) is the present value of an investment. First, you need to understand the time value of money.

The time value of money changes over time, so to calculate the total value of cash flows of a project, we need to discount all cash flows to the same point in time. To do that, we need NPV.

NPV is one of the common indicators for analyzing and evaluating the value of a project. It is also a measure of the cash flow situation, which can be used for investment planning, budgeting, and analyzing the success potential of a project.

Formula for calculating NPV

Below is the formula for calculating NPV:

Where:

  • Rt: Net cash flow at time t.
  • n: Total time of the project.
  • i: Discount rate.
  • t: Time calculated in years or months.
  • C0: Initial investment cost.

Significance of NPV

The NPV result is used to assess whether a project is feasible or not, specifically:

  • NPV > 0: The income of the project will be greater than the initial investment cost, so it is worth investing.
  • NPV < 0: The expected rate of return is lower than the discount rate. NPV < 0 means the project is not worth investing in (no value).
  • NPV = 0: The project will break even.

Therefore, when assessing a project based on NPV, we should choose projects with positive NPV and select the project with the highest NPV, as it will provide the highest rate of return.

How to calculate NPV in Excel

In Excel, the NPV function is used with the following syntax:

= NPV (rate, value1,value2,…)

Where:

  • Rate: Discount rate (8%, 10%, 15%,…)
  • Value1, value2,…: Cash flows for each year 1, 2,…

How to calculate NPV in Excel when cash flows occur at the end of period 1

For example, if we need to assess a project with a discount rate of 5% and cash flows for each year as shown in the image:

To calculate the NPV for the project, you enter the function using the above syntax:

=NPV(D2,B2:B7)

Where:

  • D2: Discount rate
  • B2:B7: Range of cells containing cash flows for each year

The result returns 3,067,706, which means that with an initial investment of 50 million and cash flows for each year, with a discount rate of 5%, this project will bring a profit of 3,068,706 VND.

How to calculate NPV in Excel when cash flows occur at the beginning of period 1

If cash flows occur at the beginning of period 1, we simply add B2 directly to the result of the NPV function. So the syntax would be:

=B2 + NPV(D2,B3:B7)

Where:

  • B2: Value of cash flow in period 1
  • NPV (D2,B3:B7): The formula for calculating NPV for the remaining periods.

How to calculate NPV in Excel and compare projects

Suppose you need to assess the investment potential of 3 projects, NPV is a suitable indicator for evaluating and comparing projects. Here is an example:

You have 3 projects to invest in over a 6-year period with an initial investment in the first year of 50 million, cash flows at the end of each year, and a discount rate of 5%.

We calculate NPV using the syntax for each project:

  • Project 1: = NPV(5%,B2:B7)
  • Project 2: = NPV(5%,C2:C7)
  • Project 3: = NPV(5%,D2:D7)

According to the results, Project 3 has the highest rate of return (over 6 million). Therefore, if you can only choose 1 out of the 3 projects to invest in, Project 3 is the best choice.

Frequently asked questions

Net Present Value (NPV) is a metric used to determine the profitability of an investment or project. It calculates the difference between the present value of cash inflows and the present value of cash outflows over a period of time. NPV is important because it helps businesses make informed decisions about whether to pursue a particular investment or project by taking into account the time value of money.

To calculate NPV, you need to follow these steps: Determine the cash flows associated with the investment or project, including the initial investment and future cash inflows and outflows. Assign a discount rate that reflects the opportunity cost of capital or the minimum return expected. Use the discount rate to calculate the present value of each cash flow. Sum up the present values of the cash inflows and outflows separately. Finally, subtract the present value of cash outflows from the present value of cash inflows. A positive NPV indicates that the investment is expected to be profitable, while a negative NPV suggests otherwise.

The formula for calculating NPV is as follows: NPV = CF1 / (1 + r)^1 + CF2 / (1 + r)^2 + … + CFn / (1 + r)^n – Initial Investment, where CF represents the cash flows (positive or negative) in each period, ‘r’ is the discount rate, and ‘n’ is the number of periods.

A positive NPV means that the expected returns from the investment or project exceed the cost of capital, indicating that it is likely to be profitable. A negative NPV suggests that the investment is expected to lose money, and the opportunity cost of pursuing it is higher than the potential gains. A zero NPV indicates that the investment is expected to break even.

You may also like

What is IRR? Detailed and Easy Method to Calculate IRR in Excel

Are you wondering what the IRR index is, what IRR is applied for, and how to calculate IRR in Excel in detail? When evaluating the effectiveness of investments or any project, people always care about this indicator. Let’s find out in the article below!

RELATED ARTICLESMORE FROM AUTHOR